# Need something like INDEX MATCH with MIN value returned

1. ## 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:
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. ## 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)

3. ## 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!!!!

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