vlookup two references from a single cell....

New Contributor



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



Is this what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.


@Quadruple_Pawn Thanks for your reply. I will give this a try and let you know. 

@Quadruple_Pawn 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:




In B7:



@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.




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.


One day, if you are lucky, you may get to use a 365 license in which case

= MAP(codes, LAMBDA(v,
          XLOOKUP(TEXTSPLIT(v,","), products, description)

will return the entire column of product descriptions.