+ Reply to Thread
Results 1 to 3 of 3

Find MAX of Column D (conditional on set data in Column B & C) to LOOKUP Column A

  1. #1
    Registered User
    Join Date
    05-23-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    1

    Question Find MAX of Column D (conditional on set data in Column B & C) to LOOKUP Column A

    Hi everyone,
    A tough one...
    I have names in Column A
    I have ages in Column B
    I have gender in Column C
    I have totals in Column D
    Column E is where I want the desired data of the MAX function to show

    I want Column E to find the MAX of Column D based on the conditions that Column B="8" and Column C="F". I then want the Column E to display the data of Column A, based on the MAX.

    Example of the spreadsheet (Column E shows what I would like to search for):
    Capture.PNG

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Find MAX of Column D (conditional on set data in Column B & C) to LOOKUP Column A

    "...based on the conditions that Column B="8"..."
    There is no 8 in column B, your data or your description of the problem is wrong.

    Simply put, dont you just want the top Male and Female scorers in each age group?
    Last edited by Special-K; 05-23-2017 at 08:00 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,913

    Re: Find MAX of Column D (conditional on set data in Column B & C) to LOOKUP Column A

    Try this in E2:

    =IF(D2=MAX(IF(($B$2:$B$80=B2)*($C$2:$C$80=C2),$D$2:$D$80)),B2&" Year Old"&IF(C2="M"," Male"," Female")&" Winner","")

    Enter with Ctrl+Shift+Enter.

+ 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. [SOLVED] Lookup range of numbers in one column and average corresponding data from next column
    By phenixwars in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-08-2014, 12:20 AM
  2. Replies: 5
    Last Post: 05-14-2014, 08:35 AM
  3. [SOLVED] find set value in column d then get value in column a, INDEX or LOOKUP?
    By lexi521 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2013, 03:17 PM
  4. Dynamic Column Lookup, find the last row, apply conditional formating
    By ronaldj31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-16-2013, 04:18 AM
  5. Lookup OR Find value of Colum F in Column G and Provide output in Column H
    By Pankaj05 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2013, 07:37 AM
  6. Lookup/Find value of Colum F in Column G and Provide output in Column H
    By Pankaj05 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2013, 07:36 AM
  7. Replies: 5
    Last Post: 06-01-2011, 01:19 PM

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