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

sungjinyun

New member
Joined
Jan 25, 2012
Messages
2
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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!
 

scenography

New member
Joined
Jan 29, 2012
Messages
4
Reaction score
0
Points
0
Location
San Francisco, California
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

New member
Joined
Jan 29, 2012
Messages
4
Reaction score
0
Points
0
Location
San Francisco, California
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.
 

Attachments

  • Sample of Substitute() as a lookup.xls
    23 KB · Views: 85

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :)
 
Top