PDA

View Full Version : Solved: Index / Match Multiple Criteria Issue

demetre
05-29-2008, 06:59 AM
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

Surname
Date of departure
Amount
From / departure pointI am using the following formula

=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))),"Match","No Match")

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.

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

Bob Phillips
05-29-2008, 08:14 AM
=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

demetre
05-29-2008, 08:28 AM
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...

regards

D.

Bob Phillips
05-29-2008, 08:48 AM
That is because your file has calculation mode set to manul. Set it to automatic.

demetre
05-29-2008, 10:15 AM
XLD

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

regards

D.