replace #NA with zero in Index/Match formula!!

[ADB_BL]

hello all.
stuck with an N/A and tried multiple times to hack my way out of it.
close to head pounding on pavement stage.
somebody please help!

=INDEX('Sheet1'!$R$4:$T$13,MATCH(B12,'Sheet1'!$Q$4:$Q$13,0),MATCH(D12,'Sheet1'!$R$3:$T$3,0))

 

[T. Valko]

hello all.
stuck with an N/A and tried multiple times to hack my way out of it.
close to head pounding on pavement stage.
somebody please help!

=INDEX('Sheet1'!$R$4:$T$13,MATCH(B12,'Sheet1'!$Q$4:$Q$13,0),MATCH(D12,'Sheet1'!$R$3:$T$3,0))

The best solution depends on the answers to these questions:

What version of Excel are you using?

What type of data does the formula normally return? Is it text? Numeric? Could be either?

 

[ADB_BL]

Hi there, thanks.

Using MS Office 2007.
Return value is a plain vanilla number!

Thanks,
Brian

 

[T. Valko]

Hi there, thanks.

Using MS Office 2007.
Return value is a plain vanilla number!

Thanks,
Brian

Try this...

=IFERROR(INDEX(Sheet1!$R$4:$T$13,MATCH(B12,Sheet1!$Q$4:$Q$13,0),MATCH(D12,Sheet1!$R$3:$T$3,0)),0)

 

[ADB_BL]

Boom. Bingo.

Thank you so much!!

BL

 

[T. Valko]

Boom. Bingo.

Thank you so much!!

BL

You're welcome. Thanks for the feedback!

 

[SGTSparty]

Try this...

=IFERROR(INDEX(Sheet1!$R$4:$T$13,MATCH(B12,Sheet1!$Q$4:$Q$13,0),MATCH(D12,Sheet1!$R$3:$T$3,0)),0)

Thank you! I've been trying to solve this same issue for days and every solution I tried kept telling me I had too many arguments. I really appreciate this help!

 

[SBLee]

what changes need to be made to the formula with excel 2013

 

[Aladin Akyurek]

what changes need to be made to the formula with excel 2013

On a 2013 system the formula will run unchanged and as expected.

 

[SBLee]

=INDEX(bwt!$K:$K,MATCH(1,(bwt!$B:$B=$A18)*(bwt!$E:$E=N$4),0))
This is the formula I've been using. I now have reports that may not have the N4 (which is text), the return is NA. The value in K:K is a decimal.


I need to replace the NA with 0 or blank for the 10 columns that have this formula to total. I've changed the Index match, tried so many variations, and know I'm just missing something and can't see it.