Index Match Query

Any way that I could remove the #N/A with the below statement?

=INDEX('FIN PLAN'!$J$4:$J$99,MATCH('ASM Setting'!F60,'FIN PLAN'!$C$4:$C$99,FALSE))/Maintenance!P56

ABC 256,077
#N/A

Change it to this:

=IFERROR(INDEX('FIN PLAN'!$J$4:$J$99,MATCH('ASM Setting'!F60,'FIN PLAN'!$C$4:$C$99,FALSE))/Maintenance!P56,"")

Hope this helps.

Pete

That for all Excel version:

=IF(ISNA(MATCH('ASM Setting'!F60,'fin plan'!$C$4:$C$99,FALSE)),"",INDEX('fin plan'!$J$4:$J$99,MATCH('ASM Setting'!F60,'fin plan'!$C$4:$C$99,FALSE))/Maintenance!P56)

hi guys... thanks a lot... it's very useful...

Hi Guys,

sorry, if I would like to add another requirement like if Cell A has ZERO value, and it will jump to Cell B to capture the values

=IF(ISNA(MATCH('ASM Setting'!F6,'FIN PLAN'!$C$4:$C$99,FALSE)),"",INDEX('FIN PLAN'!$J$4:$J$99,MATCH('ASM Setting'!F6,'FIN PLAN'!$C$4:$C$99,FALSE))/Maintenance!P2)

Do you mean like this?

=IF(A6=0,B6,IF(ISNA(MATCH('ASM Setting'!F6,'FIN PLAN'!$C$4:$C$99,FALSE)),"",INDEX('FIN PLAN'!$J$4:$J$99,MATCH('ASM Setting'!F6,'FIN PLAN'!$C$4:$C$99,FALSE))/Maintenance!P2))

Hope this helps.

Pete

Hi Pete,

Thanks a lot for your help.

If I would like to use index and match with 2 criteria would that work? How do I go about that?

Try:
=IF(A6=0,B6,IF(ISNA(INDEX('FIN PLAN'!$J$4:$J$99,MATCH('ASM Setting'!F6&'ASM Setting'!G6,'FIN PLAN'!$C$4:$C$99&'FIN PLAN'!$D$4:$D$99,FALSE))),"",INDEX('FIN PLAN'!$J$4:$J$99,MATCH('ASM Setting'!F6&'ASM Setting'!G6,'FIN PLAN'!$C$4:$C$99&'FIN PLAN'!$D$4:$D$99,FALSE))/Maintenance!P2))

Just to add.confirm this formula with CONTROL+SHIFT+ENTER not just ENTER

=IF(ISNA(MATCH('ASM Setting'!$C7&'ASM Setting'!$E7,Maintenance!$AE$2:$AE$55&Maintenance!$AG$2:$AG$55,FALSE)),0,INDEX(Maintenance!$AJ$2:$AJ$55,MATCH('ASM Setting'!$C7&'ASM Setting'!$E7,Maintenance!$AE$2:$AE$55&Maintenance!$AG$2:$AG$55,FALSE)))

anyway that I could get the statement above show if #N/A will only empty cell...