How to make my index / match not show 0 and show "No Data" Instead

[aedctalk]

Hello.. when i have values the below code works great. If i have no value it shows a zero though

=IF(ISERROR(INDEX($F$7:$AS$7,MATCH(AX148,$F$42:$AS$42,0))),AW143,INDEX($F$7:$AS$7,MATCH(AX147,$F$42: $AS$42,0)))

I've tried setting AW143 to ="No Data" and that worked for half my cells.. however the othe half still show 0..

Are there any other alternative methods of showing "No Data" if the value is = 0 ? instead of 0?

 

[Aladin Akyurek]

Thank You!

=IF(ISNA(MATCH(AX147,$F$42:$AS$42,0)),"No  Data",IF(INDEX($F$7:$AS$7,MATCH(AX147,$F$42:$AS$42,0))="","No  Data",INDEX($F$7:$AS$7,MATCH(AX147,$F$42:$AS$42,0))))

Worked Perfectly!

Mind if i ask what ISNA does or stands for? Never seen that one before :P

Interesting to observe that you want to see one type of result:

1) No Data, when AX147 is not in $F$42:$AS$42, trapping an #N/A result; And,

2) No Data, when AX147 is available but the corresponding cell is empty, avoiding a 0 read as result.

What I suggested is fast and gives you a differentiated result.

When case 1 is in effect, we get No Data from AW143, housing that very string.

When case 2 is in effect, we get a blank, that is, "" which is due to the T() function.

If you realy don't care about differentiating, return blank for both cases with:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",T(INDEX($F$7:$AS$7,MATCH(AX147,$F$42: $AS$42,0)))))