# Help! Index and Match Functions

#### alidp

##### New Member
Hi,

I am trying to use index and match formulas to determine the salary percentile in table 1 below. I need to match the person's group and position name in Table 1 to those in Table 2 and then determine the closest percentile to that person's salary.

For example: Joe an analyst in red group makes 37,000. The correct return in table 1 would be the 12th percentile. Any help on this is greatly appreciated! I'm not sure how to attach/link to the Excel file, so the tables are below. Thanks in advance!

 A B C D E F G H 1 Table 1 2 3 Name Group Position Name Salary Salary Percentile 4 Joe Red Analyst 37000 ? 5 Bob Green Associate 309000 ? 6 Sally Blue Assistant 156000 ? 7 John Red Chief 142000 ? 8 9 Table 2 10 11 Group Position Name 10th %tile 11th %tile 12th %tile 13th %tile 14th %tile 15th %tile 12 Red Analyst 30,000 33,000 36,000 39,000 42,000 45,000 13 Red Associate 100,800 110,880 120,960 131,040 141,120 151,200 14 Red Assistant 125,600 138,160 150,720 163,280 175,840 188,400 15 Red Chief 130,800 143,880 156,960 170,040 183,120 196,200 16 Green Analyst 163,600 179,960 196,320 212,680 229,040 245,400 17 Green Associate 220,400 242,440 264,480 286,520 308,560 330,600 18 Green Assistant 28,000 30,800 33,600 36,400 39,200 42,000 19 Green Chief 92,000 101,200 110,400 119,600 128,800 138,000 20 Blue Analyst 110,000 121,000 132,000 143,000 154,000 165,000 21 Blue Associate 112,800 124,080 135,360 146,640 157,920 169,200 22 Blue Assistant 158,400 174,240 190,080 205,920 221,760 237,600 23 Blue Chief 204,400 224,840 245,280 265,720 286,160 306,600

<tbody>
</tbody>

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to the MrExcel board!

For example: Joe an analyst in red group makes 37,000. The correct return in table 1 would be the 12th percentile.
Just to clarify .. IF Joes makes \$38,000 would the result change to the 13th percentile because that is now closer, or would it stay at the 12th because that is the closest one below what he makes?

For the record, you cannot attach files, but what you did was fine, or you can also post small screen shots using one of the methods shown in my signature block.

Welcome to the MrExcel board!

Just to clarify .. IF Joes makes \$38,000 would the result change to the 13th percentile because that is now closer, or would it stay at the 12th because that is the closest one below what he makes?

For the record, you cannot attach files, but what you did was fine, or you can also post small screen shots using one of the methods shown in my signature block.

Yes, the result would change to the 13th percentile if Joe makes \$38,000 because that is now closer. Thanks for your response!

Yes, the result would change to the 13th percentile if Joe makes \$38,000 because that is now closer. Thanks for your response!
So, I guess I should have asked this last time as well: What if Joe makes \$37,500, exactly half way between \$36,000 and \$39,000?

He makes exactly half, it should output the higher percentile.

Does anyone know of a formula that might help me? I appreciate any thoughts!

Given that it appears that a person's salary can possibly fall below the 10th%tile mark or above the 15th%tile mark, a single formula to do this task would be very large.
So, to make the individual formulas a bit smaller and easier to understand/modify, I have assumed that we can use some helper columns.

Therefore I have added a new column C below. The formula in C12 is copied down.
Column J is filled with a very large number.
Columns C and J could be hidden once the formulas/numbers are in place.
Formulas in F2:H2 are copied down.

Excel Workbook
ABCDEFGHIJ
1Table1
2
3NameGroupPosition NameSalarySalary PercentileTable 2 RowTable 2 Column
4JoeRedAnalyst37,00012th %tile14
5BobGreenAssociate309,00014th %tile66
6SallyBlueAssistant156,00010th %tile111
7JohnRedChief142,00011th %tile42
8
9Table2
10
11GroupPosition Name10th %tile11th %tile12th %tile13th %tile14th %tile15th %tile
12RedAnalyst-30,00030,00033,00036,00039,00042,00045,0009.99E+307
13RedAssociate-100,800100,800110,880120,960131,040141,120151,2009.99E+307
14RedAssistant-125,600125,600138,160150,720163,280175,840188,4009.99E+307
15RedChief-130,800130,800143,880156,960170,040183,120196,2009.99E+307
16GreenAnalyst-163,600163,600179,960196,320212,680229,040245,4009.99E+307
17GreenAssociate-220,400220,400242,440264,480286,520308,560330,6009.99E+307
18GreenAssistant-28,00028,00030,80033,60036,40039,20042,0009.99E+307
19GreenChief-92,00092,000101,200110,400119,600128,800138,0009.99E+307
20BlueAnalyst-110,000110,000121,000132,000143,000154,000165,0009.99E+307
21BlueAssociate-112,800112,800124,080135,360146,640157,920169,2009.99E+307
22BlueAssistant-158,400158,400174,240190,080205,920221,760237,6009.99E+307
23BlueChief-204,400204,400224,840245,280265,720286,160306,6009.99E+307
Lookup Table

Replies
34
Views
2K
Replies
5
Views
170
Replies
1
Views
388
Replies
5
Views
4K

1,196,474
Messages
6,015,427
Members
441,893
Latest member
vjciletti

### 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