Week # 1 2 3 4 5

Ratio 0.9 0.9 0.8 0.8 0.6

Select week from Drop Down List ____ (we have 1,2,3,4,5 inside)

So how can we use index,match,product or other excel formulas for performing the following task:

If 3 is selected from the dropdown list, then we multiply 0.90.90.8

If 2 is selected from dropdown list, then we multiply 0.9*0.9

Can you please help?

I could not find how to use index match or this

CodePudding user response：

You can use PRODUCT and INDEX to achieve this. No need for MATCH here. Take a look at this example:

In case your weeks do not start at one, you do have to use MATCH: =PRODUCT(B2:INDEX(B2:F2,1,MATCH(H1,B1:F1)))