Index and Match - Return right from left with matching values

[AntNMC1]

Hello,

I have a table which includes 21 columns, and roughly 275 rows. Column A list employee ID's, and columns B through V list on-line trainings that each employee must complete (1-20 training tests). The rows of data displays dates for each training that was completed.

I would like to return two data elements for each employee, the latest training they completed (column header), and the date it was completed on. Thus, I attempted to write two formulas in Columns W and X. In column W, I wrote a MAX Formula to return the latest, and in column X, I wrote an index and match formula, to return the corresponding header.

The issue I am running into, is that if an employee has completed multiple trainings on the same date, the index and match formula is retuning the first column header (because of multiple matching dates). My question is: how do I force the index and match formula to return the headers from right to left? NOTE: I am not able to reorder the data table, as other people will need to review it once I am finished.

Or, should I use a different set of formulas?

Best Regards,
Ant

 

[iliace]

This sounds like a situation where you need to separate each employee ID, and each training that the employee takes. This is what you might call database normalization.

Your employee table needs to be the 275~ rows as you suggested. There needs to be a separate table. For every employee who took a training, there should be a record of Employee ID, Training ID/name, and date the training was taken by that employee.

Once that is set up, now you can query each employee ID, from the first table, and then the MAX value from the trainings table, based on the employee ID.

 

[Ron Coderre]

With your described structure in A1:V4
This regular formula returns the largest date in Row_2

W2: =MAX(B2:V2)

And this regular formula returns the right-most class taken on that date

x2: =INDEX($A$1:$V$1,MAX(INDEX(COLUMN($B$1:$V$1)*(B2:V2=W2),0)))

Copy those formulas down as far as you need.

Is that something you can work with?