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?

 

[Scott R]

Custom formatting would do it...
#,##0;-#,##0;
or
#,##0;-#,##0;"no data"

 

[Aladin Akyurek]

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?

What do you have in F7:AS7 - text or numeric data?

 

[T. Valko]

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?

When you get a result of 0 is it because the cell in F7:AS7 is empty?

What type of data is in F7:AS7? Is it text? Numbers? Could be both?

 

[aedctalk]

Hello! Thanks for the quick responses

F7:AS is formattted general and has peoples names

row 42 is currency values

when a name is entered the 0 dissapears and show sthe persons name.

However when there are no names at all i show 0's and want to show "No Data" (refering to above formula i typed in first post)

hope that makes sense.

 

[Aladin Akyurek]

Hello! Thanks for the quick responses

F7:AS is formattted general and has peoples names

row 42 is currency values

when a name is entered the 0 dissapears and show sthe persons name.

However when there are no names at all i show 0's and want to show "No Data" (refering to above formula i typed in first post)

hope that makes sense.

Try...

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

 

[T. Valko]

Hello! Thanks for the quick responses

F7:AS is formattted general and has peoples names

row 42 is currency values

when a name is entered the 0 dissapears and show sthe persons name.

However when there are no names at all i show 0's and want to show "No Data" (refering to above formula i typed in first post)

hope that makes sense.

Try this...

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

 

[T. Valko]

Try...

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

That won't return "No Data" if the indexed cell is empty.

However when there are no names at all i show 0's and want to show "No Data"

 

[aedctalk]

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

 

[T. Valko]

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!

You're welcome. Thanks for the feedback!

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

It's a logical function that tests for the #N/A error. It means literally: IS NA.

If an error is generated it'll be generated by the MATCH function and it'll be a #N/A error.