Can VLookup return multiple hits from same column of data?

nopicante

New Member
Joined
Jul 9, 2009
Messages
4
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:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,173
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))),"")
 

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
try this
Excel Workbook
ABCDE
1ID#LOCATION23
223SERVER1SERVER1
334SERVER4SERVER2
423SERVER2
513SERVER1
617SERVER3
734SERVER1
8
Sheet1
#VALUE!
 

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
F2, confirmed with CONTROL+SHIFT+ENTER, and copied across:
Domenic...do you mean copy down?
Excel Workbook
ABCDEFGHI
1IDDataLookupCountifFormulaCopyDownFormulaCopyAcross
223a342bbb
334bf
423c
513d
617e
734f
8
9
10
VlookupMult
Cell Formulas
RangeFormula
E2=COUNTIF(A2:A7,D2)
#VALUE!
 

nopicante

New Member
Joined
Jul 9, 2009
Messages
4
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.
 

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
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.
 

luvuroop

New Member
Joined
Jul 22, 2009
Messages
1
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,173
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,576
Messages
5,469,489
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top