Lookup reference

cmgish · Feb 15, 2021

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?

Toadstool · Feb 15, 2021

Hi CMGish,

Can you use XL2BB or copy and paste as text so we don't have to retype your data.

cmgish · Feb 23, 2021

Toadstool said:
Hi CMGish,

Can you use XL2BB or copy and paste as text so we don't have to retype your data.

I would be happy to do so, but this is on a company computer and it won't allow me to download anything. Not even an Excel plugin

Toadstool · Feb 23, 2021

cmgish said:
I would be happy to do so, but this is on a company computer and it won't allow me to download anything. Not even an Excel plugin

Then just copy and paste as text so we don't have to re-type your data.

cmgish · Feb 23, 2021

	Patients per hour (avg)	% of Bonus	Payout
Level	Year 1	Year 2	Year 3+
1	2.05	2.30	2.55	100.0%	5,000.00
2	1.93	2.18	2.43	90.0%	4,500.00
3	1.80	2.05	2.30	75.0%	3,750.00
4	1.68	1.93	2.18	60.0%	3,000.00
5	1.55	1.80	2.05	50.0%	2,500.00
6	1.43	1.68	1.93	30.0%	1,500.00
7	1.30	1.55	1.80	20.0%	1,000.00
8	1.18	1.43	1.68	10.0%	500.00

cmgish · Feb 23, 2021

This is the formula I am using now (H7 is the cell that contains the value I need to "match", 2.23):

=IF(H7>=P6,1,IF(H7>=P7,2,IF(H7>=P8,3,IF(H7>=P9,4,IF(H7>=P10,5,IF(H7>=P11,6,IF(H7>=P12,7,IF(H7>=P13,8,"DNQ"))))))))

This formula works great as long as you know the tenure of the employee is "Year 3+". I am using this as a template for several different employees, so this formula will not work on an employee that has a tenure of "Year 1" or "Year 2" because those benchmarks are listed in columns N & O.

Fluff · Feb 23, 2021

How about

+Fluff 1.xlsm

G

H

I

J

K

L

M

N

O

P

Q

R

4

Patients per hour (avg)

% of Bonus

Payout

5

Level

Year 1

Year 2

Year 3+

6

Year

Payout

1

2.05

2.3

2.55

100.00%

5,000.00

7

3

2.23

4

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

Master

Cell Formulas
Range	Formula
I7	I7	=INDEX(M6:M13,XMATCH(H7,INDEX(N6:P13,,G7),-1))

cmgish · Feb 23, 2021

That might work great except that I am using Excel 2016 and I don't think XMATCH is available for this version. I guess I should update my profile, huh? Sorry about that.

Fluff · Feb 23, 2021

Ok, how about

+Fluff 1.xlsm

G

H

I

J

K

L

M

N

O

P

Q

R

4

Patients per hour (avg)

% of Bonus

Payout

5

Level

Year 1

Year 2

Year 3+

6

Year

Payout

1

2.05

2.3

2.55

100.00%

5,000.00

7

3

2.23

4

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

Master

Cell Formulas
Range	Formula
I7	I7	=INDEX(M6:M13,MATCH(TRUE,(H7>=INDEX(N6:P13,,G7)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Toadstool · Feb 23, 2021

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

Lookup reference

cmgish

New Member

Excel Facts

Toadstool

Well-known Member

cmgish

New Member

Toadstool

Well-known Member

cmgish

New Member

cmgish

New Member

Fluff

MrExcel MVP, Moderator

cmgish

New Member

Fluff

MrExcel MVP, Moderator

Toadstool

Well-known Member

Similar threads

Forum statistics

Share this page

Lookup reference

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock