VLOOKUP for a date within a referenced row of matching username

vwphoto

Hi,

I am building a sheet that references a larger sheet of user activity. There are two sheets are play. Sheet A is the main database and Sheet B is a culmination of data, soon to shoot off into reports of its own.

Sheet A has two columns which include parent and child rows.

Sheet A Column 1 is named originalUsernames. It contains usernames manually inserted. Each unique username is a parent row. The child rows repeat the username featured in the parent rows' originalUsernames column.

Sheet A Column 2 is called "Job End Date" and contains dates. The child rows within this column contain different dates to represent different "Job End Dates" for the same user presented in the child rows.

This is where it gets tricky.

In Sheet B Column 1, I am pushing data from an alternate source via API with usernames that match the originalUsernames found in Sheet A Column 1.

Sheet B Column 1 is called userName

To match up my originalUsernames from Sheet A Column 1 with Sheet B Column 1, I use the column formula in Sheet B Column 2 below:

=IFERROR(VLOOKUP(userName@row, {originaluserName}, 1, false), "No username")

This part works great, and usernames from each sheet line up on their respective rows, showcasing the error "No username" as intended if no username is present in Sheet A Column 1.

Now, in Sheet B Column 3 I need to pull in the Job End Date the most in the future from Sheet A Column 2, all while matching up with the appropriate rows present.

I tried referencing the Job End Date column in Sheet A Column 2, and building a similar VLOOKUP, but it is just giving me the error of "No dates", but there are dates present, and many.

=IFERROR(VLOOKUP(userName@row, {JobEndDate}, 1, false), "No dates")

I am also missing the part for searching for the most future date within the child rows of each username.

Thank you for your insight on next steps.

Paul Newcome

You need a MAX/COLLECT combo instead of those IFs.

=MAX(COLLECT({Date Column}, {User Column}, @cell = userName@row))

vwphoto

I want to also skip any non-dates or blank values listed in the job end date column of Sheet A

=IFERROR(MAX(IF(ISDATE({JobEndDate}), IF({originaluserName} = userName@row, {JobEndDate}, 0))), "No dates")

I think its something in this range but getting a #INCORRECT ARGUMENT SET - would appreciate any feedback

---

I also tried:

=IFERROR(MAX(COLLECT({JobEndDate}, {AD userName}, userName@row)), "No dates")

I cleared all non-dates and made sure it was either dates or blank fields in the JobEndDate column, but received a #INVALID COLUMN VALUE error for one user, and "0" for other users

If a user with a parent row and child has all dates filled in, then I receive a #INVALID COLUMN VALUE. If a date is missing in the parent row cell of Job End Date, then it results in "0".

Do I need a separate column in Sheet A that pulls the oldest date listed within a user's child rows? And then have my separate Sheet B reference this cell for each user?

vwphoto

Update: I had to change the Column type to "date" and that resolved the issue of INVALID COLUMN VALUE

I now see the date I wanted in relation to the user.

vwphoto

The only issue that remains now is that it is not picking the most future set date "latest date" as per MAX, it is just pulling the date within the parent row, even if there is a more future set date / later date in the child rows.

Thoughts?

Paul Newcome

You need a MAX/COLLECT combo instead of those IFs.

=MAX(COLLECT({Date Column}, {User Column}, @cell = userName@row))

vwphoto

Thanks @Paul Newcome - The formula works and pulls the latest date, although only if every child row in Sheet A lists the userName. Is there anyway to have it detect a value in the parent row only, and then scan through all child rows within that parent?

Or is that userName value present in every child row the only way for the formula to understand the request of pulling every related job end date for the child rows of said user?

Paul Newcome

It will have to be present in every row. If you are worried about keeping the sheet clean, you can use a helper column to pull the user name into every row based on the parent cell, hide the column, then reference this helper column in your MAX/COLLECT.

vwphoto

Yes, the concern is more about reducing data entry. If there is 5 to 10 child rows per parent/user, we would want the child rows to auto-popuplate with the parent rows username value.

So you would create a new column with a column formula that pulls the username based on the parent cell, and then this would fill the child rows?

Could you provide an example please @Paul Newcome ?

Paul Newcome

The formula in the helper column would be:

=PARENT(userName@row)