vlookup two references from a single cell....

[Bookhimdanno]

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

[#2]

@Bookhimdanno 

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

 

[#3]

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

[#4]

@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

[#5]

@Bookhimdanno 

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))))

[#6]

@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

[#7]

@Bookhimdanno 

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.

[#8]

@Bookhimdanno 

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.