Return MULTIPLE corresponding values for TWO Lookup Value, Horizontally, in one Row

windycity_SP500

New Member
Joined
Feb 6, 2020
Messages
2
Office Version
2007
Platform
Windows
Similar to this link(reference the bottom)- I am trying to return multiple corresponding values with two look up conditions, horizontally, in one row. Then I can apply it down other rows.

Not sure how to go about this- I have been stuck for days. I provided a sample below:

IDNameQty
AClark
9​
aAnn
11​
pBob
50​
pBob
65​
aJoe
66​
oTy
4​
AJoe
4​
bJoe
80​

What I need is here:

Based on Name and ID shown below, I need to fetch the corresponding qty for those look up conditions. For instance, Joe with ID 'A', should output QTY 66 and 4.

NameIDqtyqtyqtyqtyqtyqtyqtyetc…
Joea
bobB

I am able to return such value for one condition, but that isn't the result I need. Any help would be greatly appreciated. Thank you!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,739
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
ZFluff.xlsm
ABCDEFGHIJKLMNO
1IDNameQtyNameIDqtyqtyqtyqtyqtyqtyqtyetc…
2AClark9Joea664
 
 
 
 
 
 
3aAnn11bobp5065
 
 
 
 
 
 
4pBob50joeb80
 
 
 
 
 
 
 
5pBob65
6aJoe66
7oTy4
8AJoe4
9bJoe80
Data
Cell Formulas
RangeFormula
H2:O4H2=IFERROR(INDEX($C$2:$C$9,AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$2)+1)/(($B$2:$B$9=$F2)*($A$2:$A$9=$G2)),COLUMNS($H1:H1))),"")
 

windycity_SP500

New Member
Joined
Feb 6, 2020
Messages
2
Office Version
2007
Platform
Windows
Hi & welcome to MrExcel.
How about
ZFluff.xlsm
ABCDEFGHIJKLMNO
1IDNameQtyNameIDqtyqtyqtyqtyqtyqtyqtyetc…
2AClark9Joea664
 
 
 
 
 
 
3aAnn11bobp5065
 
 
 
 
 
 
4pBob50joeb80
 
 
 
 
 
 
 
5pBob65
6aJoe66
7oTy4
8AJoe4
9bJoe80
Data
Cell Formulas
RangeFormula
H2:O4H2=IFERROR(INDEX($C$2:$C$9,AGGREGATE(15,6,(ROW($C$2:$C$9)-ROW($C$2)+1)/(($B$2:$B$9=$F2)*($A$2:$A$9=$G2)),COLUMNS($H1:H1))),"")
Thank you so much! This is what I needed. I appreciate it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,739
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,682
Messages
5,445,939
Members
405,370
Latest member
Theglyde

This Week's Hot Topics

Top