Use multiple data validation lists to find a price in grid

[matigras]

Datasheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 104, align: center"]A[/TD]
[TD="width: 104, align: center"]B[/TD]
[TD="width: 104, align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Morning[/TD]
[TD="align: center"]Weekday[/TD]
[TD="align: center"]Spring[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Night[/TD]
[TD="align: center"]Weekend[/TD]
[TD="align: center"]Summer[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Fall[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Winter[/TD]
[/TR]
</tbody>[/TABLE]

Gridsheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 104, align: center"][/TD]
[TD="width: 64, align: center"]A[/TD]
[TD="width: 84, align: center"]B[/TD]
[TD="width: 84, align: center"]C[/TD]
[TD="width: 84, align: center"]D[/TD]
[TD="width: 84, align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="colspan: 2, align: center"]Morning[/TD]
[TD="colspan: 2, align: center"]Night[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Weekday[/TD]
[TD="align: center"]Weekend[/TD]
[TD="align: center"]Weekday[/TD]
[TD="align: center"]Weekend[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Spring[/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]$150[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]$250[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Summer[/TD]
[TD="align: center"]$300[/TD]
[TD="align: center"]$350[/TD]
[TD="align: center"]$400[/TD]
[TD="align: center"]$450[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Fall[/TD]
[TD="align: center"]$500[/TD]
[TD="align: center"]$550[/TD]
[TD="align: center"]$600[/TD]
[TD="align: center"]$650[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Winter[/TD]
[TD="align: center"]$700[/TD]
[TD="align: center"]$750[/TD]
[TD="align: center"]$800[/TD]
[TD="align: center"]$850[/TD]
[/TR]
</tbody>[/TABLE]


Worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 104, align: center"]A[/TD]
[TD="width: 104, align: center"]B[/TD]
[TD="width: 104, align: center"]C[/TD]
[TD="width: 104, align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Time of Day[/TD]
[TD="align: center"]Time of Week[/TD]
[TD="align: center"]Season[/TD]
[TD="align: center"]Rate[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="width: 1207"]
<colgroup><col span="8"><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fall[/TD]
[TD][/TD]
[TD][/TD]
[TD]MorningWeekend[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]weekend[/TD]
[TD][/TD]
[TD]becomes[/TD]
[TD]Fall[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]morning[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rate[/TD]
[TD]$550[/TD]
[TD]######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Morning[/TD]
[TD]Morning[/TD]
[TD]Night[/TD]
[TD]Night[/TD]
[TD][/TD]
[TD][/TD]
[TD]MorningWeekday[/TD]
[TD]MorningWeekend[/TD]
[TD]NightWeekday[/TD]
[TD]NightWeekend[/TD]
[TD][/TD]
[TD]row 11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Weekday[/TD]
[TD]Weekend[/TD]
[TD]Weekday[/TD]
[TD]Weekend[/TD]
[TD][/TD]
[TD]Spring[/TD]
[TD]$100[/TD]
[TD]$150[/TD]
[TD]$200[/TD]
[TD]$250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Spring[/TD]
[TD]$100[/TD]
[TD]$150[/TD]
[TD]$200[/TD]
[TD]$250[/TD]
[TD][/TD]
[TD]Summer[/TD]
[TD]$300[/TD]
[TD]$350[/TD]
[TD]$400[/TD]
[TD]$450[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Summer[/TD]
[TD]$300[/TD]
[TD]$350[/TD]
[TD]$400[/TD]
[TD]$450[/TD]
[TD][/TD]
[TD]Fall[/TD]
[TD]$500[/TD]
[TD]$550[/TD]
[TD]$600[/TD]
[TD]$650[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fall[/TD]
[TD]$500[/TD]
[TD]$550[/TD]
[TD]$600[/TD]
[TD]$650[/TD]
[TD][/TD]
[TD]Winter[/TD]
[TD]$700[/TD]
[TD]$750[/TD]
[TD]$800[/TD]
[TD]$850[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Winter[/TD]
[TD]$700[/TD]
[TD]$750[/TD]
[TD]$800[/TD]
[TD]$850[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]formula returning $550[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=OFFSET($H$11,MATCH(I2,$H$12:$H$15,0),MATCH($I$1,$I$11:$L$11,0))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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:
[TABLE="width: 508"]
<tbody>[TR]
[TD]Season[/TD]
[TD]Time of Week[/TD]
[TD]Time of Day[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD]Weekday[/TD]
[TD]Morning[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Summer[/TD]
[TD]Weekday[/TD]
[TD]Morning[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Fall[/TD]
[TD]Weekday[/TD]
[TD]Morning[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Winter[/TD]
[TD]Weekday[/TD]
[TD]Morning[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD]Weekend[/TD]
[TD]Morning[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]Summer[/TD]
[TD]Weekend[/TD]
[TD]Morning[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD]Fall[/TD]
[TD]Weekend[/TD]
[TD]Morning[/TD]
[TD="align: right"]550[/TD]
[/TR]
[TR]
[TD]Winter[/TD]
[TD]Weekend[/TD]
[TD]Morning[/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD]Weekday[/TD]
[TD]Night[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]Summer[/TD]
[TD]Weekday[/TD]
[TD]Night[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]Fall[/TD]
[TD]Weekday[/TD]
[TD]Night[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD]Winter[/TD]
[TD]Weekday[/TD]
[TD]Night[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD]Weekend[/TD]
[TD]Night[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Summer[/TD]
[TD]Weekend[/TD]
[TD]Night[/TD]
[TD="align: right"]450[/TD]
[/TR]
[TR]
[TD]Fall[/TD]
[TD]Weekend[/TD]
[TD]Night[/TD]
[TD="align: right"]650[/TD]
[/TR]
[TR]
[TD]Winter[/TD]
[TD]Weekend[/TD]
[TD]Night[/TD]
[TD="align: right"]850[/TD]
[/TR]
</tbody>[/TABLE]

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