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
xlookup and a wild card search
-
- Panoramic Lounger
- Posts: 8125
- Joined: 25 Jan 2010, 09:09
- Location: retirement
-
- Administrator
- Posts: 78234
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: xlookup and a wild card search
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
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
Hans
-
- Panoramic Lounger
- Posts: 8125
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: xlookup and a wild card search
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
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
-
- Administrator
- Posts: 78234
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Panoramic Lounger
- Posts: 8125
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: xlookup and a wild card search
Except, the number of cells I wish to populate with the array formula means the workbook now takes many minutes to recalc No matter, once it does finally finish I can turn off autocalc. I only really need it to do the lookup once.
Ken
Ken
-
- Administrator
- Posts: 78234
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: xlookup and a wild card search
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
Hans
-
- Panoramic Lounger
- Posts: 8125
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: xlookup and a wild card search
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
Thanks, again,
Ken
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: xlookup and a wild card search
If you have XLOOKUP, do you have FILTER? If so, use:
=TRANSPOSE(FILTER(sheet2!C:C,LEFT(sheet2!C:C,3)=$D4),"not found"))
=TRANSPOSE(FILTER(sheet2!C:C,LEFT(sheet2!C:C,3)=$D4),"not found"))
Regards,
Rory
Rory