VLOOKUP - Return multiple values vertically based on 2 drop down list.

[RahulNa]

Hi Guys,

I hope all of you are safe and doing good.

I have an excel which has 2 drop-down lists "Sales Person Name" and "Sales Type (Existing & New)" based on the above I want to display their Unique account name.

Please share any formula or view to help me with this. Also, don't require VBA for the same as the file will be kept at a SharePoint where it might not work.

 

[Toadstool]

Hi RahulNa,

Here's one way:

RahulNa.xlsx
	A	B	C	D	E	F	G	H	I
1	Sales Person Name	Sales Type (Existing & New)	Account Name		Sales Person Name	Sales Type (Existing & New)		Sales Person Name	Sales Type (Existing & New)
2	Susan	Red	Tiger		Susan	Blue		Susan	Red
3	Susan	Blue	Mouse					Robert	Blue
4	Sarah	Blue	Mole		Account Name =	Mouse		Charles	Yellow
5	Vanessa	Red	Goat					Vanessa
6	Vanessa	Red	Sheep					Sarah
7	Paula	Yellow	Dog					Alfred
8								Vincent
9								Paula
Sheet1

Cell Formulas
Range	Formula
F4	F4	=IFERROR(INDEX(C1:C10,AGGREGATE(15,6,ROW(A$1:A$10)/((A1:A10=E2)*(B1:B10=F2)),1)),"No match")

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$H$2:$H$9
F2	List	=$I$2:$I$4

 

[RahulNa]

Hi

Thank you for the response. I am looking to find something like in color green.

The formula mentioned above works only for the first record identified.

Sales Person Name	Sales Type (Existing & New)	Account Name		Sales Person Name	Sales Type (Existing & New)
Susan	Red	Tiger		Susan	Blue
Susan	Blue	Mouse
Sarah	Blue	Mole		Account Name =	Tiger
Vanessa	Red	Goat			Mouse
Vanessa	Red	Sheep			Jaguar
Paula	Yellow	Dog			Lion
Susan	Red	Jaguar
Susan	Blue	Lion
Sarah	Blue	Rat
Vanessa	Red	Goat
Vanessa	Red	Sheep
Paula	Yellow	Dog

 

[Fluff]

Can you explain why you have Tiger & Jaguar in the result, as they are not Susan & Blue?

Also please update your account profile to show what version of Excel you are using. As some functions do not exist in all versions.

 

[RahulNa]

Can you explain why you have Tiger & Jaguar in the result, as they are not Susan & Blue?

Also please update your account profile to show what version of Excel you are using. As some functions do not exist in all versions.

Hi,

Sorry, It was a copy-paste issue. Currently, I am using Office 365 pro plus.

I hope you got my concern from the above formula.

 

[Fluff]

Ok, thanks for updating your profile.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	Sales Person Name	Sales Type (Existing & New)	Account Name		Sales Person Name	Sales Type (Existing & New)
2	Susan	Red	Tiger		Susan	Blue
3	Susan	Blue	Mouse
4	Sarah	Blue	Mole		Account Name =	Mouse	Mouse
5	Vanessa	Red	Goat			Lion	Lion
6	Vanessa	Red	Sheep
7	Paula	Yellow	Dog
8	Susan	Red	Jaguar
9	Susan	Blue	Lion
10	Sarah	Blue	Rat
11	Vanessa	Red	Goat
12	Vanessa	Red	Sheep
13	Paula	Yellow	Dog
List

Cell Formulas
Range	Formula
F4:F5	F4	=FILTER(C2:C13,(A2:A13=E2)*(B2:B13=F2))
G4:G9	G4	=IFERROR(INDEX($C$2:$C$13,AGGREGATE(15,6,(ROW($C$2:$C$13)-ROW($C$2)+1)/(($A$2:$A$13=$E$2)*($B$2:$B$13=$F$2)),ROWS(G$4:G4))),"")
Dynamic array formulas.

Formula in G4 is if you don't have dynamic arrays yet.

 

[RahulNa]

Ok, thanks for updating your profile.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	Sales Person Name	Sales Type (Existing & New)	Account Name		Sales Person Name	Sales Type (Existing & New)
2	Susan	Red	Tiger		Susan	Blue
3	Susan	Blue	Mouse
4	Sarah	Blue	Mole		Account Name =	Mouse	Mouse
5	Vanessa	Red	Goat			Lion	Lion
6	Vanessa	Red	Sheep
7	Paula	Yellow	Dog
8	Susan	Red	Jaguar
9	Susan	Blue	Lion
10	Sarah	Blue	Rat
11	Vanessa	Red	Goat
12	Vanessa	Red	Sheep
13	Paula	Yellow	Dog
List

Cell Formulas
Range	Formula
F4:F5	F4	=FILTER(C2:C13,(A2:A13=E2)*(B2:B13=F2))
G4:G9	G4	=IFERROR(INDEX($C$2:$C$13,AGGREGATE(15,6,(ROW($C$2:$C$13)-ROW($C$2)+1)/(($A$2:$A$13=$E$2)*($B$2:$B$13=$F$2)),ROWS(G$4:G4))),"")
Dynamic array formulas.

Formula in G4 is if you don't have dynamic arrays yet.

Hi,

I am getting the below error message When I edited the formula.

#SPILL!

 

[Fluff]

In that case you have cells below the formula that contain values or a formula. Clear out those cells & the formula should spill down.

 

[RahulNa]

Thank You. But is it possible in one single formula.

 

[Fluff]

In that case can you please use the XL2BB add-in to post your data (including formula) to the thread.