# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Index/ Match Formula, How to return blank cell as a blank not 0

## MDResearcher

I  am using this below formula to look up a patient medical record number, and then return the value of a cell for a certain parameter (Blood pressure, heart rate, ect).

=INDEX('Required Patient Info'!C4:C1101,MATCH(C3,'Required Patient Info'!B4:B1101,0))

This code returns blank cells as a 0 instead of returning as blank.  Is there a way to fix this, so that it returns just a blank value?

----------


## Domski

=INDEX('Required Patient Info'!C4:C1101,MATCH(C3,'Required Patient Info'!B4:B1101,0))&""

Would work but if it's numerical values you are returning they will now come back as text strings for non-blanks.

Dom

----------


## MDResearcher

They are unfortunately numerical values, any further suggestions? Thank you for your help!

----------


## MDResearcher

Actually any way to make the result of a blank cell vs. a cell with numbers using a variation of the formula I posted above would be great. All I want to know is if the cell I look up is filled or blank. To put this in context I am using the result of the cell to either black out a cell if the cell looked up is blank, or leave it with no fill if the cell I looked up is filled. Some of the cells eligible to be looked up have zeros in them, so have a blank cell return a 0 also is not an option.

I can run a macro on the formula Domski posted to copy & paste the value of it into another cell and then run a ISTEXT, ISNUMBER, or ISBLANK.  Unfortunately though there is no way of differentiating the pasted value of a blank cell result (ISTEXT comes back TRUE, ISNUMBER & I SBLANK come back FALSE) and the pasted value of a cell with actual numbers (ISTEXT also comes back true, ISNUMBER & ISBLANK also come back FALSE)

----------


## butterfly123

I am having the same problem.  I am using this formula and it is bringing up text,so not sure why it brings up a 0 instead of a blank.

=IF($T$8>0,VLOOKUP($S$8,Tubemaster!$A$11:$P$1986,12,FALSE),"")

----------


## Domski

It's a tad inefficient but you can use the construct like this and get it to do what you want:

=IIF(INDEX('Required Patient Info'!C4:C1101,MATCH(C3,'Required Patient Info'!B4:B1101,0))="Whatever","",=INDEX('Required Patient Info'!C4:C1101,MATCH(C3,'Required Patient Info'!B4:B1101,0))

Dom

----------


## NBVC

Domski, you might want to double-check your formula.. you have an IIF at the beginning...and a leading = sign in your what_if_false argument....

----------


## Domski

Cheers NBVC, been out for a boozy meal and really shouldn't be trying to answer questions now.

Feel free to correct...

=IF(INDEX('Required Patient Info'!C4:C1101,MATCH(C3,'Required Patient Info'!B4:B1101,0))="","",INDEX('Required Patient Info'!C4:C1101,MATCH(C3,'Required Patient Info'!B4:B1101,0)))

Dom

----------


## NBVC

You could also keep the original and format the result cells as custom: 0;-0;;@

to avoid double-calculations...

----------


## dudeshane01

Domski's formula suggestion worked flawlessly.
Thanks so much

----------

