+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    06-21-2011
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    10

    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. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    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
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    06-21-2011
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    10

    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. #4
    Registered User
    Join Date
    06-21-2011
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    10

    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)
    Last edited by MDResearcher; 06-22-2011 at 01:00 PM.

  5. #5
    Registered User
    Join Date
    06-06-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    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
    Last edited by Domski; 06-22-2011 at 04:21 PM.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

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

    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....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  8. #8
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    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. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #10
    Registered User
    Join Date
    08-30-2012
    Location
    New York, USA
    MS-Off Ver
    2010
    Posts
    6

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

    Domski's formula suggestion worked flawlessly.
    Thanks so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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