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





[TABLE="width: 537"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Project[/TD]
[TD]Project Costs[/TD]
[TD]Print Costs[/TD]
[TD]Staff Costs[/TD]
[TD]Total Costs[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]10000[/TD]
[TD]15000[/TD]
[TD]20000[/TD]
[TD]45000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]12000[/TD]
[TD]17000[/TD]
[TD]22000[/TD]
[TD]51000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[TD]14000[/TD]
[TD]19000[/TD]
[TD]24000[/TD]
[TD]57000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]D[/TD]
[TD]16000[/TD]
[TD]21000[/TD]
[TD]26000[/TD]
[TD]63000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]18000[/TD]
[TD]23000[/TD]
[TD]28000[/TD]
[TD]69000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]20000[/TD]
[TD]25000[/TD]
[TD]30000[/TD]
[TD]75000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]22000[/TD]
[TD]27000[/TD]
[TD]32000[/TD]
[TD]81000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D[/TD]
[TD]24000[/TD]
[TD]29000[/TD]
[TD]34000[/TD]
[TD]87000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]26000[/TD]
[TD]31000[/TD]
[TD]36000[/TD]
[TD]93000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]28000[/TD]
[TD]33000[/TD]
[TD]38000[/TD]
[TD]99000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]30000[/TD]
[TD]35000[/TD]
[TD]40000[/TD]
[TD]105000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]D[/TD]
[TD]32000[/TD]
[TD]37000[/TD]
[TD]42000[/TD]
[TD]111000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]34000[/TD]
[TD]39000[/TD]
[TD]44000[/TD]
[TD]117000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[TD]36000[/TD]
[TD]41000[/TD]
[TD]46000[/TD]
[TD]123000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]38000[/TD]
[TD]43000[/TD]
[TD]48000[/TD]
[TD]129000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]40000[/TD]
[TD]45000[/TD]
[TD]50000[/TD]
[TD]135000[/TD]
[/TR]
</tbody><colgroup><col><col span="4"><col></colgroup>[/TABLE]
[TABLE="width: 285"]
<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>[TR]
[TD="class: xl82, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl82, width: 72, bgcolor: transparent"][/TD]
[TD="class: xl83, width: 174, bgcolor: transparent, colspan: 2"]Cost[/TD]
[TD="class: xl82, width: 69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]YEAR[/TD]
[TD="class: xl82, bgcolor: transparent"]2[/TD]
[TD="class: xl84, bgcolor: transparent, colspan: 2"] [/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]BAND[/TD]
[TD="class: xl82, bgcolor: transparent"]C[/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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)