I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.
I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.
this is the formula that i am using:
=IF($C$4="", "", IF($C$4="No Match", "", (VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))
The site name appears in C4, and is selected from another sheet in the workbook.
Last edited by Back2Basics; 01-22-2009 at 01:02 PM.
Could change the last section of the formula to something along the lines of
Please Login or Register to view this content.
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
First off let me point out that the above is really no different to your earlier formula just shortened slightly and lookup range reduced to A:E given you're returning value from E you need not reference anything beyond that.Please Login or Register to view this content.
To answer your question... if it's purely presentational (as it would seem) you're best bet would be to use a Custom Format on the cell as opposed to double evaluating the entire VLOOKUP, eg:
So 0 will persist in the cell but the cell will appear as blank... this is preferable to doing the VLOOKUP twice over.Please Login or Register to view this content.
Last edited by DonkeyOte; 01-22-2009 at 12:59 PM. Reason: code tags
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I had tried that, it seems the logical step - but when i hit enter it just displays the formula as text.
*tried it again before sending post*
It originally did the same, then i noticed that the "IF" was lower case - Normally okay, but when i changed it to upper case it worked!
Thanks for your time Kitty
And thank you Donkey, if that is your real name.
I didn't realise quit how useful the formating can be, cheers!
Quote: First off let me point out that the above is really no different to your earlier formula just shortened slightly and lookup range reduced to A:E given you're returning value from E you need not reference anything beyond that.
I have an extended range because the cell below this one references from 7, below that from 9 etc until 19....
thanks for the concern though
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks