VLOOKUP function with embedded LEFT

  • Thread starter Thread starter SamB
  • Start date Start date
S

SamB

I am using a VLOOKUP function to look up what State and area code is from.
The lookup value is being calculated by using a LEFT formula to pull the area
code from a cell where the full phone number is located. However, the LEFT
formula is adding " on each side of the area code when it calculates which
leads to an error in the lookup. Is there any way around this?
 
the phone number is 419-490-XXXX
the formula I am using is: =VLOOKUP(LEFT(F2,3),Sheet2!A1:B335,2,FALSE)

If I remove the LEFT statement and hardcode the 419 in as the lookup, it
works. If I use the formula, it returns a #N/A
 
I have duplicated your error; the values in your lookup table in sheet2,
a1.... are numbers; replace your formual as follows:

=VLOOKUP(VALUE(LEFT(F2,3)),Sheet2!A1:B335,2,FALSE)
 
cm said:
I have duplicated your error; the values in your lookup table in sheet2,
a1.... are numbers; replace your formual as follows:

=VLOOKUP(VALUE(LEFT(F2,3)),Sheet2!A1:B335,2,FALSE)
Click to expand...

or this:

=VLOOKUP(--LEFT(F2,3),Sheet2!A1:B335,2,FALSE)
 
Glenn, this worked as well. I have never seen -- used before. What command
is that giving?
 
In this case, it converts the text returned by the LEFT() function into a number
(instead of text that looks like a number). That way it matches the data type
of your table and allows the VLOOKUP() to work properly.
 
The double unary minus is used (among other purposes) to convert a text
string into a number.

The left function LEFT(F2,3) returns a text string, so it might be "123"
-LEFT(F2,3) would then return the number -123
--LEFT(F2,3) would return the number 123.

In other places you'll see the double unary minus used to convert Boolean
TRUE and FALSE results to 1 and 0 respectively in a similar manner.
-TRUE returns -1, --TRUE returns 1.
-FALSE calculates -0 (which is 0), --FALSE returns 0.
 
Back
Top