VLookup Issues

Jeremy Norman

Problem: While I am able to input the VLOOKUP formula correctly, I having an issue with it returning correct data.

I am using a form to track attendance. The same form inputs dates from different lab types but creates a row with each entry. Each different lab date creates a new row as designed. I am trying to move the data from the form entry sheet to a master tracker for each employee via the VLOOKUP formula. Here is the VLookup formula (Each Column has its own formula) in the master tracker

=VLOOKUP([Employee ID Number]@row, {Master List - Lab/Specialty Class Tracker Range 1}, 17, false) - This is correct when the ALL data is only in one row. When I have multiple rows for the same employee the formula will not return any data but a blank (not #NO MATCH).

Any thoughts as to how to return data from multiple rows for the same employee into one master tracker sheet?

Form Entry Data screen shot -

Master Tracker screen shot -

Thanks for your help.

Paul Newcome

You will need to move over to an INDEX/COLLECT to filter out the blank rows.

=IFERROR(INDEX(COLLECT({Date Column}, {Date Column}, @cell <> "", {Employee ID}, [Employee ID Number]@row), 1), "")

Jeremy Norman

Thanks for your help. I truely appreciate your time.

Are there any good lessons for the Index and Collect formulas? I am not that savy with these two.

Paul Newcome

Basically the INDEX function allows you to pull in data based on a row number and optional column number.

The COLLECT function collects data based on range/criteria sets (similar to a COUNTIFS).

So we use the COLLECT function to pull in all of the dates that match the range/criteria sets (in this case not blank and name match). Then use the INDEX function to output the first row of that collected data.

The IFERROR statement is there in case there is no match. It will output a blank.

Jeremy Norman

Thanks for your help. I appreciate your time.

Paul Newcome

Happy to help. 👍️