VLOOKUP a value based on picking 2 criteria from drop down boxes

[Mark Roberts]

Hi All,

I don't know if this is possible at all. I ahve created two drop down boxes. One called "YEAR" and another for "BAND/PROJECT".
What I need the end user to do is change these to whatever they want. So they could choose Year 2 and Band/Project C as per the below. In this case I would like the Total Cost to appear as 81000.

The Total Costs Cell is D4 (this is where I would like 81000 to show)

The YEAR drop dwon box is Cell C4 and the BAND/PROJECT drop down box is in Cell C5.

The first table shown below and where I would like to LOOKUP are cells F50:K66

Many thanks for any help!

Regards,
Mark

Year	Project	Project Costs	Print Costs	Staff Costs	Total Costs
1	A	10000	15000	20000	45000
1	B	12000	17000	22000	51000
1	C	14000	19000	24000	57000
1	D	16000	21000	26000	63000
2	A	18000	23000	28000	69000
2	B	20000	25000	30000	75000
2	C	22000	27000	32000	81000
2	D	24000	29000	34000	87000
3	A	26000	31000	36000	93000
3	B	28000	33000	38000	99000
3	C	30000	35000	40000	105000
3	D	32000	37000	42000	111000
4	A	34000	39000	44000	117000
4	B	36000	41000	46000	123000
4	C	38000	43000	48000	129000
4	D	40000	45000	50000	135000

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

		Cost
YEAR	2
BAND	C

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" width="72"> <col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" width="104"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;" width="70"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <tbody>
</tbody>

 

[ryansclev]

You could use sumifs: =Sumifs('Project Cost Range', 'Year Range', Year, 'Band Range', Band)

Substitute your ranges and reference the cells for year and band.

 

[ryansclev]

More specifically with your values the formula should be =Sumifs($K$50:$K$66,$F$50:$F$66,$C$4,$G$50:$G$66,$C$5)