array vlookup formula help

[michelle]

I have several sheets as keys -an employee key, percentage key and a pay
grade key. I hope this makes sense. On the employee key I would like to
pull in paygrade comparisons from the grade key spreadsheet. For example,
the employee earns 5.00/hr, on the FY03 grade he is in the "maximum" grade
and on the FY07 grade he is in the "job worth" grade.

On Grade Key spreadsheet
fy03 fy07
grade low high status grade low high status
5 3 3.99 minimum 5 4.00 4.99 minimum
5 4 4.99 job worth 5 5.00 5.99 job worth
5 5 5.99 maximum 5 6.00 6.99 maximum
5 6 over max 5 7 over max
6 4.5 6.99 minimum 6 5.5 7.99 minimum
6 5.5 7.99 job worth 6 6.5 8.99 job worth

On Employee Key spreadsheet
Employee Grade Per Hour FY03 FY07
Jane Doe 5 5.00 Max Job Worth

Thanks in advance

 

[Chad]

have you tried using vlookup? perhaps something like this:
for fy03, =VLOOKUP(C2,'key grade'!$B$3:$D$8,3,FALSE), where c2 is the per
hour employee wage to be found and b3:d8 is the range of key grades
(basically i copied your data into the range b3:h8 on a sheet named key
grade, then applied the vlookup function)

hope this helps,
-chad

 

[michelle]

Thank you for your response. I have tried a vlookup, but I ran into problems
because of the grades. In the following example, 4.5 would fall under either
grade 5 or grade 6.
5 4 4.99 job worth 5 5.00 5.99 job worth
6 4.5 6.99 minimum 6 5.5 7.99 minimum

I tried to do the following formula (where column "D" is the job worth,
column "A" is the grade, and column "B" is the pay scale). I also tried
inserting a vlookup where the match is too. I get #n/a as answer.
=INDEX('Grade Key'!$D$3:$D$62,MATCH(1,('Grade Key'!$A$3:$A$62=H2)*('Grade
Key'!$B$3:$B$62=P2),0),1)