+ Reply to Thread
Results 1 to 18 of 18

multiple match/index formula

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    multiple match/index formula

    i need to retrieve a number from a spreadsheet but only if two values are found to match. i've attached an example that shows my dilemma. i'm trying to think of a way but i can't figure it out. what i've come up with in my head is something like a If(And(Index(Match)), Index(Match)) function.

    in the example file: if D matches a value in A AND E matches B, then retrieve the value in F.

    any help would be appreciated!

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    anguyen27,

    Welcome to the forum!
    If you meant to attach a file, it looks like it got missed because there is no attachment. As for your question, something like this should work for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    oops, here's the file. i tried what you suggested and it didn't work.
    Attached Files Attached Files
    Last edited by Cutter; 09-18-2012 at 11:59 AM. Reason: Removed whole post quote

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    anguyen27,

    What are the expected results and where do they go? (Column C? Column G? Elsewhere?) The only ones that match are 4d (CC) and 8h (FF). Because of that, all the others would result in an error. What you like instead of an error?

  5. #5
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    lets say for this example, they'd just go in H. you're right, only those two would match. it doesn't matter if the matches are on the same row, but i just want F to be returned if D matches a value in A and E matches the adjacent value in B.
    Last edited by Cutter; 09-18-2012 at 11:59 AM. Reason: Removed whole post quote

  6. #6
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    this is what i had previously but i misunderstood the requirements. before i thought i would have to match one value in one column with a value in another sheet. if there wasn't, it'd just check the next column (up to 4 columns).

    =IF(ISNA(INDEX(Sheet1!$E$2:$E$17349, MATCH(AJ2, Sheet1!$D$2:$D$17349, 0))), IF(ISNA(INDEX(Sheet1!$E$2:$E$17349, MATCH(BN2, Sheet1!$D$2:$D$17349, 0))), IF(ISNA(INDEX(Sheet1!$E$2:$E$17349, MATCH(AI2, Sheet1!$C$2:$C$17349, 0))),IF(ISNA(INDEX(Sheet1!$E$2:$E$17349, MATCH(AH2, Sheet1!$B$2:$B$17349, 0))),"",INDEX(Sheet1!$E$2:$E$17349, MATCH(AH2, Sheet1!$B$2:$B$17349, 0))), INDEX(Sheet1!$E$2:$E$17349, MATCH(AI2, Sheet1!$C$2:$C$17349, 0))), INDEX(Sheet1!$E$2:$E$17349, MATCH(BN2, Sheet1!$D$2:$D$17349, 0))), INDEX(Sheet1!$E$2:$E$17349, MATCH(AJ2, Sheet1!$D$2:$D$17349, 0)))

    but now we want two matches so if D matches a value in A and E matches the adjacent B, then give us F

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    anguyen27,

    Attached is a modified version of your example workbook. In cell H1 and copied down is this formula:
    Please Login or Register  to view this content.
    Note this is basically identical to the formula I provided earlier.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: multiple match/index formula

    hi,
    you can use another formula below:
    Please Login or Register  to view this content.
    see attached file...
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    @ tigeravatar

    thanks, for some reason i didn't think it worked earlier. now i modified it to be:

    =IF(ISNA(INDEX($F$1:$F$6,MATCH(1,INDEX(($A$1:$A$6=D1)*($B$1:$B$6=E1),),0))), "", INDEX($F$1:$F$6,MATCH(1,INDEX(($A$1:$A$6=D1)*($B$1:$B$6=E1),),0)))

    now i have to try X match AND (Y match or Z match). that's going to be pretty long.
    Last edited by Cutter; 09-18-2012 at 12:00 PM. Reason: Removed whole post quote

  10. #10
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    i'm assuming the * is AND, how do i do OR?

  11. #11
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    anguyen,

    Can you provide a sample workbook showing the new data and expected results?

  12. #12
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    here you go
    Attached Files Attached Files
    Last edited by Cutter; 09-18-2012 at 12:00 PM. Reason: Removed whole post quote

  13. #13
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    I don't get it, don't they all match?

    In row 1, you have 1a9 and 1b9. 1=1, and 9=9, so its a match
    In row 2, you have 3c6 and 3c5. 3=3 and c=c so its also a match.

    So in the provided workbook, they are all matches. Is that true? In that case, you can use this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how the formula gets surrounded by the curly braces {}. Don't try to add those yourself.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    I just changed it up, I guess I didn't change enough. Here's the new edit.
    Attached Files Attached Files

  15. #15
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    In cell L1 and copied down:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    that almost helped. why is the lookup value in the Match function = 1? Match(1,

  17. #17
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: multiple match/index formula

    Because this part creates an array of 1 (both were true) and 0 (at least one of the conditions was not met) values:
    Please Login or Register  to view this content.
    The Match is looking for the first instance where both were true, so it is matching a 1 against an array of 1's and 0's. (There will almost always only be a single 1).

  18. #18
    Registered User
    Join Date
    09-11-2012
    Location
    northern va
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: multiple match/index formula

    so i'm trying to implement this in my working file. it was a little more complicated than i thought. i think it's right but will update after i check with the file owner.

    =IFERROR(INDEX(Sheet1!$E$2:$E$17349,MATCH(1,INDEX((Sheet1!$B$2:$B$17349=$AH2)*(Sheet1!$D$2:$D$17349=$BN2),),0)),IF(OR(ISBLANK(AI2),ISBLANK(AJ2)),"",IFERROR(INDEX(Sheet1!$E$2:$E$17349,MATCH(1,INDEX((Sheet1!$C$2:$C$17349=$AI2)*(Sheet1!$D$2:$D$17349=$AJ2),),0)),IFERROR(INDEX(Sheet1!$E$2:$E$17349,MATCH(1,INDEX((Sheet1!$C$2:$C$17349=$AI2)*(Sheet1!$D$2:$D$17349=$BN2),),0)),""))))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1