Multiple Criteria INDEX/MATCH not working

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
Using Excel 2019

Given a table on SHEET1, assume the following is range A1:B3

Main AccountSub-AccountJan-2020
DataInflow<Formula>

... and a table on a sheet called SHEET2, also assuming the following is range A1:B3

CustomerProduct LineJan-2020
SuppliesOutflow$150
DataInflow$300

Assume that cells D1 through N1 of SHEET2 are the rest of the months of the year.

I am trying the following INDEX/MATCH formula in SHEET1 B3, attempting to return the value in SHEET2 C3:

Code:
={INDEX(SHEET2!C2:N3,MATCH(1,(A2=SHEET2!A2:A3)*(B2=SHEET2!B2:B3)*(C1=SHEET2!C1:N1),0))}

Instead of returning $300, it returns #N/A.

This is where I learned about this particular syntax: Excel formula: INDEX and MATCH with multiple criteria | Exceljet

Can someone help me understand why this doesn't work? If there is another way to get INDEX/MATCH to work with the above criteria, I'm happy to use it. I'm wondering if the issue is because my first two sets of criteria are matching to vertical ranges, and the third one is matching to a horizontal range?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
How about
=INDEX(Sheet2!C2:N3,MATCH(A2&"|"&B2,INDEX(Sheet2!A2:A3&"|"&Sheet2!B2:B3,0),0),MATCH(C1,Sheet2!C1:N1,0))
This is a normal formula & does not need Ctrl Shift Enter
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
Works perfectly! Thanks Fluff. Now I just need to analyze this to make sure I understand what it's doing. This is the first time I've used an INDEX within an INDEX.

Thanks again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
It's actually the same as
=INDEX(Sheet2!C2:N3,MATCH(A2&"|"&B2,Sheet2!A2:A3&"|"&Sheet2!B2:B3,0),MATCH(C1,Sheet2!C1:N1,0))
but this version needs Ctrl Shift Enter, whereas adding the extra index function doesn't.
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119

ADVERTISEMENT

Fantastic, thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119

ADVERTISEMENT

Hi @Fluff - circling back with a little side note question for you on your formula. I'm trying to use INDIRECT so I can reference the sheet name from a cell. It's working fine for the 1st and 4th instances of Sheet2, but I'm having trouble constructing the expression for the 2nd and 3rd instances. Assuming cell Q1 contains the string "Sheet2", this is what I have so far:

Code:
=INDEX(INDIRECT("'"&Q1&"'!C2:N3"),MATCH(A2&"|"&B2,Sheet2!A2:A3&"|"&Sheet2!B2:B3,0),MATCH(C1,INDIRECT("'"&Q1&"'!C1:N1"),0))

This formula works fine. But when I start trying to create the INDIRECT expression for that middle MATCH statement, I can't get it to work. I'm sure I'm getting caught up somewhere with the single/double quotes and the ampersands.

Would you know how to do this?

For your reference, here is where I learned about INDIRECT for referencing sheet names from a cell: Use string value from a cell to access worksheet of same name
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
Never mind, I figured it out! I had to use two INDIRECT statements within that MATCH expression. I was trying to do it with one.

Thanks,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback
 

Forum statistics

Threads
1,136,864
Messages
5,678,212
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top