Forum Discussion
XLOOKUP Multiple Lookup Values?
I found the solution! I needed to use an IFNA function to search for the first two variables, then nest that whole formula within another IFNA function to get the third. This video helped me: https://www.youtube.com/watch?v=AXo9BPqbvxo&t=195s
You can look up an array of values and return results from multiple columns with INDEX/XMATCH
= INDEX(returnArray, XMATCH("*"&lookupValue&"*",lookupArray, 2), {1,2,3,4})XLOOKUP is a bit harder because Microsoft made a mess of 'arrays of arrays' to ensure backward compatibility with things that users could have done when using obsolete versions of Excel.
= LET(
returnRecordsϑ, XLOOKUP("*"&lookupValue&"*",lookupArray, BYROW(returnArray, THUNK), , 2),
EVALTHUNKARRλ(returnRecordsϑ)
)works OK but at the cost of having to import the function EVALTHUNKARRλ that I posted to GitHub.
A version of Excel MAP helper function that will return an array of arrays
What you cannot do with any straightforward implementation of an Excel formula using XLOOKUP is to return multiple matches for a given lookup value. That is better done with FILTER.