Index Formula Help

[klatlap]

i am using a formula similar to this to lookup but if the cell is blank it returns a zero how can i get it to return blank
=IF(INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+1,1)="","",INDEX($BS$15:$BS$350,MATCH($AG$4,$BS$15:$BS$350,0)+1,1))

 

[Scott Huish]

If there are no actual zeros in your data, how about dropping the IF and formatting the cell as:


[=0]""

 

[klatlap]

Ok working now, for some reason i removed the first set of offsets no idea why idid that but this works fine

=IF(INDEX($BS$15:$BS$350,MATCH($AG$4,$BR$15:$BR$350,0)+1,1)="","",INDEX($BS$15:$BS$350,MATCH($AG$4,$BR$15:$BR$350,0)+1,1))

 

[T. Valko]

No i just played with what you posted and it works the same if i add the offsets, must be formatting.

Is it possible that the cell contains numeric 0 but you have display of 0 turned off?

In Excel versions 2003 and ealier that setting is located at:

Tools menu>Options>View tab>Zero Values

In Excel 2007 that setting is located at:

Office button>Excel Options>Advanced>Display options for this worksheet>Show a zero in cells that have zero value

Excel 2007 makes you want to