# Adding a third match to an Index Match Formula

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

3. ## 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)  Register To Reply

4. ## Re: Adding a third match to an Index Match Formula

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