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


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Docs[/TD]
[TD]KPD[/TD]
[TD]Quality of Data[/TD]
[TD]WPM[/TD]
[TD]KPH[/TD]
[TD]Skill Class[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]200[/TD]
[TD]Handwritten[/TD]
[TD]10[/TD]
[TD]2500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Tab 2
[TABLE="width: 590"]
<tbody>[TR]
[TD]Skill Class[/TD]
[TD]Excellent[/TD]
[TD]Very Good[/TD]
[TD]Good[/TD]
[TD]Average[/TD]
[TD]Below Average[/TD]
[/TR]
[TR]
[TD]Typed[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]Typed/Multiple Pages[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(80/20)[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]2500[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(70/30[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(60/40)[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(50/50)[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(40/60)[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(30/70)[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(20/80)[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Handwritten[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody><colgroup><col><col span="5"></colgroup>[/TABLE]

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

[TABLE="width: 513"]
<tbody>[TR]
[TD]Skill Class[/TD]
[TD]Below Average[/TD]
[TD]Average[/TD]
[TD]Good[/TD]
[TD]Very Good[/TD]
[TD]Excellent[/TD]
[/TR]
[TR]
[TD]Typed[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]8000[/TD]
[/TR]
[TR]
[TD]Typed/Multiple Pages[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(80/20)[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]6500[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(70/30[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]6000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(60/40)[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]5500[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(50/50)[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(40/60)[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4500[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(30/70)[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD]Typed/Handwritten(20/80)[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD]Handwritten[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000
[/TD]
[/TR]
</tbody>[/TABLE]

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.