Results 1 to 7 of 7

Find Max and Min based on cell reference(s), display corresponding value

Threaded View

  1. #1
    Registered User
    Join Date
    06-30-2009
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Find Max and Min based on cell reference(s), display corresponding value

    MY CURRENT FORMULAS/TABLE FORMAT:

    =MAX(IF(A:A=K16,D:D))
    (Displays the Price for the most expensive Item of the Type entered in K16.)

    =INDEX(C:C,MATCH(MAX(D:D),D:D,0))
    (Displays most expensive Item in table.)

    (A:A is Type column)
    (C:C is Item column)
    (D:D is Price column)
    (H:H is basically a True/False column)
    (K16 is the Type I am telling it to look for)

    FIRST QUESTION: How can I modify the formula to show that newly-found Price's corresponding Item (C:C)?

    SECOND QUESTION: How can I modify the formula to show the Item (C:C) that matches multiple criteria (A:A column matches K16, H:H matches "x")?

    THIRD QUESTION: How can I modify the formula to show the Price (D:D) that matches multiple criteria? (A:A column matches K16, H:H matches "x".)

    NOTE ON ATTACHMENT: The GREEN cells are the ones I am trying to populate.

    (P.S. I used this helpful thread to get to this point: http://www.excelforum.com/excel-misc...reference.html)
    Attached Files Attached Files
    Last edited by mpls; 06-30-2009 at 02:25 PM. Reason: attached example file

Thread Information

Users Browsing this Thread

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

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