+ Reply to Thread
Results 1 to 8 of 8

Need to calculate Max in range with 2 criteria and return the result of a different column

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Need to calculate Max in range with 2 criteria and return the result of a different column

    Hi,

    I am working on a vehicle cost spreadsheet and wanted to find the last fuel purchase within a certain date range and return column C which shows how many liter purchased. I am able to find the highest mileage but not sure how to show column C instead of the mileage column. I attached sample data. Many thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    G2 =IF(AND($F2=$I$7,$A2>=$I$8,$A2<=$I$9,E2="FUE"),"yes","")

    J11 =INDEX(A1:G28,MATCH("yes",$G$1:$G$28,0),3)

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    Thank you oeldere, is there a possible solution without creating the additional criteria column?

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    See if this helps;

    Array type formula (Ctrl+Shift+Enter)

    Please Login or Register  to view this content.
    You may need to change ";" to "," depending on your regional settings.

    Note: Sample workbook attached...
    Attached Files Attached Files
    Last edited by Haluk; 07-27-2020 at 10:25 AM. Reason: Sample workbook is attached

  5. #5
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    Also, a non-array formula may be:

    Please Login or Register  to view this content.
    Note: You may need to change ";" to "," depending on your regional settings.

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    J11=AGGREGATE(14,6,Table1[Description]/(Table1[Date]>=I8)/(Table1[Date]<=I9)/(Table1[Reg]=I7)/(Table1[Code]="FUE"),1)
    Last edited by CARACALLA; 07-27-2020 at 10:44 AM.

  7. #7
    Registered User
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    Thank you Haluk for the non-CSE formula, I am trying to reference to a different sheet call Service Transactions, what would be the syntax for that?

  8. #8
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Need to calculate Max in range with 2 criteria and return the result of a different co

    You're welcome...

    If the data table is on the sheet "Service Transactions" and you are referring to this table from another sheet, then;

    Please Login or Register  to view this content.
    Change ";" with "," depending on your regional settings.

    Note: Sample workbook is attached.
    Attached Files Attached Files
    Last edited by Haluk; 07-27-2020 at 12:07 PM. Reason: Sample workbook is attached

+ 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] Return result with multiple criteria
    By jlp83 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-19-2020, 09:00 AM
  2. Return a Result Based on 2 Criteria
    By uberathlete in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-26-2016, 07:13 AM
  3. Return the earliest result and latest result with criteria
    By PFDave in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-26-2016, 07:20 AM
  4. Lookup two criteria and return a third criteria as result. Aaaargh!
    By dearthofjoy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-08-2013, 12:13 PM
  5. Replies: 4
    Last Post: 09-02-2011, 12:27 PM
  6. Return result from two criteria
    By Ben4481 in forum Excel General
    Replies: 2
    Last Post: 09-13-2010, 10:15 AM
  7. Return Result based on Multiple Criteria
    By franciz in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-20-2009, 01:30 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