how to handle duplicate data from the source sheet when using vlookup?

Benjo09

Hi,

I have a work schedule sheet (source/reference data) and shift schedule of employees is being logged there. Employees shift schedule can be night shift and day shift per employee. Now, I have another sheet (Control log sheet) where I am looking up the work schedule of each employee in a daily basis. When I am using the normal look up it only returns me the first shift scheduled logged (ex. nigh shift). I want the newest schedule to loook up.

May I know what modification with the normal vlookup formula so that it returns the newest or recent shift schedule?

Or Do we have other formula we can use for this? (I have searched the threads but I have read about using =join(collect( , but the problem is I don't how it works.)

Kindly help me to solve this. thank you.

Paul Newcome

They should be dates entered into a date type column on the source sheet, and the formula on the metrics sheet should also go into a date type column.

Paul Newcome

Happy to help. 👍️

Paul Newcome

I would suggest something like this...

Insert a new column on the sheet and use the below to pull the "max date" for that employee.

=MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Employee Column}, Employee@row))

Then in place of your VLOOKUP you can use...

=INDEX(COLLECT({Other Sheet Status Column}, {Other Sheet Employee Column}, Employee@row, {Other Sheet Date Column}, [Date Helper Column]@row), 1)

Benjo09

Hi Paul,

Thank you very much for the help .

I have created max date column and entered the formula but the max(collect( formula gave me a 0 result. I have tried to change the other sheet date column to DATE data type and gave me #invalid column value error.

Anything I have done wrong in this formula? =MAX(COLLECT({reference Range 2}, {reference Range 3}, Employee@row)

Paul Newcome

The formula should be right. It has to do with the source data.

How are those dates populated?

Benjo09

Paul,

Since the sheets are confidential, I made a shorter version.

I just typed in the Dates (text format).

And debugging the error, I also tried to put it in a date type format in column properties and select the dates.

Paul Newcome

They should be dates entered into a date type column on the source sheet, and the formula on the metrics sheet should also go into a date type column.

Benjo09

Hi Paul,

It works! Thank you very much! I deleted both date columns and set it to date format. =)

Paul Newcome

Happy to help. 👍️