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

1. ## 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  Reply With Quote

2. ## 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))),"")  Reply With Quote

3. ## Re: Can VLookup return multiple hits from same column of data?  Reply With Quote

4. ## 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  Reply With Quote

5. ## Re: Can VLookup return multiple hits from same column of data? Originally Posted by Domenic 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  Reply With Quote

6. ## Re: Can VLookup return multiple hits from same column of data? Originally Posted by TheNoocH 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!  Reply With Quote

7. ## 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.  Reply With Quote

8. ## 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.  Reply With Quote

9. ## 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.  Reply With Quote

10. ## Re: Can VLookup return multiple hits from same column of data? Originally Posted by luvuroop 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.

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))),"")  Reply With Quote

## User Tag List

vlookup 