efficient way to lookup and return multiple values?

[daveyc18]

currently i i want to look up a unique security ID to another tab and return the inventories its in.

the problem with obviously vlookup is that it would only return the first inventor result found.....what would be a way to return all the inventories the security ID is in? i suppose this can't be done in one shot.

 

[sandy666]

post an example of source data and expected result (in table form)

 

[daveyc18]

post an example of source data and expected result (in table form)

=VLOOKUP(E15,'DTR Pivot'!A:B,2,0)

where e15 has the security ID

in the DTR Pivot tab, column A has the security ID, but column B has the inventory number.....but as i said, in the pivot, the security ID can have multiple inventory numbers and vlooup would only return the first result in column B

 

[Edward6266]

Assume, your E15 was the first of security ID, and E16 was its second, and further E17 was its third, etc..., try:

F15=INDEX('DTR Pivot'!$B$1:$B$2000,MATCH(E15,'DTR Pivot'!$A$1:$A$2000,)-1+COUNTIF($E$1:E15,E15))
(Enter only)

Hope correct!

 

[Fazza]

Sounds like an ideal job for a query. Works like a database query.
Simpler but may not perfectly suit what you want is a pivot table.