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))

 

[Aladin Akyurek]

=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.

Control+shift+enter, not just enter:

Either:

=IFERROR(INDEX(bwt!$K:$K,MATCH(1,(bwt!$B:$B=$A18)*(bwt!$E:$E=N$4),0)),0)

Or:

=IFERROR(INDEX(bwt!$K:$K,MATCH(A18,IF(bwt!$E:$E=N$4,bwt!$B:$B),0)),0)

If INDEX runs into a #N/A, delivered by the MATCH bit, we get 0.

By the way, it is better to avoid referencing whole columns for reasons of efficiency.

 

[SBLee]

thank you, that worked, appreciate your time

 

[Aladin Akyurek]

thank you, that worked, appreciate your time

You are welcome.