Vlookup Question

[tomlinsn]

I am looking for someone to explain to me how to format my data to remove the need to lookup a value with a ' preceding the value. For example: I am wanting to lookup the value 100100 in my data range. Using the vlookup command vlookup(a3,list,3,false) where A3=100100. I get a #n/a error, but if I change the value in A3 to '100100 I get the correct result. How do I eliminate the need for the '?

Thank you in advance.
Tom

 

[Marcelo Branco]

Maybe

=vlookup(a3&"",list,3,false)

M.

 

[Sharpefiction]

Is the value in A3 a number (as in formatted as a number)? That could be throwing excel off as it wont be an exact match..

Both values - within A3 and the lookup table 'list' - will need to be the same format

 

[tomlinsn]

Yes, it is formatted as a number with 0 decimal places.

 

[texasalynn]

try this
=VLOOKUP(TEXT(A3,"000000"),list,3,FALSE)

 

[look]

Hi tomlinsn,

You could copy paste value will remove the '.

Thanks

 

[tomlinsn]

I have read through the responses, and I need more information:

I have a vlookup table established in a worksheet, where the key value is a numeric, 0 decimal formatted value. When I receive my monthly update file, the data that I extract has the key value column formatted General. When I copy the table values from the monthly update file and Paste Values into the Vlookup range, I receive an #N/A error on my worksheet with the vlookup formula. If I go into the key value column and manually edit each value (hit F2) and hit <Enter>, the #N/A error goes away and the proper value is displayed. My question is, Why is this happening? What can I do to prevent the need for this useless step?

Please let me know how to correct this issue.

 

[tomlinsn]

FIXED. I determined that the values stored in Excel on the original worksheet were text values even though when I checked the format for the cell it indicated that the cell was formatted as a numeric value. There was a green triangle in the upper left hand corner of the cell and a yellow diamond that I clicked. When I converted these to numeric values, the copy and paste into the Vlookup table worked as designed. Took to long to find the answer, but the answer was found.