using extracted #'s to do vlookup

[matmich]

I use a right or left command to extract a series of #'s from a PN.
Once I have the #'s i use a vlookup command to search for values. Bu
the vlookup wont recognize these #'s and returns #n/a. WHY??? If
enter the number (data entry) everything is ok. I have 2000+ #'s t
read, can someone explain and give me a solution

 

[kkknie]

It is seeing those numbers as text (since you used right and/or left
which returns a string rather than a number. The VLookup sees th
number 2 as being different than the "letter" 2. In you left/righ
statements, change to add the Value() function:

=Value(Right(A1,5))

This should convert to numbers and should work if everything else i
OK.

 

[Mike Lewis]

The left and right functions return strings, use
the "Value" function to convert to a number.
=value(left(A1,3))

Good Luck

 

[Mike Lewis]

The left and right functions return strings, use
the "Value" function to convert to a number.
=value(left(A1,3))

Good Luck

 

[Domenic]

Hi,

VLOOKUP doesn't recognize those values because the LEFT and RIGHT
functions return a value in the text format. You need to convert them
to numeric values by preceding the functions with two dashes. For
example:

=--LEFT(A1,4)

So, in your case, you can easily make these changes using FIND and
REPLACE, something like:

Edit > Replace
Find What: LEFT
Replace With: --LEFT

Hope this helps!

 

[Aladin Akyurek]

Also...

=VLOOKUP(--LEFT(...),...)

=VLOOKUP(LEFT(...)+0,...)