Remember Me?

#1
October 7th 09, 02:36 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 10
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?

#2
October 7th 09, 02:46 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 136
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?

#3
October 7th 09, 03:08 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 10
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?

#4
October 7th 09, 03:39 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 136
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?

#5
October 7th 09, 04:17 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240
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)

#6
October 7th 09, 05:14 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 10
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)

#7
October 7th 09, 06:04 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240
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)

#8
October 7th 09, 06:16 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651
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)

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Shams Excel Worksheet Functions 1 March 3rd 09 02:08 PM Excel Student Excel Worksheet Functions 2 January 13th 07 04:39 AM beautyteknorth Excel Worksheet Functions 6 August 17th 06 09:31 AM Corey Osborn Excel Discussion (Misc queries) 3 March 23rd 06 06:36 PM CHAIM Excel Worksheet Functions 1 July 27th 05 09:10 PM

All times are GMT +1. The time now is 07:56 PM.

The comments are property of their posters.