Tricky VLookup Funkcion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I could need some help with a tricky VLookup function.
I want my spreadsheet to Lookup articel numbers by typing in the articel decription. Problem is that some of the articel have 2 or 3 different numbers because they are produced in different plants. For example

USA Product european produc
Product A 2000
Product B 20006 3000
Product C 20013 3000

So when I write Product A in column A the Art number(20001) should automatically come up in column B
When I write Product B in the next line column B should give me the option to chose between 20006 or 30001. Perhaps in a dropdown or so.

I can do the first part with a normal VLoolkup function but I have no idear how to do the res

Thanks very much for your hel
I hope I wrote it that someone can understand the proble

Matt
 
Hi
this is a tricky one. Try the following
- Your product information is listed on a separate sheet (lets say
called 'Prod_list')
- On your master sheet called 'master' (where you want the option list)
do the following:
-> the frist column (A) is the column to store your product names
(you may use also a dropdown listbox for this):
a) create a defined name (goto 'Insert - Name - Define
Name')
b) call this name 'Prod_names'
c) enter the formula: ='Prod_list'!$A$1:$A$999
d) on your master sheet select the cells in column A,
goto 'Data - Validation'
e) Choose 'List' and enter the formula: =Prod_names
-> the second column (B) is a helper column. Enter the following
formula in B1:
=MATCH(A1,'Prod_list'!$A$2:$A$999,0)
-> copy down for all rows
-> you may hide this row
- Create a new defined name (goto 'Insert - Name - Define name'). Lets
say called 'Option_list'. Enter the following formula for this name:
=OFFSET('Prod_list'!$B$2:$D$999,'master'!$B1-1,0)
- In your master list select the cells in column C (where we create the
option list). goto 'Data - Validation'. Choose 'List' and enter the
formula
=Option_List
 
Makes sence in a way. I am trying for a while now but I can't get it run. I think I made all you sugessted but in column C the Validation gives me the option between all the art numbers and not only the numbers for this one product. By the way I have products with 3 different art numbers while others have only one. So the products with only one Art number should directly pop up after selecting in column

Not sure if that helps to clearify

Anyway thanks for your help so far and by the wa

Ich komm aus Nuernber
Und nochmals Dank


----- Frank Kabel wrote: ----

H
this is a tricky one. Try the followin
- Your product information is listed on a separate sheet (lets sa
called 'Prod_list'
- On your master sheet called 'master' (where you want the option list
do the following
-> the frist column (A) is the column to store your product name
(you may use also a dropdown listbox for this)
a) create a defined name (goto 'Insert - Name - Defin
Name'
b) call this name 'Prod_names
c) enter the formula: ='Prod_list'!$A$1:$A$99
d) on your master sheet select the cells in column A
goto 'Data - Validation
e) Choose 'List' and enter the formula: =Prod_name
-> the second column (B) is a helper column. Enter the followin
formula in B1
=MATCH(A1,'Prod_list'!$A$2:$A$999,0
-> copy down for all row
-> you may hide this ro
- Create a new defined name (goto 'Insert - Name - Define name'). Let
say called 'Option_list'. Enter the following formula for this name
=OFFSET('Prod_list'!$B$2:$D$999,'master'!$B1-1,0
- In your master list select the cells in column C (where we create th
option list). goto 'Data - Validation'. Choose 'List' and enter th
formul
=Option_Lis


-
Regard
Frank Kabe
Frankfurt, German

MATT wrote
 
Back
Top