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!
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!