Datamesh or VLOOKUP using two criteria to be met

Courtney Holdt

Is it possible to use a vlookup formula or Datamesh to transfer data when TWO criteria are met.

I have a parent row which is the name of the task and then children row below showing when each employee completed the task (so three columns Task, Employee, Date). I'm looking for a way to transfer the date each individually employee completed each task into a new task tracker for the new year. The order of employees or tasks could change in the new tracking sheet so that is why I am trying to do some sort of data connection formula.

Right now I have a datamesh set up to transfer over the date each task was completed but it only pulls the date over for the first employee in the list.

Genevieve P.

Hi Courtney,

In your tracking sheet, do you already have the course name and employee name in two different columns, and you just need to bring in the date?

If so, how about a JOIN(COLLECT formula, even though you don't have values to Join (you'll just be returning one cell's value, the date, assuming that only one value meets all the criteria).

The way JOIN(COLLECT works is that you first list the range that has the value you want returned, and then list each range and criteria in the current, second sheet afterwards.

For example:

=JOIN(COLLECT({Other Sheet Date Column}, {Other Sheet Task Column}, [Task Column]@row, {Other Sheet Employee Column}, [Employee Column]@row))

The ranges in {these} are cross-sheet references in your first sheet. The values in [these] represent the column in your data collection sheet, where the formula is being written. Keep in mind that this formula would need to be created in a Date type of column if you are looking to return a date.

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References

Let me know if you have any questions as you build this out! (Screen captures of both sheets without any sensitive data would be helpful).

Thanks,

Genevieve

Courtney Holdt

Thanks for the tip Genevieve! I never would have thought about a JOIN formula. I ended up getting around this issue yesterday afternoon by creating a "lookup" column for each entry which combined the two criteria I needed but I will definitely have to give your way a try as well.

Genevieve P.

No problem at all!

Helper columns are also great solutions - I'm glad you were able to sort something out.