+ Reply to Thread
Results 1 to 3 of 3

Need something like INDEX MATCH with MIN value returned

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    51

    Need something like INDEX MATCH with MIN value returned

    Using Excel 2016
    This will be simplified data
    Table 1 as unique values in column A
    - A1 is the header "ID"
    - cell A2 is where the data starts with values like 10001, 10002, 10003 , 10004, 10005 (not exactly this, but random numbers, all unique) - and NOT all ID's here are in Table 2 so I need to handle that as well

    Table 2 can have multiple records for the same ID, laid out like this:
    Column A (listed with headers)
    ID DateTime Hour
    10001 3/1/2021 7:56am 7
    10001 3/1/2021 8:02am 9
    10002 3/1/2021 3:32pm 15
    10003 3/1/2021 11:25am 11
    10003 3/2/2021 9:05am 9

    The "Hour" column is just the built-in excel formula =Hour([@DateTime]) and simply gives me the whole army hour value from the DateTime column
    Note: I do not care about 10003 having 2 different dates, I'm only concerned about the Hour value itself and I'm wanting to retrieve the Minimum value.

    On Table 1, I need to find the MINIMUM Hour value and return it next to the ID in Table 1. So 10001 would return 7, 10002 would return 15, and 10003 would return 9, and 10004 and 10005 wouldn't return anything so I would want to display "N/A" or something like that.

    I've tried everything I know of with VLOOKUP and INDEX MATCH, but I can't get anything to work. Not saying I need to use VLOOKUP or INDEX MATCH, but those are things I know how to use.

    Any advice would be greatly appreciated. I hope this is simple and maybe there's an easier way to do this vs VOOKUP or INDEX MATCH?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Need something like INDEX MATCH with MIN value returned

    Assum table 2 is in G2:I6
    In B2:
    Two option:
    1) Using column I: converting hour
    =AGGREGATE(15,6,$I$2:$I$6/($G$2:$G$6=A2),1)
    2) Use column G and H only:
    =INT(AGGREGATE(15,6,MOD($H$2:$H$6,1)/($G$2:$G$6=A2),1)*24)
    Attached Images Attached Images
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Need something like INDEX MATCH with MIN value returned

    Thank you. Both options worked perfectly. I only had to add a bit of logic for the #NUM values, but all good now. Very much appreciated!!!!

+ 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] Shared Value not being returned using Index/Match and Mode Formula
    By ShakJames in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-10-2017, 07:14 PM
  2. Comparing the multiple values returned against index/match
    By agraham in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-18-2015, 07:18 AM
  3. Need 0 Value Returned for NA# on Index Match Formula
    By dharrier in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2015, 08:21 PM
  4. [SOLVED] Incorrect column number returned in INDEX, MATCH for nth instance
    By BuZZarD73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2014, 11:00 AM
  5. Index Match returning values when none should be returned
    By Khaos1208 in forum Excel General
    Replies: 6
    Last Post: 04-27-2014, 04:48 PM
  6. [SOLVED] Lookup & Index Match - Errors Returned
    By shudder in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-23-2013, 05:56 AM
  7. Duplicate values returned using Index Match Lookup with Small()
    By jacob@thepenpoint in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2013, 10:48 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