triple v-lookup ./ index match ? ?

[Cervani]

Hello,
I have following issue, could you advise me please.
I need to VLOOKUP Value to lookup and Value to lookup 2 from table on the right, problem is
I have duplicated values in Code, then they need to match Team column match office column.

Please take a look at Result Value to lookup 2 for "QC team" some of them have DE and some CN as per values from table on the right.
Is there a way to build formula please ?

Code	Team	Office	Result Value to lookup	Result Value to lookup 2
27/J	QC team	Sacramento	Not completed	DE
27/J	Marketing team	New York	Completed	US
27/J	QC team	Denver	Not completed	CN
27/J	QC team	Denver	Not completed	CN
27/J	QC team	Denver	Not completed	CN
27/J	Marketing team	New York	Completed	US
Table 1	Results I want to get

Code	Team	Office	Value to lookup	Value to lookup 2
27/J	Marketing team	New York	Completed	US
27/J	QC team	Sacramento	Not completed	DE
27/J	QC team	Denver	Not completed	CN

 

[igold]

You're using 365, so you can use XLookUp...

Book1
	A	B	C	D	E	F
1	Code	Team	Office	Result Value to lookup	Result Value to lookup 2
2	27/J	QC team	Sacramento	Not completed	DE
3	27/J	Marketing team	New York	Completed	US
4	27/J	QC team	Denver	Not completed	CN
5	27/J	QC team	Denver	Not completed	CN
6	27/J	QC team	Denver	Not completed	CN
7	27/J	Marketing team	New York	Completed	US
8
9
10
11	Code	Team	Office	Value to lookup	Value to lookup 2
12	27/J	Marketing team	New York	Completed	US
13	27/J	QC team	Sacramento	Not completed	DE
14	27/J	QC team	Denver	Not completed	CN
15
16
17
Sheet1

Cell Formulas
Range	Formula
D12:D14	D12	=XLOOKUP(C12,$C$2:$C$7,$D$2:$D$7)
E12:E14	E12	=XLOOKUP(C12,$C$2:C$7,E$2:E$7)

 

[Cervani]

Hi there, sorry its my fault...


this is as data I already have

this is what I want to get

 

[igold]

How about this:

xlookup2.xlsm
	A	B	C	D	E	F
1	Code	Team	Office	Value to lookup	Value to lookup 2
2	27/J	Marketing team	New York	Completed	US
3	27/J	QC team	Sacramento	Not completed	DE
4	27/J	QC team	Denver	Not completed	CN
5
6
7
8	Code	Team	Office	Result Value to lookup	Result Value to lookup 2
9	27/J	QC team	Sacramento	Not completed	DE
10	27/J	Marketing team	New York	Completed	US
11	27/J	QC team	Denver	Not completed	CN
12	27/J	QC team	Denver	Not completed	CN
13	27/J	QC team	Denver	Not completed	CN
14	27/J	Marketing team	New York	Completed	US
15
16
Sheet2

Cell Formulas
Range	Formula
D9:D14	D9	=XLOOKUP(C9,$C$2:$C$4,$D$2:$D$4,"")
E9:E14	E9	=XLOOKUP(C9,$C$2:$C$4,$E$2:$E$4,"")

 

[igold]

I am glad it worked for you. Thanks for the feedback!