ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   VLOOKUP function with embedded LEFT (https://www.excelbanter.com/excel-worksheet-functions/244849-vlookup-function-embedded-left.html)

SamB

VLOOKUP function with embedded LEFT
 
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?

CM

VLOOKUP function with embedded LEFT
 
can you post the formula and phone number example please
--
hope to help,
cm


"SamB" wrote:

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?


SamB

VLOOKUP function with embedded LEFT
 
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

"cm" wrote:

can you post the formula and phone number example please
--
hope to help,
cm


"SamB" wrote:

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?


CM

VLOOKUP function with embedded LEFT
 
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)

--
hope to help,
cm


"SamB" wrote:

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

"cm" wrote:

can you post the formula and phone number example please
--
hope to help,
cm


"SamB" wrote:

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?


Glenn

VLOOKUP function with embedded LEFT
 
cm wrote:
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)


or this:

=VLOOKUP(--LEFT(F2,3),Sheet2!A1:B335,2,FALSE)

SamB

VLOOKUP function with embedded LEFT
 
Glenn, this worked as well. I have never seen -- used before. What command
is that giving?

"Glenn" wrote:

cm wrote:
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)


or this:

=VLOOKUP(--LEFT(F2,3),Sheet2!A1:B335,2,FALSE)


Glenn

VLOOKUP function with embedded LEFT
 
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.

SamB wrote:
Glenn, this worked as well. I have never seen -- used before. What command
is that giving?

"Glenn" wrote:

cm wrote:
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)

or this:

=VLOOKUP(--LEFT(F2,3),Sheet2!A1:B335,2,FALSE)


David Biddulph[_2_]

VLOOKUP function with embedded LEFT
 
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.
--
David Biddulph


"SamB" wrote in message
...
Glenn, this worked as well. I have never seen -- used before. What
command
is that giving?

"Glenn" wrote:

cm wrote:
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)


or this:

=VLOOKUP(--LEFT(F2,3),Sheet2!A1:B335,2,FALSE)





All times are GMT +1. The time now is 10:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com