Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Can VLookup return multiple hits from same column of data?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can VLookup return multiple hits from same column of data?

    I am running a Vlookup on a large set of ID#'s that exist accross multiple servers.
    The data is structured in (2) columns how you see below , and you will notice that the same ID# can show up on multiple servers.
    However, the VLOOKUP is only returning the first match it finds.
    Assuming that I want to keep the data structured as is, how do I make the vlookup return all matches?


    ID# LOCATION

    23 SERVER1
    34 SERVER4
    23 SERVER2
    13 SERVER1
    17 SERVER3
    34 SERVER1
    Last edited by nopicante; Jul 14th, 2009 at 12:55 PM.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,730
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    Assuming that A2:A7 contains the ID number, B2:B7 contains the server, and D2 contains the server of interest, try the following...

    E2:

    =COUNTIF(A2:A7,D2)

    F2, confirmed with CONTROL+SHIFT+ENTER, and copied across:

    =IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F2))),"")

  3. #3
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    It always helps to post some data and your expected results!!!
    To show your sheet on the board use Excel Jeanie or Beta HTML Maker from RichardSchollar

    When posting VBA Code use CODE tags ... eg. [CODE] <your vba code here> [ /CODE]
    To install code --> ALT-F11, Insert, Module...Paste the code from Sub <---> End Sub into the blank area

  4. #4
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    try this

    Sheet1
    ABCDE
    1ID#LOCATION23
    223SERVER1SERVER1
    334SERVER4SERVER2
    423SERVER2
    513SERVER1
    617SERVER3
    734SERVER1
    8


    Array Formulas
    CellFormula
    E2=IF(SUMPRODUCT(--($A$2:$A$7=E$1))>=ROWS(E$2:E2),INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=E$1,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(E$2:E2))),"")
    Entered with Ctrl+Shift+Enter

    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  5. #5
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,482
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    Quote Originally Posted by Domenic View Post
    F2, confirmed with CONTROL+SHIFT+ENTER, and copied across:
    Domenic...do you mean copy down?

    VlookupMult
    ABCDEFGHI
    1IDDataLookupCountifFormula Copy DownFormula Copy Across
    223a342bbb
    334bf
    423c
    513d
    617e
    734f
    8
    9
    10


    Worksheet Formulas
    CellFormula
    E2=COUNTIF(A2:A7,D2)

    Array Formulas
    CellFormula
    H2=IF(ROWS(H$2:H2)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(H$2:H2))),"")
    I2=IF(ROWS(I$2:I2)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(I$2:I2))),"")
    F2=IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F2))),"")
    F3=IF(ROWS(F$2:F3)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F3))),"")
    F4=IF(ROWS(F$2:F4)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F4))),"")
    F5=IF(ROWS(F$2:F5)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F5))),"")
    Entered with Ctrl+Shift+Enter


    It always helps to post some data and your expected results!!!
    To show your sheet on the board use Excel Jeanie or Beta HTML Maker from RichardSchollar

    When posting VBA Code use CODE tags ... eg. [CODE] <your vba code here> [ /CODE]
    To install code --> ALT-F11, Insert, Module...Paste the code from Sub <---> End Sub into the blank area

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,730
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    Quote Originally Posted by TheNoocH View Post
    Domenic...do you mean copy down?
    I'm not sure where that came from but, yes, I meant copy down. Thanks for catching that TheNoocH, much appreciated!

  7. #7
    New Member
    Join Date
    Jul 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    Thanks everyone for all the input.
    My situation is actually not even as in depth as some of the examples being provided so I'm going to provide the sample Vlookup. If an index is the answer, then I'll look to that but due to the simplicity of what I'm trying to do here I was hoping for a way to just use vlookup.

    While this entire process may seem ridiculous to accomplish such an easy task; it is a necessary evil as the system logging all the ID#'s does not contain their SERVER locations.

    So Sheet 1 is essentially a dump of ID's from the system and Sheet 2 is the directory of all servers capturing in (2) columns ID# and SERVER.

    That said the actual Vlookup is taking place in SHEET1 col 2, I would just like it to depict in the same cell every server match it encounters when it searches through SHEET2.
    (If Possible)
    If not, what is the easiest alternative?


    Assume (2) worksheets SHEET1 and SHEET2 each has (2) Columns

    SHEET1
    ID#'s SERVER/S


    SHEET2
    Dir of ID#'s SERVER



    Thanks again.

  8. #8
    Board Regular
    Join Date
    Mar 2009
    Location
    St. Louis, MO
    Posts
    1,561
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    The suggestions by TheNooch, Dominic, and Sanrv1f are the best solutions if you want to use a formula.

    You cannot use Vlookup because it works on the assumption that the value you are looking up will exist only 1 time in the list you are trying to find it in. The reason it will always return the first value is because once it looks through your list starting at the top and if it finds a match returns the corresponding value so it will never proceed past the first occurance.

  9. #9
    New Member
    Join Date
    Jul 2009
    Location
    United States of America
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    The above example was very helpful in solving my problem. My doubt is, as I am building database, I have left some empty cells at the bottom of array for future entries and trying to make this work but its not working.

    thanks in advance.

  10. #10
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,730
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Can VLookup return multiple hits from same column of data?

    Quote Originally Posted by luvuroop View Post
    The above example was very helpful in solving my problem. My doubt is, as I am building database, I have left some empty cells at the bottom of array for future entries and trying to make this work but its not working.

    thanks in advance.
    Based on the same assumption as my first post, try...

    E2:

    =SUMPRODUCT(--(A2:A7=D2),--(B2:B7<>""))

    F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D$2,IF($B$2:$B$7<>"",ROW($B$2:$B$7)-ROW($B$2)+1)),ROWS(F$2:F2))),"")

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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