# Index/ Match Formula, How to return blank cell as a blank not 0

1. ## Index/ Match Formula, How to return blank cell as a blank not 0

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?

2. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

=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

3. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

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

4. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

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)

5. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

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),"")

6. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

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

8. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

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

9. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

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

to avoid double-calculations...

10. ## Re: Index/ Match Formula, How to return blank cell as a blank not 0

Domski's formula suggestion worked flawlessly.
Thanks so much

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1