Results 1 to 2 of 2

Thread: Multiple VLOOKUP?

  1. #1

    Multiple VLOOKUP?

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


    There's a question: can I use vlookup to find several occurences of a value in a column? Is there a way to make excel repeat the VLOOKUP function until all positions are found or do I have to use VBA?

  2. #2
    Good morning,

    The way I usually accomplish this is with a helper column. I'll use this data set as an example:

    Category Name Helper = if ( CATEGORY = "A", ROW(), "")
    A Smith, John 2
    A Johnson, Adam 3
    B Smith, Sharon *blank*
    B Jones, Aaron *blank*
    A Jones, Sarah 6

    What this helper does is to create a unique list of autonumbers (by using row) of all the criteria I want. Then, you can use index-match to call the "names" you want, like so:

    = index ( NAME COLUMN, match( small( HELPER COLUMN, ROW()), HELPER COLUMN, 0 ))

    This formula will return the NAME field that corresponds to the smallest HELPER field in row 1, the second smallest in row 2, and so on. If you need header rows you can subtract them manually in the formula (i.e. small ( HELPER COLUMN, ROW() - **# of headers**). It's also wise to wrap this formula in an iferror, because when the formula has exhausted the number of correct answers it will return an error.

    Hope this helps,
    Last edited by bgoree09; 2014-06-18 at 02:35 PM. Reason: Had to format the text as a table.

Posting Permissions

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