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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VLOOKUP Problem | Ian | General Discussion | 3 | April 6th, 2006 06:47 PM |
vlookup on large text in cells | SteveC | Worksheet Functions | 0 | February 23rd, 2006 06:55 PM |
vlookup data hidden within worksheet | Worksheet Functions | 0 | January 26th, 2005 12:09 PM | |
Insert Vlookup into table_array of Vlookup with named range | Denise | Worksheet Functions | 1 | January 24th, 2005 10:49 PM |
VLOOKUP and IF statements | JAnderson | General Discussion | 2 | August 4th, 2004 11:39 PM |