IF ISNA and INDEX MATCH together

**certain_death** · 02-11-2009, 10:08 AM

Hi I am using this formula which works fine, except when the amount isnt there in which case it returns an #N/A

=INDEX('Trade P'!A$4:Z$46,MATCH(A15,'Trade P'!A$4:A$46,0),MATCH("Sum of "&Cover!M$84,'Trade P'!A$4:Z$4,0))

I have previously used =IF(ISNA to get around this problem but I cant seem to put the two together. Can you help?

Basically I want to use the top formula but if the result is #N/A i want it to put a zero.

Any help really appreciated.
Thanks
cd:

**NBVC** · 02-11-2009, 10:11 AM

easiest way...

Please Login or Register  to view this content.

eDIT:

Added what_if_true = 0 condition

**NBVC** · 02-11-2009, 10:17 AM

If you know that it is for sure one of the 2 Matches always causing the NA... ie. if there is an NA, it is because the row match is always the one not found...then you can use something like:

Please Login or Register  to view this content.

Likewise, if it is the Column Match that is the one that causes the N/A

**DonkeyOte** · 02-11-2009, 10:24 AM

If you're result is always to be numerical (ie 0 for Error else INDEX result which is numerical) there is a slightly different approach you can take which avoids use of IFS and NA altogether:

Please Login or Register  to view this content.

Those running 2007 could of course use:

Please Login or Register  to view this content.

**certain_death** · 02-11-2009, 10:36 AM

wow

Thanks everybody.

I'll give them all a try

Thanks
Mark

**NBVC** · 02-11-2009, 10:38 AM

If you're result is always to be numerical (ie 0 for Error else INDEX result which is numerical) there is a slightly different approach you can take which avoids use of IFS and NA altogether:

Maybe, slightly more efficient... but the If/Na or If/Isnumber approach is more universal... (accomodates text and numerics) and is easier to understand... especially years from now, when one looks back to see how the solution came to be...