Vlookup problem...

[boousaf]

Hi Guys,
I've been scratching my head all day with this vlookup problem
(getting #n/a errors). Please excuse the noobness - first post.

Can you see anything wrong with this on the surface?

=VLOOKUP(G9,Dept!A9:B157,2,FALSE)

I am trying to tell excel to look at the dept value (G9), and populate
the locations based on the location listed on the department tab (dept),
and i keep getting na errors... any ideas or direction would be greatly
appreciated!

 

[Mark]

I will get an #NA error whenever my Vlookup function cannot find the
value in the table. In your example, it would appear that whatever
value resides in cell G9 cannot be found within Dept!A9:B157. I hope
that gets you going in the right direction.

 

[Alan]

Two things, If you're dragging this down you need to make the range
absolute,

=VLOOKUP(G9,Dept!$A$9:$B$157,2,FALSE)

not,

=VLOOKUP(G9,Dept!A9:B157,2,FALSE)

Is it all on one sheet? If you're using two sheets try

=VLOOKUP(Dept!G9,Dept!A9:B157,2,FALSE)

Regards,
Alan.


I am trying to tell excel to look at the dept value (G9), and populate
the locations based on the location listed on the department tab (dept),
and i keep getting na errors... any ideas or direction would be greatly
appreciated!

 

[Jules]

=VLOOKUP(trim(G9,Dept!$A$9:$B$157,2,FALSE)

sometimes the formating isn't exact and this helps me a great deal.

Good Luck.

 

[T. Valko]

Try this:

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

That will return a blank cell. If you'd rather have a 0 returned just
replace "" with 0.

Biff

 

[T. Valko]

Post the *EXACT* formula that your tried.

Biff

I did try that too but not no good. Are you sure it works and i'm just
putting it in incorrectly?

JB