Multiple Criteria Accrual Vlookup with Greater Than or Equal To

[cyndy01]

Hi Everyone,

I need a bit of help. I have a sheet that looks up accrual rates for employees based on length of service and is supposed to come up with the right accrual rate. It doesn't. I've tried INDEX MATCH, SUMPRODUCT and Vlookup and keep falling flat.

Basically it's like this

A B C D
1 Class Years YearsEnd Rate
2 Executive 0 1 8
3 Executive 5 10 9
4 Executive 10 15 10
5 Manager 0 5 7
6 Manager 5 10 8
7 Manager 10 15 9
8 Associate 0 5 6
9 Associate 5 10 7
10 Associate 10 15 8


I'm supposed to come up with a way of finding the classification (I1) matching that with a year that if greater than or equal to the years in length of service(j1) and then getting the accrual rate in column D from that. For example if Associate (I1) and 3.5 years (j1) then k2 has the formula that gives me 6 (falls between 0 and 5 years).

Vlookup won't work because there is no way to get the greater than or equal to. I've gone back and sorted by column A to get everything in ascending order, but frankly I'm at a loss. Can anyone point me in the right direction? I've tried getting rid of the second column completely, but it's still a matter of not getting greater than or equal to. Thanks in advance.

 

[lenze]

Create a Table like this

  A         B  C  D  
1           0  5  10 
2 Executive 8  9  10 
3 Manager   7  8  9  
4 Associate 6  7  8  
5                    
6 Associate          
7 3.5                
8              6     
Sheet2
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA 
C8    =INDEX(B2:D4,MATCH(A6,A2:A4,0),MATCH(A7,B1:D1,1))
[Table-It] version 09 by Erik Van Geit

Change cells as required
lenze

 

[cyndy01]

Wow. I really don't understand that table at all. Either that or it didn't show up right.

A B C D
1 0 5 10
2 Executive 8 9 10
3 Manager 7 8 9
4 Associate 6 7 8
5
6 Associate
7 3.5
8 6
Sheet2
[Table-It] version 09 by Erik Van Geit

This is the way it showed up for me. I can't grasp the logic behind it.

 

[lenze]

It's called a two dimentional lookup table. In the example
Cells B1:D1 contain the threshold numbers for years
0 5 10
Cells A2:A4 contain the listing of the classes
Excecutive
Manager
Associate

Cells B2:D4 contain the corresponding Rate from (at the intersection)
A6 contains the Class you want to lookup
A7 contains the Years
The Formula in C8

=INDEX(B2:D4,MATCH(A6,A2:A4,0),MATCH(A7,B1:D1,1))

says look at the Table (B2:D4) and return the value from the Row where A6 matches A2:A4 and the Column where A7 matches B1:D1 (closest match without going over)

HTH
lenze

 

[cyndy01]

Ah I see what you're saying. But somehow it didn't work for me. I got the Jeanie to show you what I have:

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 64px"><COL style="WIDTH: 56px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 27px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Employee Class</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">LOS Start</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">LOS End</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Rate</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">Executive</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc99">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc99">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">Executive</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">Executive</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">Executive</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: center">11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">Executive</TD><TD style="TEXT-ALIGN: right">20</TD><TD>A.I.</TD><TD style="TEXT-ALIGN: center">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Associate</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Associate</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Associate</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Associate</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Associate</TD><TD style="TEXT-ALIGN: right">20</TD><TD>A.I.</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Manager</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Manager</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Manager</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Manager</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Manager</TD><TD style="TEXT-ALIGN: right">20</TD><TD>A.I.</TD><TD style="TEXT-ALIGN: center">10</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4

This may make it clearer. I made an exact match of the one you did and I'm very grateful, but I ended up with a #Value error when I tried to go Executive 1 year. I was expecting Executive (I2) Years of service 1 (j2) k2= 8 rate. Hopefully the sheet will make it clearer what I was trying to achieve.

 

[lenze]

This is what it should look like

  A         B  C  D  E  F  G  H  I         J     K    
1           0  5  10 15 20       Class     Years Rate 
2 Executive 8  9  10 11 12       Executive 1     8    
3 Manager   7  8  9  10 13                            
4 Associate 6  7  8  9  10                            
Sheet2
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA 
K2    =INDEX($B$2:$F$4,MATCH($I2,$A$2:$A$4,0),MATCH($J2,$B$1:$F$1,1))
[Table-It] version 09 by Erik Van Geit

You can copy the formula down in K2 if you need to.

lenze

 

[cyndy01]

Thank you so much! I never thought to transpose the numbers across the top! That's the best, kick myself and facepalm myself idea I've never come up with! You're the best!

I kept trying to come up with everyway to not alter the format of the data, but I'm coming to realize that the end result sometimes can't come unless you do. ^_^ Thanks ever so much for that and the explanation! I love learning this stuff so I don't have to ask people as opposed to just getting the answer. I appreciate this so much!