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.