Results 1 to 6 of 6

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    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 (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    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.

  3. #3
    where can I find the "go advanced"?

  4. #4
    Neophyte scenography's Avatar
    Join Date
    Jan 2012
    Location
    San Francisco, California
    Posts
    4
    Articles
    0
    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.

  5. #5
    Neophyte scenography's Avatar
    Join Date
    Jan 2012
    Location
    San Francisco, California
    Posts
    4
    Articles
    0
    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.
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Quote Originally Posted by scenography View Post
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •