Index & match/IF/Vlookup formula help

A

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
 
M

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
 
A

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index and match formulas 1
Find value based on two lookup values 2
Countifs or a pivot 1
Need some PWA help 0
formula to display cell address 3
VLookup or Macro? 0
Average problem 1
Insert Index into a Table 1

Top