Using Excel 2019
Given a table on SHEET1, assume the following is range A1:B3
... and a table on a sheet called SHEET2, also assuming the following is range A1:B3
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:
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?
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:
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?