Forum Discussion
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 values to find information and essentially run the function three different times. I'm wondering if there is a way around this. Additionally, my lookup values need a wildcard search, and I also need to pull in multiple return values. Here is the formula I've been using:
=XLOOKUP("*"&'Sheet1'A1&"*",'Sheet2'A:A,CHOOSE({1,2,3,4},'Sheet2'B:B,'Sheet2'C:C,'Sheet2'D:D,'Sheet2'E:E),,2)
I would love it if I could do something like the nested CHOOSE function for the lookup value, but I'm guessing this probably isn't possible. Any other suggestions? Would Power Query be able to do this? I have pretty messy data that is out of my control :( Any help would be appreciated.
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
8 Replies
Try this:
=XLOOKUP("*"&Sheet1!A1&"*",Sheet2!A:A,Sheet2!B:B,XLOOKUP("*"&Sheet1!A1&"*",Sheet2!A:A,Sheet2!C:C,XLOOKUP("*"&Sheet1!A1&"*",Sheet2!A:A,Sheet2!D:D,XLOOKUP("*"&Sheet1!A1&"*",Sheet2!A:A,Sheet2!E:E,2),2),2),2)
No need for IFNAs if you do a second lookup as the error condition of not finding it in the first column.=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Thanks! I forgot about that, would definitely speed things up.
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.
Thanks! I was able to use a couple IFNA functions but I'll see if this works better.
XLOOKUP may not be the best fit for the situation. If there's a need to return multiple values then FILTER may be a better option or even an aggregation function such as GROUPBY. Do you happen to have a sample workbook to share?
It's confidential info so unfortunately I can't share. But I will try what you suggested, thanks!
