Lookup returning #NA when pulling from data validation drop down list?

would this work for you? =IFERROR(VLOOKUP(A17,ProductsPrice,2,0),0)

EDIT: your issues isn't that is it? is it that you can't figure out why it isn't returning your value?

The Lookup table needs to be defined as

Formula:

Please Login or Register  to view this content.

not

Formula:

Please Login or Register  to view this content.

Regards, TMS

Change ProductsPrice Named range as
=Sheet3!$A$1:$B$217 instead of =Sheet3!$B$1:$B$217

Your named range for ProductsPrice is incorrect (if you want to stick to the current VLOOKUP formula you have). Change the range to cover column A as well. It would look like this:

Please Login or Register  to view this content.

Hope this helps!

would this work for you?

It would hide the error but it wouldn't fix the problem

yes, it took me rereading it three times to figure it out. then i was sure when i read your post.

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Hi,

Change your ProductsPrice range to cover column A & B. At the mpment it's just B

Better still give it a dynamnic range name so that you can forget about changing the formulae when adding new rows.
i.e.
ProductsPrice

Formula:

Please Login or Register  to view this content.

ditto for the Products name bu change the ,2 to ,1