# Multiple Criteria INDEX/MATCH not working

#### Nola111

##### Board Regular
Using Excel 2019

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

 Main Account Sub-Account Jan-2020 Data Inflow

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

 Customer Product Line Jan-2020 Supplies Outflow \$150 Data Inflow \$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
=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
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
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

Fantastic, thanks!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### Nola111

##### Board Regular

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
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
Glad you sorted it & thanks for the feedback

Replies
5
Views
105
Replies
7
Views
363
Replies
3
Views
92
Replies
4
Views
240
Replies
3
Views
851

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.

### Which adblocker are you using?

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

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