where trip (B5:B14) and cost (C5:C14) are named ranges. In F5, F6, and F7, the formula returns the trip closest in cost to 500, 1000, and 1500, respectively. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Taking things step-by-step, the lookup value is calculated with MIN and ABS like this: First, the value in E5 is subtracted from the named range cost (C5:C14). This is an array operation, and since there are 10 values in the range, the result is an array with 10 values like this: These numbers represent the difference between each cost in C5:C15 and the cost in cell E5, 700. Some values are negative because a cost is lower than the number in E5. To convert negative values to positive values, we use the ABS function: which returns: We are looking for the closest match, so we use the MIN function to find the smallest difference, which is 101: This becomes the lookup value inside MATCH. The lookup array is generated as before: which returns the same array we saw earlier: We now have what we need to find the position of the closest match (smallest difference), and we can rewrite the MATCH portion of the formula like this: With 101 as the lookup value, MATCH returns 8, since 101 is in the 8th position in the array. Finally, this position is fed into INDEX as the row argument, with the named range trip as the array: and INDEX returns the 8th trip in the range, “Spain”. When the formula is copied down to cells F6 and F7, it finds the closest match to 1000 and 1500, “France” and “Thailand” as shown. Note: if there is a tie, this formula will return the first match.
With XLOOKUP
The XLOOKUP function provides an interesting way to solve this problem, because a match type of 1 (exact match or next largest) or -1 (exact match or next smallest) doesn’t require data to be sorted. This means we can write a formula like this: As above, we use the absolute value of (cost-E5) to create a lookup array: Then we configure XLOOKUP to look for zero, with match type set to 1, for exact match or next largest. We supply the named range trip as the return array, so the result is “Spain” as before.
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.
