efficient way to lookup and return multiple values?

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
440
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,318
post an example of source data and expected result (in table form)
 

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
440
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

New Member
Joined
Nov 6, 2019
Messages
1
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,020
Messages
5,466,085
Members
406,464
Latest member
buks1232000

This Week's Hot Topics

Top