Index Function that Returns 0's AND Blanks

[thestranger66]

Hi Everyone,

I am in need of an INDEX function that is capable of returning both blanks and zeros from a column of data. Normally an when and INDEX function comes across a blank it returns a "0". A lot of people on the forums seem to devise solutions to similar problems using IF statements where all 0's are replaced with ""'s, but that doesn't work in my case since the data I want to be returning may contain both zeros and blanks. Below is a sample input and output that should clarify what I am trying to accomplish.

1 -> 1
2 -> 2
3 -> 3
0 -> 0
4 -> 4
""-> ""
5 -> 5
6 -> 6

Thanks in advance!

 

[Aladin Akyurek]

Is the 6th row (say A6) just empty, unused cell, or a blank, created by means of a formula like =""?

 

[thestranger66]

Hi Aladin. The 6th row in this example is supposed to represent an empty cell.


Thanks.

 

[Aladin Akyurek]

=INDEX($A$1:$A$8,ROWS($D$1:D1))

entered in D1 and copied down, will read A6 as 0 as designed. If A6 is to contain a formula blanks (i.e. ""), INDEX will read it as such. COUNTA(D6) will yield 1.

If you want to make INDEX read an empty, unused cell as if it were a formula blank, in E1 enter and copy down:

=INDEX($A$1:$A$8,ROWS($E$1:E1))&""

which would convert any true number into text and an empty cell into text (a formula blank).

 

[thestranger66]

Aladin,

You second formula is exactly what I needed.

Thank you!

 

[Aladin Akyurek]

Aladin,

You second formula is exactly what I needed.

Thank you!

You are welcome.

 

[Jonmo1]

Glad you got solution, but as Aladin said the result of that formula is a TEXT string, not a real number.

When you really need to differenciate between Blank and a True Zero value, you may have to do this.

=IF(INDEX(MATCH(..))="","",INDEX(MATCH(..)))

Or maybe
=IFERROR((INDEX(MATCH(..)&"")+0,"")