Multiple column and row lookup - help please

[bamadq]

I'm looking to return a specific value given the criteria of Location, Customer name, Commodity, & Date. I can not get this formula to work and have tried lookups, matches, indexes, etc. Any ideas? Basically, I'm trying to return a value in cell A15 given the criteria above it.


[TABLE="width: 668"]
<tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Location[/TD]
[TD]Customer Name[/TD]
[TD]Commodity[/TD]
[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Alabama[/TD]
[TD]DA[/TD]
[TD]prime[/TD]
[TD] $ 81.00 [/TD]
[TD] $ 56.00 [/TD]
[TD] $ 63.00 [/TD]
[TD] $ 65.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Alabama[/TD]
[TD]AS[/TD]
[TD]secondary[/TD]
[TD] $ 55.00 [/TD]
[TD] $ 49.00 [/TD]
[TD] $ 11.00 [/TD]
[TD] $ 69.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]South Carolina[/TD]
[TD]FT[/TD]
[TD]prime[/TD]
[TD] $ 39.00 [/TD]
[TD] $ 60.00 [/TD]
[TD] $ 95.00 [/TD]
[TD] $ 63.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]South Carolina[/TD]
[TD]DS[/TD]
[TD]Prime[/TD]
[TD] $ 83.00 [/TD]
[TD] $ 70.00 [/TD]
[TD] $ 79.00 [/TD]
[TD] $ 16.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]South Carolina[/TD]
[TD]FT[/TD]
[TD]middle[/TD]
[TD] $ 30.00 [/TD]
[TD] $ 92.00 [/TD]
[TD] $ 84.00 [/TD]
[TD] $ 23.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Criteria:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Location[/TD]
[TD]South Carolina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Customer Name[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Commodity[/TD]
[TD]Prime[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Date[/TD]
[TD="align: right"]10/1/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Cost[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="3"><col></colgroup>[/TABLE]

 

[Weazel]

maybe something like...

=SUMPRODUCT((A2:A6=B11)*(B2:B6=B12)*(C2:C6=B13)*(D1:G1=B14)*(D2:G6))

though you'd need to be careful of duplicates in the location, customer, and commodity criterias

 

[Andrew Poulsom]

Just an alternative:

=INDEX(D2:G6,MATCH(1,INDEX((A2:A6=B11)*(B2:B6=B12)*(C2:C6=B13),),FALSE),MATCH(B14,D1:G1,FALSE))