[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?

[CM]

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]

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]

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]

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]

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)