Lookup reference

cmgish

New Member
Joined
Feb 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
So I have a table that contains 3 different columns of values. I need to write a formula based on a value that will not match exactly to what is in the table, and also based on the tenure of the employee.

1613398346064.png


Based on calculated data, an employee's calculated value is 2.23 and the employee's tenure is 'Year 3+' (i.e., been with the company longer than 3 years). I've tried INDEX/MATCH, but I can't seem to get it to work right. I would like the result of the formula to be the 'Level' (column M above). In this case, the result of the formula should be '4' since 2.23 is greater than 2.18 and less than 2.3 in column P. It seems like this should be simpler than what I'm making it. Any suggestions?
 

cmgish

New Member
Joined
Feb 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi CMGish,

I see you already have an answer but here's my version:

CMGish.xlsx
MNOPQRSTU
4Patients per hour (avg)% of BonusPayoutTenure:2
5LevelYear 1Year 2Year 3+Employee Value:2.23
612.052.32.55100.00%5,000.00Result:2
721.932.182.4390.00%4,500.00
831.82.052.375.00%3,750.00
941.681.932.1860.00%3,000.00
1051.551.82.0550.00%2,500.00
1161.431.681.9330.00%1,500.00
1271.31.551.820.00%1,000.00
1381.181.431.6810.00%500
14
Sheet1
Cell Formulas
RangeFormula
U6U6=IFERROR(INDEX($M$6:$M$13,AGGREGATE(15,6,ROW($N$6:$N$13)-ROW($N$5)/((OFFSET($N$6,0,MIN(3,$U$4)-1,8)<=$U$5)),1)),"DNQ")
Cells with Data Validation
CellAllowCriteria
U4Whole numberbetween 1 and 99
M6:M13Whole numberbetween 1 and 99
Wow! Works like a charm! Thanks so much!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,148,191
Messages
5,745,251
Members
423,939
Latest member
Learnexcel1

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
Top