Finding out a position in a range

[RFJ]

I'm wanting to establish a formula that gives me the position of a person's
salary in their range - ie bottom quarter, second, third or fourth quarter.

So, in the data below I want to be able to look up the grade (Col A), check
where the person's salary is, and report the relevant value in the first
row.

eg Grade = 3, salary = £45,000, would return the value Q2


Grade Min Q1 Q2 Q3 Q4
1 £90,389 £90,389 £90,389 £90,389 £90,389
2 £57,000 £60,563 £64,125 £67,688 £71,250
3 £41,290 £44,645 £48,000 £51,355 £54,710
4 £30,000 £33,000 £36,000 £39,000 £42,000



TIA

Rob

 

[Bernie Deitrick]

Rob,

With your table in A1:F5, use the formula

=INDEX(1:1,MATCH(B10-1,OFFSET(A1,MATCH(A10,A1:A5)-1,0,1,6))+1)

where B10 has the salary of interest, and A10 has the grade of interest.

HTH,
Bernie
MS Excel MVP

 

[Jason Morin]

Try:

="Q"&MATCH(I1,OFFSET(A1,MATCH(H1,A1:A5,0)-1,1,,5),1)

where I1 = salary and H1 = grade.

HTH
Jason
Atlanta, GA

 

[RFJ]

Thanks for the reply. I've got your formula working on the data I used in
the posting <G>.

In practice, the grades are not 1, 2, 3, 4 - but 6, 5, 4, 3 - in descending
order (ie col A in my original data).

If I put those grades in, the formula sometimes returns the dreaded N/A. Do
you know how I can overcome it

TIA

Rob

 

[Bernie Deitrick]

Rob,

Either sort your data ascending <g> or use FALSE as the last parameter in
the second MATCH. Change

MATCH(A10,A1:A5)
to
MATCH(A10,A1:A5,FALSE)

HTH,
Bernie
MS Excel MVP

 

[RFJ]

Thanks Bernie - I'll try that,

I'd reposted the message as a new Part 2 so you reply overtakes that.

Regards

Rob