A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VLOOKUP function with embedded LEFT



 
 
Thread Tools Display Modes
  #1  
Old October 7th 09, 02:36 PM posted to microsoft.public.excel.worksheet.functions
SamB
external usenet poster
 
Posts: 10
Default 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?
Ads
  #2  
Old October 7th 09, 02:46 PM posted to microsoft.public.excel.worksheet.functions
CM
external usenet poster
 
Posts: 136
Default 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  
Old October 7th 09, 03:08 PM posted to microsoft.public.excel.worksheet.functions
SamB
external usenet poster
 
Posts: 10
Default 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  
Old October 7th 09, 03:39 PM posted to microsoft.public.excel.worksheet.functions
CM
external usenet poster
 
Posts: 136
Default 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  
Old October 7th 09, 04:17 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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  
Old October 7th 09, 05:14 PM posted to microsoft.public.excel.worksheet.functions
SamB
external usenet poster
 
Posts: 10
Default 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  
Old October 7th 09, 06:04 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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  
Old October 7th 09, 06:16 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default 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)
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup combined with LEFT function Shams Excel Worksheet Functions 1 March 3rd 09 02:08 PM
Embedded If Function in a Vlookup Function Excel Student Excel Worksheet Functions 2 January 13th 07 04:39 AM
Embedded VLOOKUP function within IF function beautyteknorth Excel Worksheet Functions 6 August 17th 06 09:31 AM
Vlookup and left function Corey Osborn Excel Discussion (Misc queries) 3 March 23rd 06 06:36 PM
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION CHAIM Excel Worksheet Functions 1 July 27th 05 09:10 PM


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.