Hi, when I use a vlookup formula instead of displaying N/A if it cant find anything I would like it to just be a blank cell. Please can someone help.
Hi, when I use a vlookup formula instead of displaying N/A if it cant find anything I would like it to just be a blank cell. Please can someone help.
Last edited by gandyling; 11-08-2011 at 03:50 PM.
Like this:
=IF(ISNA(your_vlookup_formula),"",your_vlookup_formula)
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.
Hi Gandyling,
It would be easier to see your formula/worksheet, but i will have a go anyway.
Your Vlookup - (for example)
=vlookup(a1,data,1,false)
To get the cell to be blank instead of N/A try this -
=if(a1="","",vlookup(a1,data,1,false)
The part "" in excel terms is the code for blank, so:
=if(a1 is blank, show me blank, if not, show me my vlookup)
Hope that helps!!
galvinpaddy
+=====================+
EDIT - Domski nailed it better
The cleanest way:
use a column ( say col V) you do not need and enter the VLOOKUP and let the NA happen
Then in your answer column enter=if(isna(V1),"",V1)
Hide col V if needed
Thanks for your help it worked great
I always use iferror()
other solutions above works too.
Not in Excel 2003 you won't
Dom
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks