Jul 01 2022 04:03 AM
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 |
Jul 02 2022 05:29 AM
=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.
Jul 02 2022 09:07 AM
@OliverScheurich Thanks for your reply. I will give this a try and let you know.
Jul 04 2022 01:03 PM
@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
Jul 04 2022 01:14 PM
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))))
Jul 05 2022 10:49 AM
@Hans Vogelaar thanks for the reply. I realised quite quickly that excel 2016 does not support the textjoin function, so I managed to find a vba work around, but then also realised that it does not support the filter function either. Frustrating.
I am not sure if you can offer a workaround to this problem?
I dont have much choice in regards to the version of excel I use, because it is on our businesses systems.
Thanks
Jul 05 2022 12:45 PM
If you know how to join the text with with vba, you may use
=INDEX( E2:E10, MATCH( FILTERXML("<t><s>" & SUBSTITUTE(B2, ",", "</s><s>") &"</s></t>", "//s"), D2:D10, 0) )
and textjoin the result.
FILTERXML splits content of B2 on elements, MATCH finds their positions and INDEX returns related names. Perhaps you need Ctrl+Shift+Enter, depends on how vba textjoin works.
Jul 05 2022 02:47 PM
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.