Use multiple data validation lists to find a price in grid

[matigras]

Datasheet

	A	B	C
1	Morning	Weekday	Spring
2	Night	Weekend	Summer
3			Fall
4			Winter

<tbody>
</tbody>

Gridsheet

	A	B	C	D	E
1		Morning	Night
2		Weekday	Weekend	Weekday	Weekend
3	Spring	$100	$150	$200	$250
4	Summer	$300	$350	$400	$450
5	Fall	$500	$550	$600	$650
6	Winter	$700	$750	$800	$850

<tbody>
</tbody>

Worksheet

	A	B	C	D
1	Time of Day	Time of Week	Season	Rate
2
3

<tbody>
</tbody>

Data Validation:

Worksheet!A2

• Data Validation > List > =Datasheet!$A$1:$A$2

Worksheet!B2

• Data Validation > List > =Datasheet!$B$1:$B$2

Worksheet!C2

• Data Validation > List > =Datasheet!$C$1:$C$4

Function:

D2 in Worksheet equals "VALUE" based on "Data Validation List" selections in columns A, B, and C as defined in Gridsheet

Examples:

If (Worksheet!A1=Morning) and (Worksheet!B1=Weekend) and (Worksheet!C1=Fall) then Worksheet!D2=$550

If (Worksheet!A1=Night) and (Worksheet!B1=Weekday) and (Worksheet!C1=Summer) then Worksheet!D2=$400


I tried many SUMIF, SUMIFS, IF(AND) functions and can't get this to work.Thanks for looking... helping!

 

[oldbrewer]

					fall			MorningWeekend
					weekend		becomes	Fall
					morning
							rate	$550	######
							col H
		Morning	Morning	Night	Night			MorningWeekday	MorningWeekend	NightWeekday	NightWeekend		row 11
		Weekday	Weekend	Weekday	Weekend		Spring	$100	$150	$200	$250
	Spring	$100	$150	$200	$250		Summer	$300	$350	$400	$450
	Summer	$300	$350	$400	$450		Fall	$500	$550	$600	$650
	Fall	$500	$550	$600	$650		Winter	$700	$750	$800	$850
	Winter	$700	$750	$800	$850
					###
					formula returning $550
					=OFFSET($H$11,MATCH(I2,$H$12:$H$15,0),MATCH($I$1,$I$11:$L$11,0))

<colgroup><col span="8"><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Rickmaurinus]

Hi,

Is the layout of your data something fixed, that has to be as you described? Sometimes the easiest solution, is to change the layout of your data.
For example: Instead of having the lookup the with multiple layers, it could be much easier to use a more tabular layout as follows:

Season	Time of Week	Time of Day	Rate
Spring	Weekday	Morning	100
Summer	Weekday	Morning	300
Fall	Weekday	Morning	500
Winter	Weekday	Morning	700
Spring	Weekend	Morning	150
Summer	Weekend	Morning	350
Fall	Weekend	Morning	550
Winter	Weekend	Morning	750
Spring	Weekday	Night	200
Summer	Weekday	Night	400
Fall	Weekday	Night	600
Winter	Weekday	Night	800
Spring	Weekend	Night	250
Summer	Weekend	Night	450
Fall	Weekend	Night	650
Winter	Weekend	Night	850

<tbody>
</tbody>

Then as formula you could use:

=SUMIFS( Table1[Rate];
Table1[Time of Day]; C2;
Table1[Time of Week]; B2;
Table1[Season]; A2)

or

=SUMPRODUCT( (Table1[Rate] ) *
(Table1[Season] = A2 ) *
(Table1[Time of Week] = B2 ) *
(Table1[Time of Day] = C2 ))

Would that help you?

Regards,
Rick