where data is an Excel Table in the range B5:D15.
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: 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: 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: Notice some values are negative. Next, the ABS function returns the absolute value of these values: 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: 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: Moving back to the XLOOKUP formula, we now have: 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: The MATCH function is configured just like XLOOKUP above: The lookup_value is 1 and the lookup_array is created with the ABS function as above. After lookup_array is evaluated, we have: The MATCH function matches the third value (1), and returns 3 to the INDEX function as the row_num: Because column_num is set to zero, INDEX returns the entire third row from 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: Note the only difference in the formulas above is the column number.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
