VLOOKUP issues

[BigPeter]

I have created a spreadsheet for pricing items. I have created a table with items, wholesale, and retail prices in three columns.
In the active transaction area I am using this formula for pulling up pricing:
=VLOOKUP(D30,A$3:C$27,2)

The problem is, some items come up correctly, but some show prices from other items or just an N/A. Any ideas how to fix this?

[TMS]

If you are looking for an EXACT match, you need to use the last parameter:

Please Login or Register  to view this content.

Regards, TMS

[etaf]

you need a false to get an exact match
=VLOOKUP(D30,A$3:C$27,2,false)
otherwise it assumes true
which will return the nearest value

does that work

and N/A will mean that the table in column A does not match with D30

[simarui]

hi BigPeter and welcome to the forum. try adding ",false" to the end of the formula:

Please Login or Register  to view this content.

as for the #N/A values, the thing you are searching for is not found in A$3:A$27... if you believe this statement to be incorrect then most likely it's an issue of text vs. numbers... check each cell carefully to ensure they are both either text or numbers (input a ' before a number to make it text)

[BigPeter]

Thank you all. Adding FALSE fixed the problem.

[TMS]

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.