vlookup error

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

 

[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