Index & match/IF/Vlookup formula help

[adam&ellie]

I desperately need help finding a formula that would work on a spreadsheet.
If I had the table below (which is A1:F7) does anyone know a formula that
could give me the figure for items a's volume at week 3? I would also need to
be able to use the same formula to find other numbers using different items,
parameter and week number.

item 1 2 3 4
a price 50 60 20 30
a volume 10 15 12 30
a uplift 18 2 565 12
b price 41 10 350 30
b volume 44 65 49 51
b uplift 85 53 3 4

I dont know if this would help but a possible pointer in the table below
this formula works
=INDEX(B2:E4,MATCH(A2,A2:A4,0),MATCH(D1,B1:E1,0))

1 2 3 4
a 50 60 20 30
b 10 15 12 30
c 18 2 565 12

The only difference I think but can't get my head around is to build in the
price/volume/uplift variable

Please help

 

[Mike H]

Maybe

=SUMPRODUCT((A2:A7="a")*(C1:F1=3)*(C2:F7))

Mike

 

[Mike H]

A bit easier

=SUMPRODUCT((A2:A7=G1)*(C1:F1=H1)*(C2:F7))

Where
G1 = a
H1 = 3
Both of which can be changed to give different lookups.

Mike

 

[adam&ellie]

And will that give me the figure. ie in the example item a's volume in week 3
= 12? Just i don't' see the formula taking the price/volume/uplift column
into account. It looks like this would sum all the price, volume and uplift
for item a in week 3

 

[Mike H]

Hi,

I'm becoming unclear about what the question is now but you simply add
another condition,

=SUMPRODUCT((A2:A7=G1)*(B2:B7=H1)*(C1:F1=I1)*(C2:F7))

G1= Item
H1= Price
I1= Week

Mike

 

[adam&ellie]

Think that answers my question.

Cheerrs