vlookup error

R

RR

I am getting a #NA error on a vlookup function. This is an excerpt of the
table. The actual table is 54,000 rows long, has 3 columns, is sorted by the
left column. THe cells in the left column contain trim functions of the
middle column and contains data that starts with special characters (like #
and * and ~), numeric and alpha characters. An example of the table would be
something like this:

G H
I
* EXPEDITE FEE * EXPEDITE FEE EXPEDITE FOR QUICK DELIVERY
~PUR COST VAR ~PUR COST VAR Purchase Price Variance
~SHOP MATERIAL ~SHOP MATERIAL MISCELLANEOUS MATERIALS
ADA10"MAKB6 ADA10"MAKB6 Convert 10" MAK B6 to NACE

The formula that returns #N/A is:

=VLOOKUP(A16,G1:I46949,3,FALSE)

where A16 is ~SHOP MATERIAL. Why does the give me an error.

Thanks for your help. I need the answer to give me the exact result or an
error. I have never had this problem before with the False Range_lookup
indicator.
 
M

Max

JE explained in a past posting that the tilde acts as an escape character in
VLOOKUP.

Try using two tildes together, something like this:
=VLOOKUP("~"&A16,G1:I46949,3,FALSE)

Or, perhaps to cater for possibly non-tilde lookup values in A16 as well:
=IF(LEFT(A16)="~",VLOOKUP("~"&A16,G1:I46949,3,FALSE),VLOOKUP(A16,G1:I46949,3,FALSE))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding a VLookup to an AutoComplete ComboBox 0
VLookup Won't Copy down (#REF! error) 1
#VALUE error-- some cells? 3
VLOOKUP PROBLEM 3
vlookup help plz 2
vlookup column based on user input 5
Vlookup 2
Vlookup 5

Top