Lookup reference

[cmgish]

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]

Hi CMGish,

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

CMGish.xlsx
	M	N	O	P	Q	R	S	T	U
4		Patients per hour (avg)			% of Bonus	Payout		Tenure:	2
5	Level	Year 1	Year 2	Year 3+				Employee Value:	2.23
6	1	2.05	2.3	2.55	100.00%	5,000.00		Result:	2
7	2	1.93	2.18	2.43	90.00%	4,500.00
8	3	1.8	2.05	2.3	75.00%	3,750.00
9	4	1.68	1.93	2.18	60.00%	3,000.00
10	5	1.55	1.8	2.05	50.00%	2,500.00
11	6	1.43	1.68	1.93	30.00%	1,500.00
12	7	1.3	1.55	1.8	20.00%	1,000.00
13	8	1.18	1.43	1.68	10.00%	500
14
Sheet1

Cell Formulas
Range	Formula
U6	U6	=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
Cell	Allow	Criteria
U4	Whole number	between 1 and 99
M6:M13	Whole number	between 1 and 99

Wow! Works like a charm! Thanks so much!