Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & match/IF/Vlookup formula help
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 -- Adam&ellie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & match/IF/Vlookup formula help
Maybe
=SUMPRODUCT((A2:A7="a")*(C1:F1=3)*(C2:F7)) Mike "adam&ellie" wrote: 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 -- Adam&ellie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & match/IF/Vlookup formula help
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" wrote: 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 -- Adam&ellie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & match/IF/Vlookup formula help
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 -- Adam&ellie "Mike H" wrote: 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" wrote: 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 -- Adam&ellie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & match/IF/Vlookup formula help
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:F 7)) G1= Item H1= Price I1= Week Mike "adam&ellie" wrote: 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 -- Adam&ellie "Mike H" wrote: 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" wrote: 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 -- Adam&ellie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index & match/IF/Vlookup formula help
Think that answers my question.
Cheerrs -- Adam&ellie "Mike H" wrote: 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:F 7)) G1= Item H1= Price I1= Week Mike "adam&ellie" wrote: 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 -- Adam&ellie "Mike H" wrote: 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" wrote: 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 -- Adam&ellie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP vs INDEX and MATCH | Excel Discussion (Misc queries) | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Need Help with Index and Match or Vlookup | Excel Worksheet Functions | |||
n/a in vlookup/index/match formula | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |