Forum Discussion

Copper Contributor

Dec 03, 2024

Solved

XLOOKUP Multiple Lookup Values?

I have a project where I have been using XLOOKUP to pull information from one sheet into another, but my problem is the following: To see if there is a match, I have to check three different lookup v...

excel

gracecurtis
Dec 03, 2024
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

PeterBartholomew1

Silver Contributor

Dec 03, 2024

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

gracecurtis

Copper Contributor

Dec 03, 2024

Thanks! I was able to use a couple IFNA functions but I'll see if this works better.