Sumifs + vlookup

[Nanaia]

I'm trying to do something I am sure has been done before. I can't get it to work the way I want though. I have two criteria that need to be used (depth and edge letter) to look up the correct dimension. I've tried VLOOKUP, which does fine if I only want to look up one criteria. I'm not sure how to combine it with SUMIF, or SUMIFS or if I'm on the wrong track.

The user will enter depth (1 3/8, 2, 2 1/2, 3, 3 1/2, 4) and identify the edge condition (A, B, C, D, E, F, G, H, J, K, Q) and I want the formula to come up with the appropriate value. For example, a depth of 1 3/8 with a F edge will be 2.3318. A depth of 4 with an A edge will be 6.3313, and so on. I have the user entering the depth into cell C3 and the edge into cell D3 and am placing the formula for displaying the result in E3. I hope this makes sense. Explaining Excel is always such a challenge.

I have the following reference chart located at V1:AC12

<colgroup><col style="width:47pt" width="62" span="8"> </colgroup><tbody>
[TD="class: xl63, width: 62"]EDGE
[/TD]
[TD="class: xl63, width: 62"]EDGE #
[/TD]
[TD="class: xl64, width: 62"]1 3/8[/TD]
[TD="class: xl63, width: 62"]2[/TD]
[TD="class: xl68, width: 62"]2 1/2[/TD]
[TD="class: xl63, width: 62"]3[/TD]
[TD="class: xl64, width: 62"]3 1/2[/TD]
[TD="class: xl64, width: 62"]4 [/TD]

[TD="class: xl63"]A[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]3.6443[/TD]
[TD="class: xl63"]4.3313[/TD]
[TD="class: xl66"]4.8313[/TD]
[TD="class: xl63"]5.3313[/TD]
[TD="class: xl63"]5.8313[/TD]
[TD="class: xl63"]6.3313
[/TD]

[TD="class: xl63"]B[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]3.6443[/TD]
[TD="class: xl63"]4.3313[/TD]
[TD="class: xl66"]4.8313[/TD]
[TD="class: xl63"]5.3313[/TD]
[TD="class: xl63"]5.8313[/TD]
[TD="class: xl63"]6.3313[/TD]

[TD="class: xl63"]C[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]1.465[/TD]
[TD="class: xl63"]2.152[/TD]
[TD="class: xl66"]2.652[/TD]
[TD="class: xl63"]3.152[/TD]
[TD="class: xl63"]3.652[/TD]
[TD="class: xl63"]4.152[/TD]

[TD="class: xl63"]D[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1.735[/TD]
[TD="class: xl63"]2.422[/TD]
[TD="class: xl66"]2.922[/TD]
[TD="class: xl63"]3.422[/TD]
[TD="class: xl63"]3.922[/TD]
[TD="class: xl63"]4.422[/TD]

[TD="class: xl63"]E[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]3.6443[/TD]
[TD="class: xl63"]4.3313[/TD]
[TD="class: xl66"]4.8313[/TD]
[TD="class: xl63"]5.3313[/TD]
[TD="class: xl63"]5.8313[/TD]
[TD="class: xl63"]6.3313[/TD]

[TD="class: xl63"]F[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2.3318[/TD]
[TD="class: xl66"]3.0188[/TD]
[TD="class: xl66"]3.5188[/TD]
[TD="class: xl63"]4.0188[/TD]
[TD="class: xl63"]4.5188[/TD]
[TD="class: xl63"]5.0188[/TD]

[TD="class: xl63"]G[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]1.465[/TD]
[TD="class: xl63"]2.152[/TD]
[TD="class: xl66"]2.652[/TD]
[TD="class: xl63"]3.152[/TD]
[TD="class: xl63"]3.652[/TD]
[TD="class: xl63"]4.152[/TD]

[TD="class: xl63"]H[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]2.0193[/TD]
[TD="class: xl63"]2.7065[/TD]
[TD="class: xl66"]3.2065[/TD]
[TD="class: xl63"]3.7065[/TD]
[TD="class: xl63"]4.2065[/TD]
[TD="class: xl63"]4.7065[/TD]

[TD="class: xl67"]J[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]3.2691[/TD]
[TD="class: xl66"]3.9561[/TD]
[TD="class: xl66"]4.4561[/TD]
[TD="class: xl63"]4.9561[/TD]
[TD="class: xl63"]5.4561[/TD]
[TD="class: xl63"]5.9561[/TD]

[TD="class: xl63"]K[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2.3318[/TD]
[TD="class: xl66"]3.0188[/TD]
[TD="class: xl66"]3.5188[/TD]
[TD="class: xl63"]4.0188[/TD]
[TD="class: xl63"]4.5188[/TD]
[TD="class: xl63"]5.0188[/TD]

[TD="class: xl63"]Q[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]3.6443[/TD]
[TD="class: xl63"]4.3313[/TD]
[TD="class: xl66"]4.8313[/TD]
[TD="class: xl63"]5.3313[/TD]
[TD="class: xl63"]5.8313[/TD]
[TD="class: xl63"]6.3313[/TD]

</tbody>

 

[tyija1995]

Hello,

Try use this formula:

=INDEX($V$1:$AC$12,MATCH(D3,V1:V12,FALSE),MATCH(C3,V1:AC1,FALSE))
Where C3 is the depth chosen by the user and D3 is the edge chosen by the user.

EDIT: Changed the formula as it was incorrect - apologies!
EDIT2: I should really read the whole question first as I repeated some things you put sorry!

 

[Nanaia]

I'm not sure drop downs will work because the value displayed as the result is going to be added together with three other edge values located in other cells to give a total.

 

[Special-K99]

Just link the dropdown to cells C3 and D3 where the results will be stored. use the formula supplied to extract the correct figure.
Then manipulate that any way you want.

If you're doing the same thing four times (quote: "three other") just have 8 dropdowns two for the first selection two for second two for the third two for the fourth, linking to a different pair of cells.
Obviously the above formula will occur three more times with slightly different MATCH( ) values based on whatever cells you link to.

 

[Nanaia]

Awesome! Your edited formula works great! Thank you!