[Solved] First and last value

[Shaima]

Good Day!
i want first and last value(time) of a given date in two different columns
im able to get the first value through vlookup with a helper column, but have trouble with the last value

				Result
Helper Column	Name	Date	Time in/Out	First Value	Last Value
Young|45068	Young	22/05/2023	8.24.01	8.24.01	17.35.59
Young|45068	Young	22/05/2023	17.35.59
Young|45069	Young	23/05/2023	8.20.47	8.20.47	17.24.43
Young|45069	Young	23/05/2023	17.18.13
Young|45069	Young	23/05/2023	17.24.43

i have attached excel file, please provide solution for the above query.

 

[Yousuf Shovon]

Good Day!
i want first and last value(time) of a given date in two different columns
im able to get the first value through vlookup with a helper column, but have trouble with the last value

				Result
Helper Column	Name	Date	Time in/Out	First Value	Last Value
Young|45068	Young	22/05/2023	8.24.01	8.24.01	17.35.59
Young|45068	Young	22/05/2023	17.35.59
Young|45069	Young	23/05/2023	8.20.47	8.20.47	17.24.43
Young|45069	Young	23/05/2023	17.18.13
Young|45069	Young	23/05/2023	17.24.43

i have attached excel file, please provide solution for the above query.

Dear Shaima,

Thank you for your query. I understand you wish to extract the first and last matches from Column D based on multiple criteria. I see you used VLOOKUP to get the first value with a helper column. But you were not able to obtain the last matching value. This is so common because VLOOKUP only returns the first matching value. Fortunately, we can get the last matching value using the LOOKUP value.

Here are the desired formulas:
To get the entry time:
=VLOOKUP(A4, $A$4:$D$91, 4, FALSE)
For exit time:
=LOOKUP(2, 1/(($A$4:$A$91=A4)*($D$4:$D$91<>"")), $D$4:$D$91)

Thus, you will obtain the desired outputs. I have attached the Excel file as well for a better understanding.

Best Regards,
Yousuf Shovon

 

[Shaima]

Thank you so much for the solution.