3 equations all N/A

[dmheller]

I have 3 equations i have tried, i am trying to do an index match with month and year but they are all returning #N/A
=INDEX('Operating Modes'!C6:O150,match(month(B7)='Operating Modes'!B54:B150)*(year('semi annual'!C3)='Operating Modes'!A54:A150),MATCH('semi annual'!C4,'Operating Modes'!C5:O50,0) ctrl+shift+enter


=INDEX('Operating Modes'!C54:O150,MATCH(1,if(MONTH('Operating Modes'!B54:B150)=month(B7),if(YEAR('Operating Modes'!A54:A150)=year(C3))),0),MATCH('semi annual'!C4,'Operating Modes'!C5:O50,0)) ctrl+shift+enter

=INDEX('Operating Modes'!C54:O150,MATCH(1,if(MONTH('Operating Modes'!B54:B150)=month(B7)),if(YEAR('Operating Modes'!A54:A150)=year(C3)),0),MATCH('semi annual'!C4,'Operating Modes'!C5:O50,0)) ctrl+shift+enter

so to explain these, c54:O150 is a bunch of numbers that i want returned

B54:B150 is a list of months
Jan 1/1/2018
Feb 2/1/2018

B7 is a month
Jan 1/1/2018

A54:A150 is a list of years
2018 1/1/2018
2019 1/1/2019

C3 is a drop down of years
2018 1/1/2018
2019 1/1/2019

the last match is the top of the table. I got that part. Just not the date look up.

 

[Special-K99]

Re: 3 equations all N/A please help

Surely the #N/A error is informing you that one or both the MATCHes can't be found?
Your lack of an IFERROR implies that whatever value B7 and C3 are, they will ALWAYS be found.

Isolate the problem.
Don't just look at the data and say you can see the data - Excel does that using formulas so you need to find out why the formula(s) aren't working.

Remove the INDEX from the formula or place the TWO matches in blank cells on the so you can see what values they are returning and which of the MATCHes is causing the #N/A.

 

[dmheller]

Re: 3 equations all N/A please help

thanks, that helped some. I found one error in the second match it should be O5 not O50. the first match is saying the equation is missing something. Any idea how to write it to match to items?

 

[Special-K99]

Re: 3 equations all N/A please help

Your first formula doesnt conform to the other two

You have MATCH(MONTH(B7)...
The other two formulas begin

MATCH(1,IF(...

More importantly you have no "value if true" in the MATCH(1,IF(... formulas

MATCH(1,IF(month(...)=month(),IF(YEAR(...)=YEAR()... then what? there's no value if true nor if false