Results 1 to 4 of 4

Thread: Find Value Based On MAX Value

  1. #1
    Junior Member Beaker Rex's Avatar
    Join Date
    Aug 2011
    Posts
    2
    Rep Power
    0

    Find Value Based On MAX Value

    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
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Beaker Rex,

    Welcome to ExcelFox !!!!


    In B36,

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

    Adjust the coloured range to suit.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member Beaker Rex's Avatar
    Join Date
    Aug 2011
    Posts
    2
    Rep Power
    0
    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

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  2. Sum Ifs Formula needed with Max Date range
    By trankim in forum Excel Help
    Replies: 2
    Last Post: 09-19-2012, 09:50 AM
  3. Find Value Based On Criteria
    By excel_learner in forum Excel Help
    Replies: 10
    Last Post: 07-30-2011, 10:27 AM
  4. Replies: 4
    Last Post: 04-07-2011, 07:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •