Can VLookup return multiple hits from same column of data?

[nopicante]

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

 

[muggi]

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))),"")

Hi,
Hope I do note break any rules, by bringing back this old thread!
But is it possible to get this to also find ID 34, So if D2 contains ID "23" and D3 contains ID "34"
Would it be possible to edit the formular, so it would find and return these cells:

23 SERVER1
34 SERVER4
23 SERVER2
34 SERVER1

To sum up my question: can it find 2 different ID's instead of only 1 ID

 

[Domenic]

Hi,
Hope I do note break any rules, by bringing back this old thread!
But is it possible to get this to also find ID 34, So if D2 contains ID "23" and D3 contains ID "34"
Would it be possible to edit the formular, so it would find and return these cells:

23 SERVER1
34 SERVER4
23 SERVER2
34 SERVER1

To sum up my question: can it find 2 different ID's instead of only 1 ID

Assuming that A2:A10 contains the ID, B2:B10 contains the corresponding server, and D2 and D3 contain the IDs of interest, try...

E2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(ISNUMBER(MATCH(A2:A10,D2:D3,0)),1))

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

=IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$10,SMALL(IF(ISNUMBER(MATCH($A$2:$A$10,$D$2:$D$3,0)),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(F$2:F2))),"")

Hope this helps!

P.S. In future, though, best to start a new thread.

 

[muggi]

Very nice and a fast reply indeed!
Seems like it work like I want it to, so a big thank you from me.

And will remember in future post, to link to old post instead of posting in it.

 

[Domenic]

That's great, glad I could help.

Cheers!