How do I do a quick lookup?

It would be helpful if you could provide a sample workbook with the data shown in the two ".png" images you have posted.

The second image, that result in a "Performance Rating", is most likely able to be achieved using one/more/multiple examples of the following in-cell formulae:

MATCH
INDEX
HLOOKUP

The application of these, though, is difficult to determine without seeing the context in which you are referring to the matrix within your own workbook.

Please see attached. I have it linked to a Word doc template that wouldn't work if I sent over to you. It just pulls all the information from this document into a letter format.

Basically, the manager would be able to choose the occurrences from the drop by menu then have the rating auto-generate at completion. This way, nobody can accidentally give an incorrect rating.
SAMPLE.xlsx

Thank you for the file.

Please could you explain how the total (3.05) in cell [D33] should be used to derive a "Performance Rating"?

I presume we will need some classification of the "Occurrence" values in the range [B25:B34] (probably as an additional column within worksheet [Sheet3]) to determine whether a specific value is a "Late/EQ" or an "Absence".  


The totals of each classification will then influence which column/row should be extracted from the "Performance Rating" matrix (that will also need to be defined within the workbook, rather than as an image pasted onto the worksheet).

The D33 was a point system I tried to do before realizing that the number of lates/EQ doesn't equate to the number of absences... it screwed up my point system. )=

OK... so how do the individual entries within [B25:B34] relate to the "Performance Rating" grid?

Without the correlation between the two, I cannot advise further.

Oh, I'm sorry! Those are the actual occurrences. They are listed in drop down menus to make it easier for managers to select (and to make sure everything is uniformed).

Occurrences that are on the vertical axis:
Tardy (2 tardies = 1 Late)
Late
Early Quit

Occurrences that are on the horizontal axis:
Notified Absence
Notified Absence - Sick
Failure to Report

So if someone has 2 tardies, 1 Late, and 1 notified absence then their attendance rating is Satisfactory. If they have 1 Late then it is Excellent.

I will let the others who are already commenting on the technical details continue, but as a side note - depending on your specific needs and the way your organization operates, you may want to forget about the Word template and recreate the needed report  in Excel.  That way you would not need to keep the two applications functioning together all the time.  You could have Excel (via a macro) print a PDF and get your non-editable document that way.

That would be wonderful! How do you suggest I start with that? I'm not too familiar with macros so I didn't want to mess with that. Is there a code somewhere that I can just modify?

Thank you so much!

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.