Multiple inputs from drop down lists to give single output from data on separate sheet

[Sports123]

Hi all,

I am trying to have my spreadsheet show costs for running a stadium based on two inputs - Crowd Size and Zones Active.

On Sheet One I have two drop down lists - Crowd Size and Zones Active. I also have a Total Cost cell where I require the output.

On Sheet Two I have my data set - rows detail crowd size, columns detail zones active - giving a grid of costs based on the two variables,

When I select the appropriate drop down variables on Sheet One I am trying to give the correct output from the grid on sheet two, but can not work a formula for more than one input.

Any help would be much appreciated!

Thank you

 

[Michael M]

You'll probably need an INDEX(MATCH function, but can you post the dat so that we can work with it please.....pictures won't cut it I'm afraid.

 

[Sports123]

Two Inputs to One Output.xlsx
	A	B	C
1
2	Crowd Size	0-5,000
3	Zones Active	4 Zones
4
5	Total Cost
6
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet2!$A$2:$A$8
B3	List	=Zones

Two Inputs to One Output.xlsx
	A	B	C	D	E
1	Total Charge (Inc GST)	4 Zones	5 Zones	6 Zones
2	0-5,000	1000	2000	3000
3	5,001-10,000	2000	3000	4000
4	10,001,15,000	3000	4000	5000
5	15,001-20,000		5000	6000
6	20,001-25,000		6000	7000
7	25,001-30,000			8000
8	30,001 +			9000
9
Sheet2

 

[Michael M]

Try using

=SUMPRODUCT((B2=Sheet2!A2:A8)*(B3=Sheet2!B1:D1)*Sheet2!B2:D8)

 

[Sports123]

Fantastic! Worked a treat. Thank you

 

[Michael M]

Glad it worked...