Question from an Excel newbie

[marcse86]

Hi guys,

I am new to Excel and would like to know what formula to use for the example below.

I have a data table which includes the type of fruit, the size and the result.

I know how to use the data validation function to create a selectable drop down list for both items, but would like to automatically calculate the result based on the options that I chose.

I would like this to be displayed in the result column.

​

Size	Apples	Oranges	Bananas
Small	1	2	3
Medium	4	5	6
Large	7	8	9

<tbody>
</tbody>

​

Fruit	Apples
Size	Med
Result	4

<tbody>
</tbody>

Thanks in advance

 

[FDibbins]

See if this will get you started?

	B​	C​	D​
3​	Type​	Category​	Breed​
4​	Fish	Marine	Shark

This would be where your drop-downs are
1. create a range name for the main category
2. create a range name for each sub-category, based on what it is

for mine, the main category is called Type (A2)
for the sub categories I give them the same name as in Type (B2:E2)
(if you have another level, you would repeat this for each subcategory, to get sub-sub-categries)

To get the DD's to work...

DD for B4 =TYPE
DD for C4 =INDIRECT(SUBSTITUTE(B4," ","_"))

THis would be your data table that th drop-downs reference...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Level 1		Level 2					Level 3
2​	Type		Mammal	Bird	Fish	Bug		Dog	Cat	Fresh Water	Marine
3​	Mammal		Dog	Domestic	Fresh Water	Insect		Doberman	Lion	Trout	Shark
4​	Bird		Cat	Wild	Marine	Arachnid		Poodle	Tiger	Minow	Barracuda
5​	Fish		Cow					bulldog	House	Pike
6​	Bugs

Hope this helps?

 

[Brian from Maui]

Hi guys,

I am new to Excel and would like to know what formula to use for the example below.

I have a data table which includes the type of fruit, the size and the result.

I know how to use the data validation function to create a selectable drop down list for both items, but would like to automatically calculate the result based on the options that I chose.

I would like this to be displayed in the result column.

​

Size	Apples	Oranges	Bananas
Small	1	2	3
Medium	4	5	6
Large	7	8	9

<tbody>
</tbody>

​

Fruit	Apples
Size	Med
Result	4

<tbody>
</tbody>

Thanks in advance

Try,

=INDEX(B2:D4,MATCH(E1,A2:A4,0),MATCH(F1,B1:D1,0))

Where B2:D4 houses your values, A2:A4 houses your sizes and B1:D1 houses fruit names. Enter size in E1 and fruit in F1.

 

[marcse86]

Thank you!

 

[FDibbins]

oops I think I showed you how to creas DD's when you already had that part, and just wanted to pull data based on those DD selections - apologies