hi all,

I need to match Name & Country with range A1:D4 (table). If Name & Country is found in the same row as the range A1:D4 (table), return column C (Country) of range A1:D4 (table).

[B]A1:D4 (table)[/B]
Name     Type     Country     Costs
SHU HUI NG     AUD     Australia     2
SHU HUI NG     SGD     Singapore     2
MARILYNE CHIN     SGD     Taiwan     10

Example
Name: SHU HUI NG
Country: Singapore
Return value: Singapore

Name: SHU HUI NG
Country: Australia
Return value: Australia

Following formula does not work

=INDEX(C2:C4,MATCH(A9,(A2:A4=A9)*(C2:C4=B9),0),0) 
=VLOOKUP(A9,If(C2:C4=B9,A2:D4,""),1,False) 
=VLOOKUP(A9,IF(A9=A2:A4,A2:D4,""),MATCH(B9,C2:C4,0),FALSE)

Thank you.

Hi,

thank you for viewing, i just got the formula here:

=INDEX(C2:C4,MATCH(1,INDEX((A2:A4=A9)*(C2:C4=B9),0),0))

• Marked as answer by heyweed 17 hours 21 minutes ago

Hi,

Thanks for visiting our forum and sharing your experience here. It'll be benefited to our communicator.

Have a good time.

Regards,

George Zhao
TechNet Community Support