Formula help

[Formula help]

A B
1 90 cell C2
2 95
3 98
4 101
5 110
6 115
7 120
8 125
9 125
10 125
11 125
12 125
13 125
14 125
15 125


I have 2 columns, A and B. Column A contains consecutive numbers, such as 1
thru 15 as shown. Column B can contain any increasing numbers as shown. Note
that the numbers can start with any value but will increase down the column.
The largest number, at any point in the column, will not increase in value
but will be repeated for the remainder of the column. In the above example,
125, the largest number, first appears in column B and corresponds with 8 in
column A. 125 will continue to be dispalyed until the end of column B. In a
separate cell, cell C2, I would like to show the number in column A that
corresponds to the first time the largest number in column B is shown. Note
that each time I open the worksheet, the numbers in column B will change.

I need help with the formula for cell C2.

Bill

 

[B. R.Ramachandran]

Hi,

If the consecutive numbers in Column A always start with 1 at A2, use the
following formula:
=MATCH(MAX(B:B),B:B,0)-1

Otherwise, you can use the following formula which is more general:
=INDIRECT("A"&MATCH(MAX(B:B),B:B,0))

With regards,
B. R. Ramachandran