VLOOKUP from two columns, return first values which you find

[Manojlo]

I would like in column F to have values from columns B and D depend on unique values (car name) from column E.
Idea is to search for car models in column A and C, and than return first value (color) encountered. Like is on example.

 

[AhoyNC]

Try:
=XLOOKUP(E2,$A$2:$A$14,$B$2:$B$14,XLOOKUP(E2,$C$2:$C$14,$D$2:$D$14,"No Match",0,1),0,1)

 

[Peter_SSs]

In future, sample data/results with XL2BB would be helpful.

If you have the VSTACK function (not all 365 users have it yet but it is [slowly] being rolled out) ..

22 05 22.xlsm
	A	B	C	D	E	F
1
2	Lincoln	Red	Skoda	Red	Lincoln	Red
3	Lincoln	Blue	Smart	Pink	Maybach	Blue
4	Lincoln	Red	Smart	Orange	McLaren	Orange
5	Maybach	Blue	Suzuki	Purple	Nissan	Yellow
6	Maybach	Pink	Suzuki	Purple	Opel	Yellow
7	McLaren	Orange	Suzuki	Orange	Skoda	Blue
8	McLaren	Yellow	Subaru	Yellow	Smart	Pink
9	McLaren	Yellow	Subaru	Yellow	Suzuki	Purple
10	Nissan	Yellow	Toyota	Yellow	Subaru	Yellow
11	Nissan	Yellow	Toyota	Red	Toyota	Yellow
12	Opel	Yellow	Subaru	Red	Tesla	Red
13	Skoda	Blue	Tesla	Red
14	Skoda	Blue	Tesla	Brown
VSTACK

Cell Formulas
Range	Formula
E2:E12	E2	=UNIQUE(VSTACK(A2:A14,C2:C14))
F2:F12	F2	=VLOOKUP(E2#,VSTACK(A2:B15,C2:D15),2,0)
Dynamic array formulas.

 

[Manojlo]

Try:
=XLOOKUP(E2,$A$2:$A$14,$B$2:$B$14,XLOOKUP(E2,$C$2:$C$14,$D$2:$D$14,"No Match",0,1),0,1)

This works great, I don't have VSTACK function.

 

[Peter_SSs]

I don't have VSTACK function.

Fair enough. But if you adapt the suggestion from your other thread to create the unique list with a single formula in a single cell then you can also use the VLOOKUP formula from above just in a single cell without the need to copy either formula down the column.

22 05 22.xlsm
	A	B	C	D	E	F
1
2	Lincoln	Red	Skoda	Red	Lincoln	Red
3	Lincoln	Blue	Smart	Pink	Maybach	Blue
4	Lincoln	Red	Smart	Orange	McLaren	Orange
5	Maybach	Blue	Suzuki	Purple	Nissan	Yellow
6	Maybach	Pink	Suzuki	Purple	Opel	Yellow
7	McLaren	Orange	Suzuki	Orange	Skoda	Blue
8	McLaren	Yellow	Subaru	Yellow	Smart	Pink
9	McLaren	Yellow	Subaru	Yellow	Suzuki	Purple
10	Nissan	Yellow	Toyota	Yellow	Subaru	Yellow
11	Nissan	Yellow	Toyota	Red	Toyota	Yellow
12	Opel	Yellow	Subaru	Red	Tesla	Red
13	Skoda	Blue	Tesla	Red
14	Skoda	Blue	Tesla	Brown
VSTACK (3)

Cell Formulas
Range	Formula
E2:E12	E2	=UNIQUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,A2:A14,C2:C14)&"</b></a>","//b"))
F2:F12	F2	=VLOOKUP(E2#,VSTACK(A2:B15,C2:D15),2,0)
Dynamic array formulas.