Explanation
There are certain functions in Excel that return a cell reference as a result rather than a value. Two of these functions are XLOOKUP and INDEX . The presence of the cell reference in the result is not obvious, because Excel immediately resolves the reference to the value in that cell. You can check the reference returned by XLOOKUP or INDEX with the CELL function . This can be useful when debugging a lookup formula to confirm the result being returned.
XLOOKUP function
XLOOKUP is a function in Excel that returns a cell reference as a result instead of a value. You can inspect the reference returned by XLOOKUP by wrapping the formula in the CELL function with “address” as info_type . In the example shown, the formula in cell F6 is:
=CELL("address",XLOOKUP(F4,B5:B17,C5:C17))
Working from the inside out, the formula is an ordinary XLOOKUP formula:
=XLOOKUP(F4,B5:B17,C5:C17)
With a lookup value of “Sat” in cell F4, XLOOKUP returns 325, the Sales amount on the first entry for Saturday. However, underneath the surface, XLOOKUP is actually returning a reference to cell C10. We can check that result by nesting the XLOOKUP function inside the CELL function, and providing “address” for the info_type argument:
=CELL("address",XLOOKUP(F4,B5:B17,C5:C17)) // returns $C$10
The CELL function returns $C$10, the address of the cell returned by XLOOKUP. Note that if we configure XLOOKUP to perform a reverse search, by providing -1 for the search_mode argument, the result is $C$17:
=CELL("address",XLOOKUP(F4,B5:B17,C5:C17,,,-1)) // returns $C$17
This is the address of the second (and last) entry for Saturday in the data.
INDEX and MATCH
Like the XLOOKUP function, the INDEX function is another function that returns an address. The equivalent INDEX and MATCH formula that retrieves the Sales amount for “Sat” in F4 is:
=INDEX(C5:C17,MATCH(F4,B5:B17,0))
By wrapping the formula in the CELL function , we can get Excel to show us the address to the cell returned by INDEX:
=CELL("address",INDEX(C5:C17,MATCH(F4,B5:B17,0))) // returns $C$10
The CELL function returns $C$10, the address of the cell returned by INDEX.
Explanation
In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found. In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is:
=TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),""))
Working from the inside out, this formula is based on the formula described here , which uses the SEARCH function together with the ISNUMBER function :
ISNUMBER(SEARCH(things,B5))
In a nutshell, SEARCH returns a number if it finds the target string and an error if not, and ISNUMBER converts this result into a TRUE or FALSE. In this case, we are looking for several strings at once – the five colors in the named range things (E5:E10), so the code above returns an array with five TRUE FALSE values like this:
{FALSE;FALSE;TRUE;FALSE;TRUE}
Notice the TRUE values correspond to white and green in the list of colors (positions 3 and 5), and FALSE values correspond to the colors not found. This array is returned directly to the FILTER function as the include argument, with the array argument given as things:
FILTER(things,{FALSE;FALSE;TRUE;FALSE;TRUE},"")
The FILTER function uses the TRUE and FALSE values to filter the list of colors and returns an array of the two colors found:
{"white";"green"} // result from FILTER
The result from FILTER is delivered to the TEXTJOIN function :
=TEXTJOIN(", ",1,{"white";"green"})
TEXTJOIN is configured to join items in the array with a comma and space (", “) and to ignore empty strings (”"). The number 1 is provided instead of TRUE for brevity. TEXTJOIN concatenates the found colors, separated by commas, and returns a final result.
Note FILTER’s not_found argument is provided as an empty string (""). In the event no colors are found, FILTER returns an empty string to TEXTJOIN, which then also returns an empty string as the final result.
