excelavon

Google Sheets: Check If a Value Falls Within a Range

How-to basics · Updated November 3, 2026

Google Sheets: Check If a Value Falls Within a Range

To check whether a value is between two limits in Google Sheets, use =AND(A2>=$B$1,A2<=$C$1). This returns TRUE when the value in A2 is at least the lower limit in B1 and no greater than the upper limit in C1; change the comparison operators if the endpoints should not count.

The basic range check

The AND function tests multiple conditions at once. In this example, the first condition checks the lower boundary and the second checks the upper boundary. Both must be true for AND to return TRUE. The dollar signs keep the boundary references fixed if you copy the formula down a column.

With the value in A2 and the lower and upper limits in B1 and C1, respectively, enter:

=AND(A2>=$B$1,A2<=$C$1)

If A2 contains 42, B1 contains 10, and C1 contains 50, the result is TRUE. If A2 contains 55, the result is FALSE. This is a logical result, not text, so it can be used in other formulas, filters, or conditional formatting rules.

To display a more readable message instead, use the same cell layout:

=IF(AND(A2>=$B$1,A2<=$C$1),"In range","Out of range")

With the example value 42 and limits 10 and 50, this returns In range. IF evaluates the AND test and returns the first text when it is true, or the second when it is false. You can replace those messages with labels such as Pass and Review, but keep the quoted text inside the formula.

Inclusive and exclusive endpoints

The operators determine whether values equal to either boundary qualify. The basic formula uses >= and <=, so the range is inclusive: both boundary values are counted as inside. That is usually appropriate for rules such as a permitted score from 0 through 100 or a date window that includes its start and end dates.

If a value must be strictly greater than the lower limit and strictly less than the upper limit, use > and < instead. With A2 as the tested value and B1 and C1 as the limits, the formula is:

=AND(A2>$B$1,A2<$C$1)

If the limits are 10 and 50, this version returns FALSE for exactly 10 or 50, and TRUE for 42. You can also make just one endpoint exclusive. For example, use > for the lower condition and <= for the upper condition when the lower limit is excluded but the upper limit is included. Choose these operators based on the actual rule, not just on which formula looks familiar.

Checking every row and counting matches

For a column of values, place the formula beside the first value and fill it down. With values in A2:A100 and fixed limits in B1 and C1, put this formula in D2, then copy it down through D100:

=AND(A2>=$B$1,A2<=$C$1)

The row reference A2 adjusts to A3, A4, and so on as the formula is copied; the absolute references to B1 and C1 stay fixed. You can fill down by dragging the small square at the bottom-right corner of the selected cell, or by copying and pasting the formula into the destination cells.

If the goal is to count how many entries fall within the limits, use COUNTIFS. With values in A2:A100 and lower and upper limits in B1 and C1, enter:

=COUNTIFS(A2:A100,">="&B1,A2:A100,"<="&C1)

If the values 10, 42, 50, and 55 are in A2:A5, and the limits are 10 and 50, the count is 3 because the endpoints are included. COUNTIFS applies both conditions to the same range. The ampersand joins each comparison operator to the corresponding limit so Sheets can use it as a criterion.

Highlight values that are in range

Conditional formatting can color cells that meet the range test without adding a TRUE/FALSE helper column. Select the cells to check, then choose Format > Conditional formatting. In the Conditional format rules panel, set “Format cells if” to “Custom formula is,” enter the formula, choose a formatting style, and select Done.

For a selection beginning at A2, with values in A2:A100 and fixed boundaries in B1 and C1, enter this custom formula:

=AND(A2>=$B$1,A2<=$C$1)

Use A2 in the formula because it is the top-left cell of the selected range. Sheets adjusts the row reference for the other cells as it applies the rule. Keep the limit references absolute so every row is compared with the same two cells. If you select a range that starts somewhere else, change A2 to that range’s top-left cell.

To highlight values outside the interval instead, negate the test. With the same selected range and limits, use:

=NOT(AND(A2>=$B$1,A2<=$C$1))

This includes values below the lower limit and above the upper limit. Think about whether blanks should be highlighted; depending on the rule and cell contents, blank cells can behave unexpectedly in comparisons. A blank guard can make the intent clearer, as described below.

Dates, blanks, and data types

Date checks use the same logic as numeric checks because Google Sheets stores dates as serial numbers and displays them according to date formatting. If A2 contains the date being checked, B1 the start date, and C1 the end date, the inclusive formula is:

