+ Reply to Thread
Results 1 to 3 of 3

Get MIN and MAX values from column B where corresponding column A matches criteria

  1. #1
    Registered User
    Join Date
    12-08-2022
    Location
    Coimbatore, India
    MS-Off Ver
    2010
    Posts
    2

    Question Get MIN and MAX values from column B where corresponding column A matches criteria

    I have a excel sheet which has tables of a different vehicles taking different market routes everyday. The data available are Vehicle to the market, Opening Km and Closing Km. A vehicle may ply to two different markets on the same day or not ply at all. I want to find the opening and closing km of each vehicle every day. The sample excel file is attached.

    The formula should find all instances of the name of the vehicle in the market table and get the minimum opening km and the maximum closing km to get the overall opening and closing km.
    vehicle.jpg

    I have tried to find a solution but couldn't find any. Please do explain your solution in brief

    Thanks

    Edison
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Get MIN and MAX values from column B where corresponding column A matches criteria

    I'm not 100% sure if this covers all the bases, but it does work for your sample.

    C26, copied across and down:
    =IFERROR(AGGREGATE(15,6,C$11:C$16/(C$4:C$9=$B26),1),B31)

    C31, copid across and down:
    =IFERROR(AGGREGATE(14,6,C$18:C$23/(C$4:C$9=$B31),1),B31)

    Restore the missing space in E8.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-08-2022
    Location
    Coimbatore, India
    MS-Off Ver
    2010
    Posts
    2

    Re: Get MIN and MAX values from column B where corresponding column A matches criteria

    Hi Glenn
    I knew this could be done somehow using AGGREGATE but couldn't figure out how. Analyzing your formula has given me a clear understanding of how AGGREGATE works. Thanks a lot.
    Edison

+ 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. Replies: 4
    Last Post: 04-02-2019, 12:22 PM
  2. [SOLVED] Lookup two column for matches and return largest values from third column
    By abulkhairi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-13-2017, 01:54 AM
  3. [SOLVED] Count one column when a 2nd column matches values in a 3rd column.
    By pbobbitt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-04-2017, 03:18 AM
  4. Replies: 4
    Last Post: 10-30-2015, 07:59 AM
  5. [SOLVED] Return value from column A where column B value matches a criteria
    By Canther in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-06-2013, 06:23 PM
  6. Hard? SUM the values of a column that matches a flag in an adjacent column
    By Nokao in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-07-2012, 11:29 AM
  7. Return Value from 2nd column when value in 1st column matches given criteria
    By Russell_K in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2008, 12:07 AM

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