Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup combined with LEFT function | Excel Worksheet Functions | |||
Embedded If Function in a Vlookup Function | Excel Worksheet Functions | |||
Embedded VLOOKUP function within IF function | Excel Worksheet Functions | |||
Vlookup and left function | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions |