Results 1 to 9 of 9

Thread: INDEX and MATCH Formula??

  1. #1

    INDEX and MATCH Formula??



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, Could someone help? What I am trying to do is lookup the name in F4 then match this in column A (A1:A26). I then want to match G4 to see if it is on the same row as H4 then give an answer "TRUE" or"FALSE" in cell F7. This Match will only occur once in the data. I apologise if this has been asked before.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =INDEX($C$1:$C$26,MATCH(F4,$A$1:$A$26,0))=H4


  3. #3

    INDEX and MATCH Formula??

    Quote Originally Posted by NBVC View Post
    Try:

    =INDEX($C$1:$C$26,MATCH(F4,$A$1:$A$26,0))=H4
    Cheers this seems to work, However if I transfer the formula on to another worksheet (changing the parameters) it doesn't, please see attached. Sorry for not posting the original worksheet in the first place but I was trying to understand how the formula worked (been trying for about a week now)
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean you want to put the 2 names to compare and formula in Sheet2?

    If so, and assuming the names are in Sheet2, A1 and C1, then try formula, in Sheet2:

    =INDEX(Sheet1!$C$1:$C$91,MATCH(A1,Sheet1!$A$1:$A$91,0))=C1
    Attached Files Attached Files


  5. #5
    No this didn't work, when I changed the names in Sheet2, A1 to Hylton_CW and in Sheet2, C1 to Ryhope it came up FALSE but there is a match in row 32

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    For me it works. Did you make sure to enable editing after you opened my spreadsheet (if you have 2013)?

    Make sure you spelled the names correctly (or consistently).


  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Ok. I just looked at it a gain, you have multiple cells with Hylton_CW in column A... you are looking for the combination....

    Change my formula in Sheet2 to:

    =COUNTIFS(Sheet1!$A$1:$A$91,A1,Sheet1!$C$1:$C$91,C1)>0


  8. #8
    Yes, enabled editing, checked the spelling and even copied and pasted the cells from sheet1 A23 and sheet1 C32, in to sheet2 A1 and C1 but still getting FALSE
    Attached Files Attached Files

  9. #9
    My apologies, our messages must have crossed, just updated the formula and it looks as if it is working OK, Thanks for your help, I will now study the formula (I WILL NOT MAKE ANY CHANGES TO IT) and see if I can understand it. THANK AGAIN much appreciated.

Posting Permissions

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