The Excel INRANGE function checks if a specific value or a range exists within another range and returns a boolean value, TRUE or FALSE.
This tutorial will show you how to check whether or not a specific value (number, text) exists in a range. INRANGE is a user-defined function, but no worries, our UDF library, DataFx, fully supports the function.
Syntax
=INRANGE(value_or_range, search_range)Arguments
The INRANGE function uses two required arguments.
- value_or_range: the single value or range you want to find in another array.
- search_range: the range where you want to find a value or a range.
INRANGE Function example
In the example, the data set contains numeric values in B3:B12. The goal is to check whether the range contains the lookup value (5).
Formula:
=INRANGE(D3,B3:B12)The formula returns FALSE since the search_array does not contain the lookup value, which is 5.
Change the formula and use 43 as a lookup value.
Formula:
=INRANGE(D4,B3:B12)The formula returns a TRUE boolean value since the search_array contains 43.
How do you find a value from a range in another range?
In the following example, the goal is to find a range in another range. To write a better formula, create two named ranges:
- range1 = B3:B12
- range2 = D3:D5
Formula:
=INRANGE(range2,range1)Explanation: The result is TRUE; all values in the range2 exist in the range1. The INRANGE function supports range-by-range comparison, so it is handy.
Workaround with regular Excel functions
You can use formulas that provide the (almost) same functionality as INRANGE. The XLOOKUP function returns the lookup value if it exists in a lookup array, else returns a #N/A error.
=XLOOKUP(D3,range1,range1)Using the COUNTIF function, you can check a single cell:
=COUNTIF(range1, D3)>0In the case of TRUE, the lookup value exists in the range.
Note: These formulas that are based on built-in functions do not support ranges as a lookup value.
