Solved: Index / Match Multiple Criteria Issue

Good afternoon All

I have an issue in regard to my formula which uses a index / match with mutiple criteria.

In essence I want to reconcile the two sheets based on

1. Surname
2. Date of departure
3. Amount
4. From / departure point

I am using the following formula
[vba]
=IF(ISNA(AND(MATCH(File1!$A2,File2!$A$1:$A$6,0),MATCH(File1!$B2,File2!$B$1: $B$6,0), MATCH(File1!$C2,File2!$C$1:$C$6,0),MATCH(File1!$D2,File2!$D$1:$D$6,0))),"Ma tch","No Match")
[/vba]

The results should match the cell data but the logical arguments are not applied for some reason. The error is eluding me at present, woudl someone be kind to assist.

I have attached a test workbook to highlight my issue.
thanks in advance

Also I forgot to add that the original lookup sheet's (sheet1) data is assumed to be correct, and the sheet 2 is the comparable sheet which may / may not contain data from sheet1. Also sheet 1's data is correct for the whole row, not just for that one cell.

regards

=IF(ISNA(MATCH(1,(File2!$A$1:$A$6=File1!$A2)*(File2!$B$1:$B$6=File1!$B2)*(F ile2!$C$1:$C$6=File1!$C2)*(File2!$D$1:$D$6=File1!$D2),0)),"No Match","Match")

this is an array formula

XLD thank you for the reply

I had entered formula as an array (control/shift/enter) but alas it is still returning an incorrect result.

worksheet File1 cell A2, B2, C2, D2 are an exact match to worksheet File2 A2, B2, C2, D2, so the result should be a match. I receive a does not match... thus is my issue...

thanks again for your assistance

regards

D.

That is because your file has calculation mode set to manul. Set it to automatic.

XLD

thanks for that.. i'll check it in the morning... the simple things I forgot...

regards

D.