Lookup value with multiple values in one cell

[red5_00]

I have a column that has multiple values in each cell separated by a comma (i.e. Z2_2,Z2_3,Z2_4). All of these are in sheet 1, column A

I then have a reference sheet (sheet2) of products... (i.e. z1 - received as gift) in 2 different columns (A, B) reference table is B2:C261 .

I want a formula to tell me that my data cell is: (apples, oranges, airplane)...


<table width="727" border="0" cellpadding="0" cellspacing="0"><tr height="19"><td style="height:14.4pt;width:367pt" width="489" height="19">
</td><td style="width:178pt" width="238">
</td> </tr></table>

 

[Gerald Higgins]

Hi, welcome to the board.

Your request is so vague that I think you are unlikely to get a useful answer unless you can clarify what you want.

For a start, how do you link "apples, oranges, airplane" to "z1", "z2" and so on ?

 

[red5_00]

ok....

in sheet one, row A2, I have a value of "Z2_2,Z2_3,Z2_4"

in sheet 2, I have 2 columns (code, description)

Code (column 1) has 261 rows with single entries like (Z2_2) (Z2_3), so on
Description (column 2) has 261 rows with single entries like (apples) (oranges)

so the formula would tell me that for Sheet1 row A2, instead of the codes, I would have "apples, oranges, bananas"

 

[Gerald Higgins]

I think I'm starting to understand.

What's the maximum possible number of values in the cell ?
And are the codes all the same format, i.e. four characters long ?

If the answers are three and yes, then you can do something like this...

=vlookup(left(a2,4),sheet2!a1:b261,2,false)&", "
&vlookup(mid(a2,5,4),sheet2!a1:b261,2,false)&", "
&vlookup(right(a2,4),sheet2!a1:b261,2,false)

I have not tested this, but I think this will work as long as you have exactly three values.

Try it, and tell me if it works for examples with three values.
Then we can tailor it for situations where the number of values varies.