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!
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!
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!
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
where can I find the "go advanced"?
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.
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, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks