Need Help with Index-Match/Small Formula

[agamino]

I have a spreadsheet with two tabs; tab one is a keystroke calculator, tab two is an array of information. I want to be able to identify a skill class based on two values the Quality of Data and KPH values.

Basically, if the value in "Keystroke Calc" C2 is "Handwritten and the KPH is between a range, in this case 2500, it should retrieve the value "Very Good" because it is =>than 2000 and <3000. The QofD changes based on selection, the KPH changes based on the word per minute a person can enter data.


Tab 1

Docs	KPD	Quality of Data	WPM	KPH	Skill Class
150	200	Handwritten	10	2500

<tbody>
</tbody>

Tab 2

Skill Class	Excellent	Very Good	Good	Average	Below Average
Typed	8000	6500	5500	4000	3000
Typed/Multiple Pages	5000	4000	3000	2000	1000
Typed/Handwritten(80/20)	6500	5500	4500	3500	2500
Typed/Handwritten(70/30	6000	5000	4000	3000	2000
Typed/Handwritten(60/40)	5500	4500	3500	2500	1500
Typed/Handwritten(50/50)	5000	4000	3000	2000	1000
Typed/Handwritten(40/60)	4500	3500	2500	1500	500
Typed/Handwritten(30/70)	4000	3000	2000	1000	250
Typed/Handwritten(20/80)	3500	2500	1500	500	100
Handwritten	3000	2000	1000	100	50

<tbody>
</tbody><colgroup><col><col span="5"></colgroup>

Been working on this one for a couple of days now using Index-Small and Index-Match, still cannot get it to work. Any assistance would be really appreciated.

Thanks in Advance.

 

[lrobbo314]

So, I rearranged your lookup table like this..

Skill Class	Below Average	Average	Good	Very Good	Excellent
Typed	3000	4000	5500	6500	8000
Typed/Multiple Pages	1000	2000	3000	4000	5000
Typed/Handwritten(80/20)	2500	3500	4500	5500	6500
Typed/Handwritten(70/30	2000	3000	4000	5000	6000
Typed/Handwritten(60/40)	1500	2500	3500	4500	5500
Typed/Handwritten(50/50)	1000	2000	3000	4000	5000
Typed/Handwritten(40/60)	500	1500	2500	3500	4500
Typed/Handwritten(30/70)	250	1000	2000	3000	4000
Typed/Handwritten(20/80)	100	500	1500	2500	3500
Handwritten	50	100	1000	2000	3000

<tbody>
</tbody>

Then the formula for Skill Class would be.

=LOOKUP($M$2,OFFSET(Sheet1!$B$1:$F$1,MATCH($K$2,Sheet1!$A$2:$A$11,0),0,1,5),Sheet1!$B$1:$F$1)

I pasted your lookup table in A1:F11 on sheet 1, and the other table with the formula is in I1:N2. You might need to adjust your references, but this formula should work.

 

[agamino]

Irobbo314, Thanks for your work, though I could not get it to work. After changing the references it does produce a "0", which is not the expected result.