Drop down with value lookup

[uttamsaxena]

I want to make a sheet with following details

Cell A1 has a category e.g. fruits.
Cell B1 should have a dropdown list like Apple, Mango, Bananas etc.
Cell C1 should display rate of the fruit which has been selected by the user in cell B1 dropdown by looking up the rates stored in Sheet 2 of this workbook.

This book has another sheet 2 which should be used to store rates in column B and name of fruit in column A. This is for easy editing of the rates.

I think you have got my problem. Please help me how to do this easily. Suggeting a link or tut will also be helpful. Thanks.

 

[WillR]

Use a combination of data validation (for cells 1 & 2) and a LOOKUP for cell 3 (using INDEX/MATCH), (assumes cell 123 are A1,B1 & C1)

First create your list if criteria for cell1 (i.e. fruit, veg, meat) say in F1:F3
in cell 1 goto menu|Data|Validation - select allow list & specify the 1st list range above...

Now create 3 lists containing your fruit, veg & meat values (plus their prices in the column to the right) H1:I6) name each list Fruit, Veg & Meat (using INSERT|NAME|DEFINE)

In cell 2, use Data Validation, but place the formula =INDIRECT(A1) in the list range box

In cell 3 - formula =INDEX(H1:I6,MATCH(B1,H1:H6,0),2)

See below

Book1
	A	B	C	D	E	F	G	H	I	J
1	fruit	apple	1			fruit		apple	1
2						veg		pear	2
3						meat		brocilli	3
4	NAMES							cabbage	4
5	H1:H2	fruit						steak	5
6	H3:H4	veg						pork	6
7	H5:H6	meat
8
9
10
Sheet3

Hope this helps

 

[Yogi Anand]

Hi Uttam Saxena:

Using essentially the same idea as WillR's, but catering to your two sheet setup, here is Sheet1 ...

Book1
	A	B	C	D	E
1	Fruit	Mango	2.1
2		cellB1usesDATA|Validation List>Fruit
Sheet1

and here is Sheet2 ...

Book1
	A	B	C	D
1	Apple	1.1	Fruit
2	Mango	2.1
3	Banana	3.1
4	pear	4.1
Sheet2

So, refering back to Sheet1 for cell B1 DATA|Validation , I used range named Fruit (Sheet2!A1:A4);

and the formula in Sheet1 cell C1 is ... =VLOOKUP(B1,Sheet2!A1:B4,2,0)

I sumise as WillR has suggested that you would want to go beyond just the category of Fruit, and add other categories, such as Vegetables, and so on. However, this should get you started.

 

[parsec]

Hello
What I need to add to the formula in Sheet1 cell C1 to be able to display data from other cells, now it displays data only from column B I would like to be able to have data from columns C, D, E for the same item
Thank you
John

 

[WillR]

Parsec....

Is your question to do with this thread? If so, I do not understand what you want help with. Can you explain further?

Will

 

[Aladin Akyurek]

Hello
What I need to add to the formula in Sheet1 cell C1 to be able to display data from other cells, now it displays data only from column B I would like to be able to have data from columns C, D, E for the same item
Thank you
John

Lets suppose the following lookup table...

Book8
	A	B	C	D
1	Items	Desc	Loc	Price
2	Item01	Desc01	Loc01	12.8
3	Item02	Desc02	Loc02	9.93
4	Item03	Desc03	Loc03	21.93
5	Item04	Desc04	Loc04	14.1
6	Item05	Desc05	Loc05	22.44
7	Item06	Desc06	Loc06	16.88
8	Item07	Desc07	Loc07	14.92
9	Item08	Desc08	Loc08	23.59
10	Item09	Desc09	Loc09	8.87
11	Item10	Desc10	Loc10	16.39
12	Item11	Desc11	Loc11	20.5
13	Item12	Desc12	Loc12	13.98
14	Item13	Desc13	Loc13	22
15	Item14	Desc14	Loc14	12.06
Sheet1

Select A2:D15.
Go to the Name Box on the Formula Bar, type LTable (from lookup table), and hit enter.

Now you can set up a data validation dropdown box anywhere in your workbook using LTable and retrieve values associated with a choice from the dropdown box...

Book8
	A	B	C	D	E	F
1
2	Item02		Desc02	Loc01	12.8	2
3
4
Sheet2

Select A2.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:

=INDEX(LTable,0,1)

which gives you the items from the 1st column of LTable.

You can now retrieve values associated with a choice in A2 from LTable efficiently:

In F2 enter:

=MATCH(A2,INDEX(LTable,0,1),0)

In C2 enter & copy across to E2...

=INDEX(LTable,F2,COLUMN()-COLUMN($C$2)+2)

 

[luckycharm]

Aladin's contrib works great and is neat.
Just add $ sign before the F2 in last formula when you paste.

 

[parsec]

Thank you Aladin

The formula is working very well and I am very thankful for the time that you took to help up

Thank you
John

 

[uttamsaxena]

Hi Aladin I tried to it as below and it is working with Vlookup. Pl check & tell me whether it is OK to do it like this or not?

Combo_example2.xls
	A	B	C	D
1
2	Fruit1	rate1	Stock1	dealer1
3	Fruit2	rate2	stock2	dealer2
4	Fruit3	rate3	Stock3	dealer3
5	Fruit4	rate4	Stock4	dealer4
6	Fruit5	rate5	Stock5	dealer5
7	Fruit6	rate6	Stock6	dealer6
8	Fruit7	rate7	Stock7	dealer7
9	Fruit8	rate8	Stock8	dealer8
10	Fruit9	rate9	Stock9	dealer9
11	Fruit10	rate10	Stock10	dealer10
12
Sheet2

Combo_example2.xls
	A	B	C	D
1	Fruit10	rate10	Stock10	dealer10
2
3		ORasbelow
4
5	Fruit8	rate8	Stock8	dealer8
6
7
Sheet1

 

[Aladin Akyurek]

Hi Aladin I tried to it as below and it is working with Vlookup. Pl check & tell me whether it is OK to do it like this or not?...

The formulas do not show, not all anyway.

I have the impression that you want to avoid the additional column. The reason for that is sheer efficiency. Something not be neglected.