+ Reply to Thread
Results 1 to 4 of 4

Adding a third match to an Index Match Formula

  1. #1
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114

    Adding a third match to an Index Match Formula

    I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores.

    I currently am using the following formula in the reporting sheet:

    Please Login or Register  to view this content.
    What this is doing is looking in my Stores sheet in column A and finding the match in cell A4 in my reporting sheet. It's then looking again in my Stores sheet in row 8 for a match to cell R3 in my reporting sheet. When it finds both criteria it's then giving the value of the column and row that match.

    What I need to do is modify the formula to add another matching criteria to look in column F in the Stores sheet and match it with cell C4 in my reporting sheet. The reason for this is that in my Stores sheet column A can have the same value repeated so I need it to also match column F. I would want the result to be the cell in the Stores sheet that matches what is in cell A4, C4, and R3 in the reporting sheet.

    Any help would be greatly appreciated. Thanks
    Last edited by Weasel; 10-07-2009 at 01:51 PM.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Adding a third match to an Index Match Formula

    If you're using Excel 2007 for the PC or Excel 2008 for the Mac, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

    =IFERROR(INDEX(Stores!$A$7:$CA$1000,MATCH(1,IF(Stores!$A$7:$A$1000=$A4,IF(Stores!$F$7:$F$1000=$C4,1)),0),MATCH(R$3,Stores!$A$8:$CA$8,0)),0)

    Otherwise, try...

    =IF(ISNUMBER(MATCH(1,IF(Stores!$A$7:$A$1000=$A4,IF(Stores!$F$7:$F$1000=$C4,1)),0)),INDEX(Stores!$A$7:$CA$1000,MATCH(1,IF(Stores!$A$7:$A$1000=$A4,IF(Stores!$F$7:$F$1000=$C4,1)),0),MATCH(R$3,Stores!$A$8:$CA$8,0)),0)

    ...also confirmed with CONTROL+SHIFT+ENTER. Actually, if the formula is suppose to return a numerical value when the data is available, the following should be more efficient...

    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(Stores!$A$7:$CA$1000,MATCH(1,IF(Stores!$A$7:$A$1000=$A4,IF(Stores!$F$7:$F$1000=$C4,1)),0),MATCH(R$3,Stores!$A$8:$CA$8,0))))

    ...also confirmed with CONTROL+SHIFT+ENTER.
    Last edited by Domenic; 10-06-2009 at 10:30 PM.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Adding a third match to an Index Match Formula

    You can try this non-array formula option which indexes columns A & F together:

    =IF(ISNUMBER(MATCH($A4 & $F4, INDEX(Stores!$A$1:$A$1000 & Stores!$F$1:$F$1000, 0), 0)), INDEX(Stores!$A$1:$CA$1000, MATCH($A4 & $F4, INDEX(Stores!$A$1:$A$1000 & Stores!$F$1:$F$1000, 0), 0), MATCH(R$3, Stores!$A$8:$CA$8, 0)), 0)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Forum Contributor
    Join Date
    09-13-2005
    Posts
    114

    Re: Adding a third match to an Index Match Formula

    Thank you both for your help. All the idea's worked great

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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