Using Parsed String Results in VLOOKUP

  • Thread starter Thread starter pmind
  • Start date Start date
P

pmind

Hello All, I am trying to use the results of a parsed string in
VLOOKUP, but keep getting #N/A. For example, I have cell A2 containing
the value 12345678, I then extract the first two numbers ("12") to
cell A5 using =LEFT(A2,2). No problem so far, but it's when I try to
use =VLOOKUP(A5,C2:D8,2,FALSE), that I get the #N/A. If I manually
type the value "12" into cell A6 and change the reference in the
VLOOKUP to A6 instead of A5, the lookup works fine, so the table
(C2:D8) is okay. All I am left to conclude is that I can not use the
results of a parsed string in VLOOKUP, or at least not in the manner
that I'm parsing it. Is it possible? Does anyone know a better way? My
VBA is not too strong, but I'm more than willing to give that a try as
well. Thanks.
 
=left(a2,2)
will return the text '12 (not the number 12).

So you could use:
=--left(a2,2)
the first minus coerces the string to a number, but the opposite sign. The
second minus changes the sign back to the original.

Or you could use:
=vlookup(--a5, ....
 
=left(a2,2)
will return the text '12 (not the number 12).

So you could use:
=--left(a2,2)
the first minus coerces the string to a number, but the opposite sign.  The
second minus changes the sign back to the original.

Or you could use:
=vlookup(--a5, ....
Click to expand...

Thanks Dave, both solutions work great!
 
Back
Top