Lookup number plus or minus N

In this example, the goal is to look up a number with a certain amount of allowed tolerance, defined as n. In other words, with a given lookup number we are trying to find a number in a set of data that is ± n. In the worksheet shown, the number to find is in cell G4 and the number used for n is in G5. All data is in an Excel Table in the range B5:D15 named "data". This problem can be solved with the XLOOKUP function or with INDEX and MATCH together with Boolean logic. Both options are explained below.

XLOOKUP function

The XLOOKUP function is a modern replacement for the VLOOKUP function. A key benefit of XLOOKUP is that it can handle array operations as the lookup_array or return_array. This means we can construct the lookup_array we need as part of the formula. We start off by providing lookup_value as 1:

=XLOOKUP(1,

Note: The lookup value of 1 has nothing to do with the value for n, which is also 1 in this case. It is simply a common convention when using Boolean logic in lookup formulas. 

Next, we create the lookup_array with this expression:

--(ABS(data[Amount]-G4)<=G5)

Inside the ABS function, we subtract the value in cell G4 (5000) from all values in data[Amount]. Since we have 11 values in the Amount column, we get 11 results in an array like this:

{-3499.65;19350.86;-1;-3750.5;4099.75;6999.75;-700.15;1750.75;9999.9;-2899.15;5249.65}

Notice some values are negative. Next, the ABS function returns the absolute value of these values:

{3499.65;19350.86;1;3750.5;4099.75;6999.75;700.15;1750.75;9999.9;2899.15;5249.65}

Each value is then checked against the value for n in cell G5 (1). We are looking for values that are less than or equal to 1. You can see that the third number is in this range. The result is a new array that contains TRUE and FALSE values:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

In this array, a TRUE value indicates we have found a matching value that is within plus or minus n of our lookup value. Notice the third value is TRUE, while all others are FALSE. Next, because we are using 1 for lookup_value, we use a double-negative (--) to convert the TRUE and FALSE values to 1s and 0s:

{0;0;1;0;0;0;0;0;0;0;0}

Moving back to the XLOOKUP formula, we now have:

=XLOOKUP(1,{0;0;1;0;0;0;0;0;0;0;0},data)

Now it's clear why we are using 1 for lookup_value. XLOOKUP matches 1 and returns the third row of data.

INDEX and MATCH option

This problem can also be solved with an INDEX and MATCH formula like this:

=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),0)

The MATCH function is configured just like XLOOKUP above:

MATCH(1,--(ABS(data[Amount]-G4)<=G5),0)

The lookup_value is 1 and the lookup_array is created with the ABS function as above. After lookup_array is evaluated, we have:

MATCH(1,{0;0;1;0;0;0;0;0;0;0;0},0)

The MATCH function matches the third value (1), and returns 3 to the INDEX function as the row_num:

=INDEX(data,3,0)

Because column_num is set to zero, INDEX returns the entire third row from the data as a final result. In the current version of Excel, the result will spill into three cells, but in Legacy Excel, you will see only the first value. To display all values, you can either enter the formula as a multi-cell array formula, or use three separate formulas to return values for each of the columns like this:

=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),1) // amount
=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),2) // date
=INDEX(data,MATCH(1,--(ABS(data[Amount]-G4)<=G5),0),3) // client

Note the only difference in the formulas above is the column number.