# Lookup reference

#### cmgish

##### New Member
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.

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
Hi CMGish,

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

Replies
1
Views
270
Replies
9
Views
680
Replies
0
Views
76
Replies
5
Views
145
Replies
1
Views
324

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.

### Which adblocker are you using?

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

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