xlookup and a wild card search

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

xlookup and a wild card search

Post by stuck »

I've fiddling with XLOOKUP but, as yet I can't get the output I want.

In column C on worksheet in my workbook I have a 'master' list.

In column D on a separate worksheet I have another list that I want to lookup in my 'master' list. That's simple enough, say:
    =XLOOKUP(D4,sheet2!C:C,sheet2!C:C

Similarly, if I want a partial match I can say:
    =XLOOKUP(LEFT($D4,3)&"*",sheet2!C:C,sheet2!C:C,"not found",2)
which returns the first partial hit from the master list.

What I want though is look up a partial match and get back all possible hits from the 'master' list. Can that be done?

Thanks,

Ken

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: xlookup and a wild card search

Post by HansV »

I don't think you can do that with XLOOKUP, but it should be possible using INDEX and SMALL - see:

Get nth match with INDEX / MATCH
Extract multiple matches into separate columns

Or a VBA solution, courtesy of sdckapr: Re: Vlookup (or another method) for searching
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: xlookup and a wild card search

Post by stuck »

:thankyou: I was coming to the same conclusion.

After a break for lunch a refreshed brain Googled more effectively and found this page with an array formula that does what I need, even down to return the values horizontally:
https://www.get-digital-help.com/how-to ... /#horizont

Ken

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: xlookup and a wild card search

Post by HansV »

Good find! :thumbup:
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: xlookup and a wild card search

Post by stuck »

Except, the number of cells I wish to populate with the array formula means the workbook now takes many minutes to recalc :laugh: No matter, once it does finally finish I can turn off autocalc. I only really need it to do the lookup once.

Ken

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: xlookup and a wild card search

Post by HansV »

If you don't need to recalculate the formulas, you can select the range, copy it, and paste special as values. You can then leave automatic calculation turned on.
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: xlookup and a wild card search

Post by stuck »

Yes, I thought of that too but decided that retaining the formulae but turning of autocalc was more flexible, giving me the option of rerunning the lookup if I needed to. Hopefully I won't, at least not until this time next year when we will have to go through this particular process again.

Thanks, again,

Ken

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: xlookup and a wild card search

Post by rory »

If you have XLOOKUP, do you have FILTER? If so, use:

=TRANSPOSE(FILTER(sheet2!C:C,LEFT(sheet2!C:C,3)=$D4),"not found"))
Regards,
Rory