Lookup starting at bottom of page with criteria

jwbrouse01

New Member
Joined
Jun 2, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Do you just mean something like this ?

Book1
ABCDEFGHI
1DateEmployee nameoperation #Training Level
210/01/2023Johna1InputNameJohn
311/01/2023Steveb2Operationa
410/02/2023Donc3
511/02/2023Johna4OutputResult4
610/03/2023Steveb5
711/03/2023Donc6
Sheet1
Cell Formulas
RangeFormula
I5I5=XLOOKUP(1,($B$2:$B$7=$I$2)*($C$2:$C$7=$I$3),$D$2:$D$7,"",0,-1)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top