Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup
How can I make this work?
I have a worksheet. I need to extract the first two digits of a number. The two digit number determines which equipment to use.That number checks against a vlookup table and then the equipment is entered in another cell. The worksheet is much larger than this but this should give you an idea what the problem is. Example: A1 = 8110332 B1 =LEFT(A1,2) B1 now shows 81 C1 =VLOOKUP(B1,D1:E1,2,FALSE) D1 =81 E1 =Thingy The #N/A error shows. I just need to enter the number in A1 and all the rest is done for me. I know the problem rests with the formula in B1. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup
Try
=VLOOKUP(--B1,D1:E1,2,FALSE) or in B1 use =--LEFT(A1,2) then use your vlookup as originally stated all the text functions return a text string -- Regards, Peo Sjoblom http://nwexcelsolutions.com wrote in message . net... How can I make this work? I have a worksheet. I need to extract the first two digits of a number. The two digit number determines which equipment to use.That number checks against a vlookup table and then the equipment is entered in another cell. The worksheet is much larger than this but this should give you an idea what the problem is. Example: A1 = 8110332 B1 =LEFT(A1,2) B1 now shows 81 C1 =VLOOKUP(B1,D1:E1,2,FALSE) D1 =81 E1 =Thingy The #N/A error shows. I just need to enter the number in A1 and all the rest is done for me. I know the problem rests with the formula in B1. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup
B1 =LEFT(A1,2) B1 now shows 81
C1 =VLOOKUP(B1,D1:E1,2,FALSE) Try instead in B1: =LEFT(A1,2)+0 The "+0" will coerce the text number returned by LEFTinto a real number -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: How can I make this work? I have a worksheet. I need to extract the first two digits of a number. The two digit number determines which equipment to use.That number checks against a vlookup table and then the equipment is entered in another cell. The worksheet is much larger than this but this should give you an idea what the problem is. Example: A1 = 8110332 B1 =LEFT(A1,2) B1 now shows 81 C1 =VLOOKUP(B1,D1:E1,2,FALSE) D1 =81 E1 =Thingy The #N/A error shows. I just need to enter the number in A1 and all the rest is done for me. I know the problem rests with the formula in B1. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup
Thank You So Much. Exactly What I Wanted.
"Max" wrote in message ... B1 =LEFT(A1,2) B1 now shows 81 C1 =VLOOKUP(B1,D1:E1,2,FALSE) Try instead in B1: =LEFT(A1,2)+0 The "+0" will coerce the text number returned by LEFTinto a real number -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: How can I make this work? I have a worksheet. I need to extract the first two digits of a number. The two digit number determines which equipment to use.That number checks against a vlookup table and then the equipment is entered in another cell. The worksheet is much larger than this but this should give you an idea what the problem is. Example: A1 = 8110332 B1 =LEFT(A1,2) B1 now shows 81 C1 =VLOOKUP(B1,D1:E1,2,FALSE) D1 =81 E1 =Thingy The #N/A error shows. I just need to enter the number in A1 and all the rest is done for me. I know the problem rests with the formula in B1. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
vlookup
You're welcome ! Glad it helped.
See also Peo's reply for other ways to do it -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message et... Thank You So Much. Exactly What I Wanted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |