lookup/match/index help

[TonTon165]

I am having trouble and I am hoping someone can help. In Column A, I have a
list of numbers between 1 and 4(Levels). In Column B, I have a list of
decimal numbers.


Row Column A Column B
1 1 1.05
2 2 1.12
3 3 1.02
4 3 1.06
5 4 1.10
6 2 1.05
7 3 1.13

The best way to explain what I am trying to do is give an example. Lets say
I am in Cell C7. I am wanting to find the last instance of a Level (Column A)
that is one higher than the current Level (so the level for row 7 is 3, so I
am needing to find the last instance of a level 2, or in this case Row 6) and
multiply the number in B7 with the corresponding number from that last
instance. So in C7 it would be 1.1865 (1.13*1.05). I hope this is written to
be understood. Any help would be greatly appreciated.

 

[Bernie Deitrick]

Ton,

In C2, enter the formula

=INDEX(B:B,SUMPRODUCT(MAX(($A$1:A1=(A2-1))*ROW($A$1:A1))))*B2

and copy down to match your column B.

HTH,
Bernie
MS Excel MVP