Need another option than INDEX MATCH

jdejongh

I am trying to pull data from 2 columns in one sheet into another (a name and a date) which worked great until there was another instance, which know I find out that INDEX MATCH can only see the first instance. I am looking for other options. Below is a quick example:

Formulas being used are in bold and underneath are the explanations of where they are pulling from. The first once is in the Evaluations completed by column and the second is in the Date of last training column

=INDEX({Evaluators name}, MATCH(Technician@row, {Tech name}, 0)) - this formula is in the Evaluation completed by column on the Follow-up training and forecast sheet.

INDEX{{Evaluators name} pulls from Technician Name on the Ongoing Technician Evaluation - sheet

MATCH(Technician@row pulls from Technician on the Follow-up training and forecast

{Tech name} pulls from Date on the Ongoing Technician Evaluation - sheet

=INDEX({Date}, MATCH(Technician@row, {Tech name}, 0)) this formula is in the Date of last training column on the Follow-up training and forecast sheet.

INDEX({Date} is pulling from Date on the Ongoing Technician Evaluation - sheet

MATCH(Technician@row, is pulling from the Technician on the Follow-up and forecast sheet

{Tech name} is pulling from Technician on the Ongoing Technician Evaluation - sheet

My issues is we complete several evaluations on the technicians through out the year. Every time one is completed it populates on the Ongoing Technician Evaluation sheet. I created a new sheet Follow-up training and forecast to list all the technicians their hire dates and last dates of training. Once we started getting more data I realized that INDEX MATCH was not over writing the second or even third occurrence. I know that was lengthy but I want to provide the data for a clear understanding. I am looking for a formula that will constantly overwrite the name and date cells in the corresponding columns. Thank you in advance for your help.

Matt Lynn-PCG

@jdejongh If I understand your issue right, you have multiple entries that meet the criteria and the formula is pulling the first match, while you are needing it to pull the latest one or one with the most recent/biggest date as a match?

The best way to solve this is to use a max(collect()) formula to pull the latest date per name. It would probably be something like this:
=max(collect({date},{Tech Name},Technician@row))

You could put that in an if or iferror for cases where there isn't a match etc.

jdejongh

That worked perfectly for the date. However the evaluators name using a modified version of the formula is returning a 0. Here is the modification I made looking for the tech name

=MAX(COLLECT({Evaluators name}, {Tech name}, Technician@row))