formula for multiple drop list selections

[davethacker]

I need to know how to cause box (C5) in my example to be populated from the lower table with the associated door factor related to the selections in (C2) and (C3). I have used the formula

=if(C2="Charleston")*(C3="Oak"),B20,if(C2="Concord")*(C3="Oak),B21,if and so on.....

This works however this can only be repeated seven times in the formula bar. I need Thirty two as you can see by the lower table in my example. Any suggestions on how this can be accomplished?

Thanks for your help,
Dave

Excel_validation_problem.xls
	A	B	C	D	E	F	G	H
1
2	SelectDoorStyle		Charleston
3	SelectWoodSpecie		Oak
4	SelectAvailablestain		Autumn
5	Doorfactor
6					Availablewoodspecies
7					Oak	Alder	Maple	Cherry
8				AvailableStains	Natural	Natural	Natural	Natural
9					Autumn	Golden	Golden	Golden
10					Nutmeg	Autumn	Autumn	Autumn
11					Java	Sienna		Sienna
12					Pacifica	Brandy		Brandy
13					Juniper	Cinnamon		Cinnamon
14					Graphite	Nutmeg		Nutmeg
15								Java
16
17
18	Doorstyles	Availablewoodspecies
19		Oak	Alder	Maple	Cherry
20	Charleston	1.48	1.48	1.5	1.65	Doorfactor
21	Concord	1.3	1.3	1.4	1.42
22	Boston	1.42	1.42	1.5	1.59
23	Savannah	1.36	1.36	1.4	1.48
24	Dakota	1.48	1.48	1.5	1.59
25	Wyoming	1.36	1.36	1.4	1.48
26	Colorado	1.53	1.53	1.6	1.65
27	Montana	1.42	1.42	1.5	1.53
Sheet1

 

[njimack]

This should do the trick...
=INDEX(B20:E27,MATCH(C2,A20:A27,0),MATCH(C3,B19:E19,0))

Note: I'd suggest using named ranges to make the formula much easier to read/audit

 

[eblake]

Hi, I think I understand what you want, try putting this in c5

=INDEX($B$20:$E$27,MATCH($C$2,$A$20:$A$27,0),MATCH($C$3,$B$19:$E$19,0))

Enjoy...

 

[davethacker]

Got it.

Thanks for the help