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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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))),"")
 
Upvote 0
try this
Excel Workbook
ABCDE
1ID#LOCATION23
223SERVER1SERVER1
334SERVER4SERVER2
423SERVER2
513SERVER1
617SERVER3
734SERVER1
8
Sheet1
#VALUE!
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))),"")
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top