Lookup from a delimited field

[djfatboyfats]

Good morning,

Is it possible to perform a lookup on a delimited field?

See the picture below. I want to perform a lookup in cell B1 which takes all the values from cell A1 and performs a lookup on the table E1:F4 & return the value in column F

Example.JPG

The desired output in cell B1 would be as follows;

Result1, Result 2, Result 4

Thanks in advance

David

[Pete_UK]

You can use this formula in B1:

=SUBSTITUTE(IF(COUNTIF(A1,"*1*"),VLOOKUP(1,$E$1:$F$4,2,0)&", ","")&IF(COUNTIF(A1,"*2*"),VLOOKUP(2,$E$1:$F$4,2,0)&", ","")&IF(COUNTIF(A1,"*3*"),VLOOKUP(3,$E$1:$F$4,2,0)&", ","")&IF(COUNTIF(A1,"*4*"),VLOOKUP(4,$E$1:$F$4,2,0)&", ","")&"x",", x","")

Hope this helps.

Pete

[djfatboyfats]

Thanks Pete that's perfect.

[AliGW]

Before you mark this as solved, could I ask that you check that your profile is up-to-date? Are you still using 2016 or something newer? Thanks.

[djfatboyfats]

Pete, what if column E - F has over 200 rows to look upon, is there an easier way of doing this rather than writing a massive formula?

[Pete_UK]

Glad to help, and thanks for the rep.

Pete

[Pete_UK]

Pete, what if column E - F has over 200 rows to look upon ...

You just need to change the range of the table in the VLOOKUP terms, i.e. $E$1:$F$4 could become $E$1:$F$250

If you mean that you might have more strings of numbers in cell A1 then I think it would need another approach.

Pete

[djfatboyfats]

Hi Pete, I changed it to 250 but it's still not working. It is also picking up the 10 as a 1 (see below)

Attachment 740438

[Glenn Kennedy]

Assuming you're on a PC, and not a Mac... use this:

=TEXTJOIN(", ",,VLOOKUP(FILTERXML("<A><B>"&SUBSTITUTE(A1,",","</B><B>")&"</B></A>","//B"),E:F,2,FALSE))

[djfatboyfats]

Assuming you're on a PC, and not a Mac... use this:

=TEXTJOIN(", ",,VLOOKUP(FILTERXML("<A><B>"&SUBSTITUTE(A1,",","</B><B>")&"</B></A>","//B"),E:F,2,FALSE))

Hi Glenn, this is just what I needed. Thank you.

[djfatboyfats]

Hi Ali, this is done.

[Glenn Kennedy]

You're welcome & thanks for the feedback!