Explanation
Working from the inside out, the MIN function is used to find the lowest bid in the range C5:C9:
MIN(C5:C9) // returns 99500
The result, 99500, is fed into the MATCH function as the lookup value:
MATCH(99500,C5:C9,0) // returns 4
Match then returns the location of this value in the range, 4, which goes into INDEX as the row number along with B5:B9 as the array:
=INDEX(B5:B9, 4) // returns Cymbal
The INDEX function then returns the value at that position: Cymbal.
Explanation
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.
