Index, Match & And function doesn't seem to be working when there is duplicate data

[penexcel]

Hi,
I am editing an existing data in a report with new data. Basically old data does not have column "city" and I would like to add city for analysis. I can't completely replace the old data due to calculation issues (it is a long explanation), so I am using Index, Match & and functions to bring in "City" from new data to the old data. But unfortunately, it is giving me incorrect data (and yes I am using Shift+Ctrl+Enter). Please see attached example.

I am assuming it is because of duplicate data but I can't get my head around it. I would really appreciate any help.

Thank you!

[azumi]

=INDEX('New Data'!$F$2:$F$8,MATCH(1,('New Data'!$A$2:$A$8=A2)*('New Data'!$B$2:$B$8=B2)*('New Data'!$C$2:$C$8=C2)*('New Data'!$D$2:$D$8=D2)*('New Data'!$E$2:$E$8=E2),0))

[azumi]

Or check the file

Azumi

[benishiryo]

welcome to the forum. it seems like the repeated item in New Data has the exact info in other columns. so maybe just:
=INDEX('New Data'!$F$2:$F$8,MATCH(A2,'New Data'!$A$2:$A$8,0))

or:
=VLOOKUP(A2,'New Data'!$A$2:$F$8,6,0)

but if you need to match every columns, then you need this array formula:

Please Login or Register  to view this content.

[penexcel]

Thank you, Azumi! Can you please tell me the what is the benefit of using Match(1,...) and is the formula basically sumproduct?