+ Reply to Thread
Results 1 to 4 of 4

How do i find the max of two values from index match max of a column

  1. #1
    Registered User
    Join Date
    10-08-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2013
    Posts
    2

    How do i find the max of two values from index match max of a column

    I am newbie to excel. I have this following data in excel sheet.

    swQj9.png

    http://i.stack.imgur.com/swQj9.png

    I want to have the value of G11 returned 27, as the value of its corresponding cell in column D, which is D11 matches to E8, and E9, which then correspond to the values of 14 and 27 in column J. Could someone tell me how to return a match value when I have two match situations and I need the maximum one. I am using the following formula.

    =IF(D11=1,0,INDEX($H$4:$H$13,MATCH(D11,$E$4:$E$13,0),1))

    I know if I use

    =IF(D11=1,0,INDEX($H$4:$H$13,MATCH(D11,$E$4:$E$13,0)+1,1))

    it returns me with the value 27 or greater of the both values but it will not work if I have three values and I want the maximum.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: How do i find the max of two values from index match max of a column

    You can use an Array Formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    committed with Ctrl-Shift-Enter rather than just Enter

    This will be confirmed as an array formula with curly brackets:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: How do i find the max of two values from index match max of a column

    Sorry, the full formula is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    committed with Ctrl-Shift-Enter rather than just Enter



    Regards, TMS

  4. #4
    Registered User
    Join Date
    10-08-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: How do i find the max of two values from index match max of a column

    Thank you TMShucks for taking time to help me in the matter. However, I am sorry but I could not implement the formula to my advantage. It is only because I know very little. Here is the link for the file I have been working on http://goo.gl/V9VYfa. What I really want to is to return in cell G11 the max value in D11 when it is equal to any of the cells in column E, when equal the return value would be the corresponding value in column H. As in the case of D11 there are two matching cells E8, and E9. I want to return the max value among these.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index match offset to get subsequent index values in a column
    By Andrew_Step in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2013, 02:55 PM
  2. [SOLVED] Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)
    By seaottr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-06-2013, 10:24 PM
  3. [SOLVED] Index/Match/(or other)? Multiple Column/Row Values
    By Pooger in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-22-2013, 09:27 PM
  4. Using index and match to find several values
    By Humanist in forum Excel General
    Replies: 1
    Last Post: 12-19-2011, 11:49 AM
  5. Replies: 3
    Last Post: 07-14-2011, 11:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1