I am using V lookup as i have bunch ppl and times and i am trying to work out who hasn't got any times. So ppl who havent put anytimes in comes up with #N/A is there way to change it into "-" ?
You could try using IF() and ISERROR() on the line.
Formula would look nasty though.
So if the lookup fails, put hyphen - otherwise, do the lookup again ;-) Efficiency++
From the help-
So I would suggest ISBLANK in my limited knowledge.
Wouldn't efficiency be storing the vlookup in a different cell and then use the iserror function on the value of that cell so you don't have to do two vlookups each time, or using your own vba function? Just sayin' :P
My "Efficiency++" was sarcasm. I know, bad form to use sarcasm on the intarwebs.
If it's only a small lookup table I wouldn't bother trying to make it more efficient.
ISERROR > ISNA
for future reference, when you get skull slippery slick at excel
SUMPRODUCT is where it's at, in all of it's wonderful variations
Holy s*** it worked!!
Why do you even need the ISERROR?
Wouldn't this work: =IF((VLOOKUP(1,A2:D10,2)>0),VLOOKUP(1,A2:D10,2),"-")
I typically set no values to "NA" as text, just so other people looking at sheets can see that no calculation could be performed, rather than people thinking there's an error.