vertical lookup for multiple values in one cell AND return multiple results.

[sungjinyun]

Hello I have a cell in one sheet containing some values separated by a comma

1, 2, 3...etc

I have another excel workbook that has codes:

cell a2 has (codes column):
1, 2, 3,...etc.

cell b2 has (code translation column)
A, B, C...etc

What is a good vertical lookup formula to use so that in the original sheet it will return all the values of that cell? I know how to make one vertical lookup formula, but it will only work if there is only one value in the cell. If it has up to 8 values, I need the 8 corresponding results. HELP PLEASE!

[Ken Puls]

Hi there,

Can you create a sample workbook and attach it for us? (Click Go Advanced to do this) I think I follow what you're asking, but I just want to be 100% sure before I look at this.

Thanks!

[sungjinyun]

where can I find the "go advanced"?

[scenography]

Click the Reply to Thread button.
Click the Go Advanced button, which is below the text entry window. The advanced message editor opens.
Scroll down to the Additional Options section, which is below the Message section.
Click the Manage Attachments button. The File Upload Manager opens.

[scenography]

You can use the Substitute() function to look up values. Use a nested Substitute() function for each row in the lookup table.

In the attached example, the lookup table has two columns (E and F) and four rows (2, 3, 4, and 5). The four Substitute() functions in the following formula refer to the four rows.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $E$2, $F$2), $E$3, $F$3), $E$4, $F$4), $E$5, $F$5)

If the lookup table has values that contain other values, such as "12" and "2", then "12" must be replaced before "2". Sort the lookup table so that the longer values precede the shorter values.

If the lookup table has more than 64 rows, then a different approach is necessary, because only 64 functions can be nested in a formula in Excel 2007 and Excel 2010.

[Ken Puls]

Click the Reply to Thread button.
Click the Go Advanced button, which is below the text entry window. The advanced message editor opens.

Thanks for picking this up scenography!

sungjinyun, you can also just double-click the Reply to Thread button and it will open up the advanced editor as well.