=AND(A2>=$B$1,A2<=$C$1)

For instance, if the start and end cells contain valid dates, a date equal to either boundary returns TRUE. Make sure all three cells contain real date values rather than text that merely looks like a date. Set the cells to a date format with Format > Number > Date if you want them displayed as dates; formatting changes the display, not the underlying value.

A blank tested cell deserves special attention. If A2 is empty, a comparison may treat it like a zero-like value, which can cause a misleading result when the lower limit is zero or negative. To require a nonblank value as well, with A2 as the tested cell and B1 and C1 as the limits, use:

=AND(A2<>"",A2>=$B$1,A2<=$C$1)

This returns FALSE for an empty A2 and still checks the inclusive interval for a populated cell. If your data can contain text, errors, or values imported with inconsistent types, inspect those cells too: a range comparison is most reliable when the tested values and boundaries are consistently numeric or consistently valid dates.

When the limits may be entered in reverse

A basic check assumes the lower limit is in B1 and the higher limit is in C1. If users may enter those two limits in either order, the check can compare the value with the smaller and larger of the two cells. With the tested value in A2 and the two limits in B1 and C1, use:

=AND(A2>=MIN(B1,C1),A2<=MAX(B1,C1))

If B1 is 50, C1 is 10, and A2 is 42, the formula returns TRUE: MIN supplies 10 and MAX supplies 50. This avoids a reversed-boundary mistake without changing the values the user entered. It does not validate whether the limits themselves are sensible, so a separate check may be needed if the two limits must follow a particular order for other reasons.

Common errors and practical checks

If the result seems wrong, check the cell references and the data before rewriting the formula. A few typical problems account for many range-check failures:

  • Wrong boundary order: The basic formula expects the smaller limit first and the larger one second. Use MIN and MAX if the order can vary.
  • Missing dollar signs: When filling a formula down, B1 and C1 can shift to B2 and C2. Use $B$1 and $C$1 when the boundary cells should remain fixed.
  • Text instead of numbers or dates: Imported values may look numeric but be stored as text. Check the cell contents and convert the data consistently before relying on comparisons.
  • Endpoint mismatch: Decide whether exact boundary values qualify. Use inclusive operators for included endpoints and strict operators for excluded ones.
  • Unexpected blanks: Add a nonblank test when empty cells should never count as valid values.
  • Wrong conditional-formatting anchor: The formula should refer to the top-left cell of the selected range, while fixed limits should normally use absolute references.

It also helps to test the formula with four deliberate cases: a value below the lower limit, one equal to the lower limit, one inside, and one equal to or above the upper limit. Those cases expose off-by-one boundary choices and reference mistakes quickly.

Google Sheets and Excel differences

The formulas shown here use common functions such as AND, IF, COUNTIFS, MIN, and MAX, which are available in both Google Sheets and Microsoft Excel. The comparison logic is the same, so a simple AND range test generally transfers between the two programs. Google Sheets formulas in a web browser use the cell references and comma-separated arguments shown here.

The instructions for conditional formatting are specific to Sheets: use Format > Conditional formatting and choose “Custom formula is.” Excel has its own Conditional Formatting controls and menu layout, so do not expect the Google Sheets path to match Excel’s ribbon. Spreadsheet locale settings can also affect formula separators; if a sheet expects semicolons rather than commas, use the separator accepted by that sheet’s locale.

Frequently Asked Questions

How do I check if a number is between two numbers in Google Sheets?

Use AND with a lower-bound and an upper-bound comparison. For example, if the value is in A2 and the limits are in B1 and C1, =AND(A2>=$B$1,A2<=$C$1) returns TRUE when both inclusive conditions are met.

Does the formula include the start and end values?

Yes, the operators >= and <= include values equal to either limit. Replace them with > and < if both endpoints should be excluded, or change only one operator to exclude just one endpoint.

How can I return words instead of TRUE or FALSE?

Wrap the AND test in IF and provide the two messages you want, such as In range and Out of range. Put text results in straight double quotation marks in the formula.

Can I highlight cells that fall within a range?

Yes. Select the cells, open Format > Conditional formatting, choose “Custom formula is,” and enter an AND formula that refers to the selection’s top-left cell. Use absolute references for fixed boundary cells.

Why does a blank cell appear to pass my range check?

Blank cells can be treated like zero-like values in comparisons, so the result depends on the limits. Add a condition such as A2<>"" inside AND when the checked cell must contain something before it can qualify.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.