Multiple Criteria INDEX/MATCH not working

[Nola111]

Using Excel 2019

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

Main Account	Sub-Account	Jan-2020
Data	Inflow	<Formula>

... 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:

={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?

 

[Fluff]

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]

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]

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]

Fantastic, thanks!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Nola111]

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:

=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]

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]

Glad you sorted it & thanks for the feedback