Returning oldest date and associated cell info from a range dependent on contents of another cell

[rrands1]

For the following data:

I am trying to populate a cell with the oldest "Date Out" (Column J) that does not have a corresponding "Date In" (Column K). The intent is to figure out the oldest date out that has not been returned (Date In), and show the "Assigned to" name as a result. I have the following, which works to find the oldest "Date Out" and show the contents of that row's "Assigned To" cell, however I can't figure out how to only check on ones that don't have anything in the "Date In" cell. Here is my formula so far:
=INDEX(I2:I5,MATCH(MIN(K2:K5),K2:K5,0))

So this would return 7/4/2021, however that has a value in column K (meaning it has been returned), so the value I really am after is 8/28/21 because it's the oldest date that has not been returned. Does anyone know how to make that work?


BONUS Q: This finds the oldest date, but I ideally would like the 3 oldest - is there a way to modify the MIN function (or use a different one) to do like MIN -1, MIN -2?


Thank you so much - I appreciate any help!

 

[JoeMo]

If your version of Excel recognizes the FILTER function, this seems to work:

Book1
	I	J	K	L	M
1	Assigned To	Date Out	Date in		Oldest Assigned To w/o Date In
2	First Last	7/4/2021	7/20/2021		More Name
3	More Name	8/28/2021
4	Another Name	9/4/2021
Sheet6

Cell Formulas
Range	Formula
M2	M2	=INDEX(FILTER(I2:I4,K2:K4=""),MATCH(MIN(J2:J4),J2:J4,0))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I ideally would like the 3 oldest -

As with JoeMo's suggestion IF you have MSoft 365 ..

21 10 20.xlsm
	I	J	K	L	M	N	O
1	Assigned To	Date Out	Date in			Oldest how many:	3
2	Name 1	17/04/2021	4/07/2021
3	Name 2	13/04/2021	25/04/2021			Assigned To	Date Out
4	Name 3	3/01/2021				Name 3	3/01/2021
5	Name 4	28/07/2021	20/09/2021			Name 8	5/02/2021
6	Name 5	11/05/2021				Name 5	11/05/2021
7	Name 6	12/10/2021	12/10/2021
8	Name 7	24/06/2021
9	Name 8	5/02/2021
10	Name 9	31/08/2021	10/10/2021
Oldest Out

Cell Formulas
Range	Formula
N4:O6	N4	=INDEX(SORTBY(FILTER(I2:J10,K2:K10=""),FILTER(J2:J10,K2:K10="")),SEQUENCE(O1),{1,2})
Dynamic array formulas.

 

[Peter_SSs]

If you do not have 365, or just need it to work in older versions as well, try these

21 10 20.xlsm
	I	J	K	L	M	N	O
1	Assigned To	Date Out	Date in			Oldest how many:	3
2	Name 1	17/04/2021	4/07/2021
3	Name 2	13/04/2021	25/04/2021			Assigned To	Date Out
4	Name 3	3/01/2021				Name 3	3/01/2021
5	Name 4	28/07/2021	20/09/2021			Name 8	5/02/2021
6	Name 5	11/05/2021				Name 5	11/05/2021
7	Name 6	12/10/2021	12/10/2021
8	Name 7	24/06/2021
9	Name 8	5/02/2021
10	Name 9	31/08/2021	10/10/2021
Oldest Out (2)

Cell Formulas
Range	Formula
N4:N10	N4	=IF(O4="","",INDEX(I:I,AGGREGATE(15,6,ROW(I$2:I$10)/((J$2:J$10=O4)*(K$2:K$10="")),COUNTIF(O$4:O4,O4))))
O4:O10	O4	=IF(ROWS(O$4:O4)>O$1,"",AGGREGATE(15,6,J$2:J$10/(K$2:K$10=0),ROWS(O$4:O4)))

 

[alansidman]

Another Option is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Out", type date}, {"Date in", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date in] = null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date Out", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] <> 4)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Date in", "Index"})
in
    #"Removed Columns"

Book6
	A	B	C	D	E	F
1	Assigned To	Date Out	Date in		Assigned To	Date Out
2	Name 1	4/17/2021	7/4/2021		Name 3	1/3/2021
3	Name 2	4/13/2021	4/25/2021		Name 8	2/5/2021
4	Name 3	1/3/2021			Name 5	5/11/2021
5	Name 4	7/28/2021	9/20/2021
6	Name 5	5/11/2021
7	Name 6	10/12/2021	10/12/2021
8	Name 7	6/24/2021
9	Name 8	2/5/2021
10	Name 9	8/31/2021	10/10/2021
Sheet1

 

[Peter_SSs]

each ([Index] <> 4)),

Shouldn't that be this?

each ([Index] < 4)),

 

[alansidman]

@Peter_SSs
You are absolutely correct. In the case presented as an example there was only 4 records and I hastily just removed the 4th record. Thanks for posting that correction. You have the eyes of an eagle.

Alan

 

[Peter_SSs]

In the case presented as an example there was only 4 records

Of course, that was my sample data, not the OP's.

I am not all that familiar with power Query. So if the number of records required was variable and indicated by a cell value like O1 in my sample data, can PQ easily adapt to any change in that cell value (eg changing that value from 3 to 2)? If so, how does that work?

 

[alansidman]

Simple answer is yes, but it will require a parameter query. This involves a separate table with that value which is then input into the filter instead of hard coding the 3 or some other value.

 

[Peter_SSs]

Thanks