When using Index(Match, it returns zero if the referenced cell is blank. How can I prevent this?

you can use conditional formatting to make the text color the same as the background when the result is 0 (zero)

you can create a custom format that

Hi Joey,

As an alternate, you can use an IF statement to test for data in the source cell, and if none is found, to insert a null string in the receiving cell.

If there is an actual value of zero in the referenced cell, the formula will return that value. If the referenced cell is empty, or contains a null string, the formula will return a null string.

The base formula in column D uses OFFSET and MATCH.

D2: =OFFSET($A$1,MATCH(ROW()-1,$A,0)-1,1)

The formula in column E uses the same base formula twice. The first instance (bold) tests the LENgth of the result and compares that value with zero.If the LENgth is greater than zero, the formula is called again (italic), and the result returned to the cell containing the formula. If the LENgth of the result is zero, IF returns a null string ( "" ) to the formula's cell.

E2: =IF(LEN(OFFSET($A$1,MATCH(ROW()-1,$A,0)-1,1))>0,OFFSET($A$1,MATCH(ROW()-1,$A,0)-1,1),"")

Regards,

Barry

Hello

If retrieved data are always text, you may simply concatenate it to empty string like this:

=""&INDEX(...)

Regards,

H

Hi Hiroto,

I like that.

quinn