LOOKUP function help
 

I've looked all through the posts and can't find a thread with a simliar issue. This is so stupid, and it's driving me crazy, but for the life of me, I can't figure it out and now it's giving me a headache. Here's the scenario: I have a cell which contains a product code number (the length can range from 3 digits to 6 digits). I have to search a data table where all of the numbers are formatted as 6 digits, hyphenated with a second set of digits. What I need to do is when the user enters the first set of digits in one cell, the function performs a LOOKUP to the data table, finds the entered 3-6 digit number within the column of 6 digit numbers (prior to the hyphenation), and retrieves the value in the column to the right (or left...I can move the columns) of the list of the 6 digit numbers. These columns can be formatted as numbers or text, it doesn't matter, but either way I format them, I can't get the dadgum LOOKUP (H and V included) function to work. Please help before I toss my laptop out the window of the 22nd floor.... sample view attached. THANK YOU.

From your post above, your workbook seems to be quite flexible. Would a helper column or two be out of the question? If not, then perhaps a hidden column that extracts just the first 6 digits from the product codes and use that for the vlookup?

For example, based on your sample image, if you inserted a new column F between "Product Code" and "Standard Rate / Hour" and in F2 enter the formula =LEFT(E2,6) . This will then let you put a vlookup in cell C2 ( =VLOOKUP(B2,F2:G28,2,FALSE) in the case of your sample image but with the new hidden column F.) but it will mean the user will have to type in the preceding zeros to ensure it's a six digit number.

There are other ways of doing this, but would need an actual example workbook, rather than the image file, before I could sort one out.

Thank you, Spencer...I did think of that (and it does work), but I was trying to make this little snippet as idiot proof as possible, as the report that the 3-6 digits is gleaned from does not print the leading zeros. In that way, the user will only have to enter the numbers printed (sans the zeros) to get the result (I know, you would think grown people who work here would know that a code used in our process was 6 digits long, but alas). I can send you the workbook, if you have the time, and send me another contact point (I tried uploading the project to the forum, and it wouldn't accept anything but a .jpg/picture). Thanks again for your help.

Hi,

PM'd you an email address.

S.

LOOKUP function help
 

Hi,

Am Fri, 15 Jun 2012 14:30:18 +0000 schrieb Perpmotion:

I've looked all through the posts and can't find a thread with a simliar
issue. This is so stupid, and it's driving me crazy, but for the life
of me, I can't figure it out and now it's giving me a headache. Here's
the scenario: I have a cell which contains a product code number (the
length can range from 3 digits to 6 digits). I have to search a data
table where all of the numbers are formatted as 6 digits, hyphenated
with a second set of digits. What I need to do is when the user enters
the first set of digits in one cell, the function performs a LOOKUP to
the data table, finds the entered 3-6 digit number within the column of
6 digit numbers (prior to the hyphenation), and retrieves the value in
the column to the right (or left...I can move the columns) of the list
of the 6 digit numbers. These columns can be formatted as numbers or
text, it doesn't matter, but either way I format them, I can't get the
dadgum LOOKUP (H and V included) function to work. Please help before I
toss my laptop out the window of the 22nd floor.... sample view
attached. THANK YOU.

in C2 try:
=INDEX($F$2:$F$100;VERGLEICH(WAHR;FINDEN(B2;$E$2:$ E$100)0;0))
It's an array formula to enter with CTRL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

LOOKUP function help
 

Hi,

Am Fri, 15 Jun 2012 21:50:34 +0200 schrieb Claus Busch:

in C2 try:
=INDEX($F$2:$F$100;VERGLEICH(WAHR;FINDEN(B2;$E$2:$ E$100)0;0))
It's an array formula to enter with CTRL+Shift+Enter

sorry, I copied the wrong formula:
=INDEX($F$2:$F$100,MATCH(TRUE,FIND(B2,$E$2:$E$100) 0,0))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks again. I know it was not a challenge for you, but it was driving me crazy! Have a good weekend.

Any time :)

The answer by the way for anyone interested was =VALUE(LEFT(CellRef,6))

Thank you, Claus. I have a forumla from Spencer, but I'll try this one, as well. As I told Spencer, I'm trying to make this as simply as possible for the user. Thanks again.