index problem

[aladinalamp]

Please help, I am trying to fill in the grade by cross referencing the sales per hour and the matrix per hour. The point that these cross in the chart should return a pay grade letter.

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		MatrixPerHour	SalesPerHour	Grade
3		56	520				125+	B	B	C	D	E	F
4							100-125	B	B	C	D	E	E
5							75-100	B	B	C	D	D	D
6							55-75	A	B	C	C	C	C
7							40-55	A	B	B	C	C	C
8							25-40	A	A	B	B	B	B
9								275-400	400-500	500-600	600-700	700-850	850+
10
11
12
13
14
15
16
17
Sheet1

many thanks in advance

 

[onlyadrafter]

Hello,

Change your column G values to the lower of the two, and change Row 9 to the higher of the two, make 850+, 100000 or higher. Sort the table via Col G so 25 is atr the top and 125 is at the bottom.

Does this give the correct answer

=VLOOKUP(B3,$G$3:$M$8,MATCH(C3,$H$9:$M$9,1)+2)

I get C for your example.

 

[aladinalamp]

Not sure that works

This is the data that I am working with the letters in the blue are the results I need from the data in columns H and I. I have made the amends that you suggested but they dont return what I am after. I know you are on the right lines but just cant figure it out. The columns H and I are in Currency format to .0 places.

QUESTION.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2		Sales	Hours	TPC	Essentials	TotalMatrix	MatrixPerHour	SalesPerHour	Grade			PerformanceContrcat
3	Colleague1	23,683	39	427	1,028	1,455	37.31	607	B			GO			125	B	B	C	D	E	F
4	Colleague2	13,748	35	95	398	493	14.09	393				GO			100	B	B	C	D	E	E
5	Colleague3	15,284	39	917	1,198	2,115	54.23	392	A			GO			75	B	B	C	D	D	D
6	Colleague4	6,102	18	230	273	503	27.94	339	A			GO			55	A	B	C	C	C	C
7	Colleague5	12,861	18	373	1,228	1,601	88.94	715	D			GO			40	A	B	B	C	C	C
8	Colleague6	14,366	39	550	734	1,284	32.92	368	A			GO			25	A	A	B	B	B	B
9	Colleague7					#REF!	#REF!	#DIV/0!				GO				400	500	600	700	850	100000
Week 2

Thanks in advance

 

[Aladin Akyurek]

Expand and re-arrange the lookup table you have:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		0	400	500	600	700	850	100000
2	0									45	608	B
3	25		A	A	B	B	B	B		0	700
4	40		A	B	B	C	C	C		160	850	E
5	55		A	B	C	C	C	C
6	75		B	B	C	D	D	D
7	100		B	B	C	D	E	E
8	125		B	B	C	D	E	F
Sheet2

B2:

=""

Copy this down to B8 and across to H8.

The formula in L2 shows how to use the new table:

=INDEX($B$2:$H$8,MATCH(J2,$A$2:$A$8,1),MATCH(K2,$B$1:$H$1,1))

 

[aladinalamp]

Yipee

many thanks that works fine thank you for your time

mike