LOOKUP function help

[Perpmotion]

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

+-------------------------------------------------------------------
|Filename: Sample Table.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=426
+-------------------------------------------------------------------

 

[Spencer101]

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

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

For example, based on your sample image, if you inserted a new column
between "Product Code" and "Standard Rate / Hour" and in F2 enter th
formula *=LEFT(E2,6)* . This will then let you put a vlookup in cel
C2 ( *=VLOOKUP(B2,F2:G28,2,FALSE)* in the case of your sample image bu
with the new hidden column F.) but it will mean the user will have t
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 exampl
workbook, rather than the image file, before I could sort one out

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[Claus Busch]

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

 

[Perpmotion]

Hi,

PM'd you an email address.

S.

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

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

Any time :

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Perpmotion]

Hi,

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

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


Regards
Claus Busch

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 a
possible for the user. Thanks again

+-------------------------------------------------------------------
+-------------------------------------------------------------------