Try:
=IF(ISNA(VLOOKUP),"",VLOOKUP))
You can also use 0 instead of "".
Hope that helps,
Smitty
This is a discussion on VLOOKUP showing #NA within the Excel Questions forums, part of the Question Forums category; Is there a way to keep a cell from showing #NA in the cell where I am performing a VLOOKUP? ...
Is there a way to keep a cell from showing #NA in the cell where I am performing a VLOOKUP? It seems that there should be a way to make it look cleaner.
Glisson
Excel Neophyte
To Live In Fear Is Not To Live
Try:
=IF(ISNA(VLOOKUP),"",VLOOKUP))
You can also use 0 instead of "".
Hope that helps,
Smitty
A couple of options...Originally Posted by Glisson
1]
=IF(ISNUMBER(MATCH(A2,$E$2:$E$20,0)),VLOOKUP(A2,$E$2:$G$20,3,0),"")
2]
=IF(ISNA(SETV(VLOOKUP(A2,$E$2:$G$20,3,0))),"",GETV())
3]
B2:
=IF(ISNA(C2),"",C2)
C2:
=VLOOKUP(A2,$E$2:$G$20,3,0)
The 2nd and 3rd formulas work faster than the 1st. The 2nd requires the morefunc.xll add-in.
Assuming too much and qualifying too much are two faces of the same problem.
So that would turn
=VLOOKUP(B3,Emptable,3,FALSE)
into what? I'm sorry I tried a couple of variations and couldn't get it to work.
P.S. When you look at formulas like this do you see it like grafitti as I do or can you actually "read" it like a second ( or third ) language? just curious.
Glisson
To Live In Fear Is Not To Live
1]Originally Posted by Glisson
=IF(ISNUMBER(MATCH(B3,INDEX(Emptable,0,1)0)),VLOOKUP(B3,Emptable,3,0),"")
2]
=IF(ISNA(SETV(VLOOKUP(B3,Emptable,3,0))),"",GETV())
3]
C3:
=IF(ISNA(D3),"",D3)
D3:
=VLOOKUP(B3,Emptable,3,0)
Note that 0 == FALSE.
You'll find that this is Aladin's PRIMARY language. He is THE Master!P.S. When you look at formulas like this do you see it like grafitti as I do or can you actually "read" it like a second ( or third ) language? just curious.
Smitty
Thanks. That got the result I wanted! Now I can go back and pick apart the formula to try to understand it better so that I can use it in other formulas.
I'm just starting to learn his Primary language. You guys are so cool to help guys like me. I never really realized HOW much you could do with Excel. A lot of people here are really pushing the "accepted" limits with it. I played Monopoly written in Excel last night! How cool is that!?
P.S. Is there an ENTRY level book recommended for learning VBA and/or some of these more advanced formulas? I like the books that actually have you work through "projects" to teach you so you can see how to apply the ideas in real world aplications.
Thanks again,
Glisson
To Live In Fear Is Not To Live
John Walkenbach has an Excel VBA for Dummies book. (Although, I've never read it, so I can't tell you how it is).
He also has several others:
http://www.j-walk.com/ss/books/index.htm
Excel Power Programming with VBA is excellent.
Bill Jelen, AKA Mr. Excel, also has "Mr. Excel on Excel", which is supposed to be very good as well.
Those books are a start, but don't forget to post anytime you have a question.
As you've seen, what is Greek to many isn't to the MVP's!
Hope that helps,
Smitty
Like this thread? Share it with others