Lookup starting at bottom of page with criteria

[jwbrouse01]

I am trying something with an automatically-updating training matrix.

Sheet1 should be the training matrix, with employee names in column A, starting in row 2 and operation # in Row 1, Columns B-....

Sheet2 is the history of reviews that I have done for each employee. I have the following data:

Column A: Date
Column B: Employee name
Column C: operation #
Column D: Training Level

Each time I review an employee, I add the new training level assessment to the bottom of the list.

Back on Sheet1, I want to put a formula into each cell in my matrix that will automatically pull the level associated with each employee for each operation based on the last time they were evaluated. So basically, a lookup starting at the bottom of the page with criteria for employee name and operation #.

I know that I can do this with a UDF, but is there any way to achieve this with normal formulas that won't require a macro-enabled workbook?

Any assistance would be greatly appreciated!

 

[Alex Blakenburg]

Do you just mean something like this ?

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Employee name	operation #	Training Level
2	10/01/2023	John	a	1			Input	Name	John
3	11/01/2023	Steve	b	2				Operation	a
4	10/02/2023	Don	c	3
5	11/02/2023	John	a	4			Output	Result	4
6	10/03/2023	Steve	b	5
7	11/03/2023	Don	c	6
Sheet1

Cell Formulas
Range	Formula
I5	I5	=XLOOKUP(1,($B$2:$B$7=$I$2)*($C$2:$C$7=$I$3),$D$2:$D$7,"",0,-1)

 

[jwbrouse01]

Works like a dream! Thanks, I appreciate you!

 

[Alex Blakenburg]

You're welcome. Glad I could help.