Consulting

Results 1 to 5 of 5

Thread: Solved: Index / Match Multiple Criteria Issue

  1. #1
    VBAX Regular
    Joined
    May 2007
    Posts
    31
    Location

    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
    Last edited by demetre; 05-29-2008 at 08:06 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    =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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    May 2007
    Posts
    31
    Location
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That is because your file has calculation mode set to manul. Set it to automatic.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    May 2007
    Posts
    31
    Location
    XLD

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

    regards

    D.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •