VLOOKUP showing #NA

Thanks:  0
Likes:  0

# Thread: VLOOKUP showing #NA

1. ## VLOOKUP showing #NA

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

2. ## Re: VLOOKUP showing #NA

Try:

=IF(ISNA(VLOOKUP),"",VLOOKUP))

You can also use 0 instead of "".

Hope that helps,

Smitty

3. ## Re: VLOOKUP showing #NA

Originally Posted by Glisson
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
A couple of options...

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.

4. ## Re: VLOOKUP showing #NA

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

5. ## Re: VLOOKUP showing #NA

Originally Posted by Glisson
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
1]

=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.

6. ## Re: VLOOKUP showing #NA

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.
You'll find that this is Aladin's PRIMARY language. He is THE Master!

Smitty

7. ## Re: VLOOKUP showing #NA

It looks like the net ate one of Aladin's commas, in solution #1 --

=IF(ISNUMBER(MATCH(B3,INDEX(Emptable,0,1),0)),VLOOKUP(B3,Emptable,3,0),"")

8. ## Re: VLOOKUP showing #NA

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

9. ## Re: VLOOKUP showing #NA

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•