Explanation
In this example, the goal is to find the closest match to a target value entered in cell E5. Although it may not look like it, this is essentially a look-up problem. The easiest way to solve this problem is with the XLOOKUP function . However in older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula . Both approaches are explained below.
XLOOKUP solution
The XLOOKUP function provides an interesting way to solve this problem because one of XLOOKUP’s core features is the ability to perform an approximate match on unsorted data. This sounds very abstract, but we can use this feature to look for a difference of zero between a target value and a set of data, and we don’t need to worry about where in the data this match might be. The trick is that we need to calculate the actual differences on-the-fly and use the result as our lookup array. Then we look for a difference of zero. This is the approach used in the workbook shown, where the formula in cell F5 is:
=XLOOKUP(0,ABS(C5:C16-E5),B5:B16,,1)
Notice the lookup_value is zero (0) and the return_array is B5:B16, the range that contains the city names. The clever bit is the lookup_array , which is calculated like this:
ABS(C5:C16-E5)
Working from the inside out, the first operation is to subtract the value in E5 from all costs in C5:C16. Because there are 12 values in C5:C16, this is an array operation , and the result is an array that contains 12 values like this:
ABS({295;-601;-411;-751;-301;-50;-651;-561;499;-50;399;-5})
These values represent the differences between 1200 and the values in C5:C16. Notice many values are negative. To normalize these values, we use the ABS function , which converts the numbers to absolute values. We use the ABS function here because some of the differences are negative, but we only care about absolute differences when looking for the closest match. The result from ABS looks like this:
{295;601;411;751;301;50;651;561;499;50;399;5}
This array is returned directly to the XLOOKUP function as the lookup_array :
=XLOOKUP(0,{295;601;411;751;301;50;651;561;499;50;399;5},B5:B16,,1)
The if_not_found argument is left empty. Finally, we get to the match_mode argument, which is key to the successful operation of the formula. By default, match_mode is zero, which means “exact match”. We don’t want an exact match in this problem because we are looking for a difference of zero and in most cases, we won’t find a perfect match. Instead, the behavior we want is “exact match or next largest value”. To enable this behavior, we use the number 1 for match_mode . ( More details on XLOOKUP here ).
With the configuration explained above, and a target value of $1200 in cell E5, the final result is “Tokyo”, because the difference between $1200 and $1195 is $5, and 5 is the closest match to zero. The next closest match is Stockholm, with a cost of $1,150 and a difference of $50.
INDEX and MATCH solution
In older versions of Excel without the XLOOKUP function, you can use an array formula based on INDEX and MATCH like this:
{=INDEX(B5:B16,MATCH(MIN(ABS(C5:C16-E5)),ABS(C5:C16-E5),0))}
Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and previous.
At the core, this is an INDEX and MATCH formula: MATCH locates the position of the closest match, feeds the position to INDEX , and INDEX returns the value at that position in the range B5:B16. The hard work is done with the MATCH function, which is carefully configured to match the “minimum difference” like this:
MATCH(MIN(ABS(C5:C16-E5)),ABS(C5:C16-E5),0)
Taking things step-by-step, the lookup value in MATCH is calculated with MIN and ABS like this:
MIN(ABS(C5:C16-E5)
First, the value in E5 is subtracted from the values in C5:C16. This is an array operation, and since there are 12 values in the range, the result is an array with 10 values like this:
MIN(ABS({295;-601;-411;-751;-301;-50;-651;-561;499;-50;399;-5}))
These numbers represent the difference between each cost in C5:C16 and the cost in cell E5, 1200. 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 the following array:
MIN({295;601;411;751;301;50;651;561;499;50;399;5}) // returns 5
We are looking for the closest match , so we use the MIN function to find the smallest difference , which is 5. This becomes the lookup value inside MATCH. The lookup array is generated in the same way:
ABS(C5:C16-E5) // generate lookup array
The ABS function then returns the same array we saw above:
{295;601;411;751;301;50;651;561;499;50;399;5}
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:
MATCH(5,{295;601;411;751;301;50;651;561;499;50;399;5},0) // returns 12
With 5 as the lookup value, MATCH returns 12, since 5 is in the 12th position in the array:
=INDEX(B5:B16,12)
The INDEX function then returns the 12th city in the range, “Tokyo”, as a final result.
Note: if there is a tie, this formula will return the first match.
Explanation
The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the XLOOKUP function or the FILTER function . However in older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula . Both approaches are explained below. For convenience, the range B5:B16 is named data . However, you can use a regular cell reference as well.
XLOOKUP solution
In the workbook shown, the XLOOKUP function is used to return the longest name in the range B5:B16. The formula in E6 is:
=XLOOKUP(MAX(LEN(data)),LEN(data),data)
Where data is the named range B5:B16. The result is “Esmeralda”, which contains 9 characters. Working from the inside out, we first use the LEN and MAX functions to get the length of the longest name like this:
MAX(LEN(data))
The LEN function returns the length of a text string in characters. In this case, there are 12 names in data (B5:B16) so LEN returns 12 results in an array like this:
{5;6;8;6;6;5;6;8;9;6;8;6}
Each number represents the length in characters of one name in the data. The result from LEN is returned directly to the MAX function, which returns 9:
MAX({5;6;8;6;6;5;6;8;9;6;8;6}) // returns 9
The result from MAX is delivered to XLOOKUP as the lookup_value :
=XLOOKUP(9,LEN(data),data)
At this point, we have a lookup value of 9 and we need to create a lookup array that holds all string lengths. To do this we call the LEN function again, the same as before:
LEN(data) // returns {5;6;8;6;6;5;6;8;9;6;8;6}
The result from LEN becomes the lookup_array :
=XLOOKUP(9,{5;6;8;6;6;5;6;8;9;6;8;6},data)
XLOOKUP performs an exact match by default. It matches the 9 in the lookup array and returns the corresponding value in data (“Esmeralda”) as a final result.
Note: with the worksheet as shown, the result from XLOOKUP and FILTER is the same, since there are no ties. However, if there were two names with a length of 9 characters, FILTER would return both names and the XLOOKUP formula would return just the first name with 9 characters.
FILTER solution
Another way to solve this problem is with the FILTER function , which is designed to retrieve multiple matching records. This makes sense in cases where there may be ties, and you would like to report all results. To get the longest string with FILTER, including any tie values, the formula is:
=FILTER(data,LEN(data)=MAX(LEN(data)))
The logic in this formula is similar to what we used above with XLOOKUP. We are asking FILTER for all values in data where the length of the text string equals the max length found in data .
INDEX and MATCH solution
In older versions of Excel without the XLOOKUP function, you can use an array formula based on INDEX and MATCH like this:
{=INDEX(data,MATCH(MAX(LEN(data)),LEN(data),0))}
Note: enter as an array formula with control + shift + enter in Excel 2019 and earlier.
Most of the hard work in this formula is done with the MATCH function, which is set up like this:
MATCH(MAX(LEN(data)),LEN(data),0)
Here, MATCH is set up to perform an exact match by supplying zero for match_type . For lookup_value , we supply the maximum string length found in all data:
MAX(LEN(data))
LEN returns an array of results (lengths), one for each name in the list:
{5;6;8;6;6;5;6;8;9;6;8;6}
MAX then returns the largest value in the array (9):
MAX({5;6;8;6;6;5;6;8;9;6;8;6}) // returns 9
For the lookup_array , LEN is again used to return an array that contains all lengths:
LEN(data) // returns {5;6;8;6;6;5;6;8;9;6;8;6}
After LEN and MAX run, we have the following:
MATCH(9,{5;6;8;6;6;5;6;8;9;6;8;6},0)
MATCH then returns the position of the max value (9) directly to the INDEX function as the row_num :
=INDEX(data,9)
Finally, the INDEX function returns the value in the 9th row of data , which is “Esmeralda”.
Note: it is a coincidence only that the longest name (9 characters), happens to be in the 9th row!
