Forum Discussion
vlookup two references from a single cell....
Hi,
I have tried searching for this query on forums, but not finding what I require.
Basically, I would like to be able to vlookup two references within a single cell separated by a comma/slash and return two values separated by a comma/slash. In the example below I want to vlookup the two codes stated and return the two descriptions from source data. Any help would be much appreciated? Thanks Dan
| Codes | 14150,15600 |
| Descriptions | Maltesers Mini Bag 58g,Cadbury Dairy Milk 90g |
7 Replies
One day, if you are lucky, you may get to use a 365 license in which case
= MAP(codes, LAMBDA(v, TEXTJOIN(",",, XLOOKUP(TEXTSPLIT(v,","), products, description) ) ))will return the entire column of product descriptions.
=VLOOKUP(B9,CHOOSE({1,2},F5:F12&","&G5:G12,H5:H12&","&I5:I12),2,FALSE)Is this what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
OliverScheurich The formula does work as you intended, but unfortunately this is not what I need. Maybe the below image will give you a better idea. The 1st result shown is what I am looking for, with the ability to add further letters in up to a maximum of 5 characters, which is shown in result 2 = 3 characters - 3 different Bob's
Thanks for the help
In B3:
=TEXTJOIN(",",TRUE,FILTER($E$2:$E$10,ISNUMBER(SEARCH($D$2:$D$10,B2))))
In B7:
=TEXTJOIN(",",TRUE,FILTER($E$2:$E$10,ISNUMBER(SEARCH($D$2:$D$10,B6))))
OliverScheurich Thanks for your reply. I will give this a try and let you know.
