Find Value Based On MAX Value

[Beaker Rex]

Hello All,
Major noob here, I'm trying to find the cell in Column A that corresponds with a max formula in the B column. if you look at the attachment, you see at B34 the max for B column is "12.43". I need to find the cell in A column that will tell me the "Date" of that max. If the max changes I would like the date to change with it.

Thanks in advance

Mike

[Admin]

Hi Beaker Rex,

Welcome to ExcelFox !!!!


In B36,

=INDEX(A5:A33,MATCH(B34,B5:B33,0))

Adjust the coloured range to suit.

[Beaker Rex]

Thank you for your help, that worked excellent. Question, which of the Index Argument's would be more correct, the shorter "array" version or the "reference" version or does it matter since both provide the same solution?

Thanks
Mike

[Admin]

Hi,

Both the arguments are correct on its own merit. The most common version is 'array'. The reference version is being used when you have nonadjacent ranges(Areas).

Directly from help file;



INDEX(reference,row_num,column_num,area_num)

Reference is a reference to one or more cell ranges.

If you are entering a nonadjacent range for the reference, enclose reference in parentheses.

If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).
Row_num is the number of the row in reference from which to return a reference.

Column_num is the number of the column in reference from which to return a reference.

Area_num selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.

For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4.