Help! Index and Match Functions

[alidp]

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>

 

[Peter_SSs]

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.

 

[alidp]

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!

 

[Peter_SSs]

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?

 

[alidp]

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

 

[alidp]

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

 

[Peter_SSs]

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