Elegant solution for two comparisons

[Joe Murphy]

I have a list of employees. Employees have salaries, and they have
grades (A through G). I'd like to tie their salaries and grades to a
separate table of grade points. Here's an example of the table:

Grade G / Grade F / Grade E
6 14,000 / 16,000 / 18,000
5 12,000 / 14,000 / 16,000
4 10,000 / 12,000 / 14,000

So I'd start with an employee with a salary of 14,000. He's Grade F.
So the formula would output point 5. If he was Grade G, he'd instead
be on point 6.

What's the most elegant way to build this? Match and Index (which I'm
not familiar with)? Nested IFs?

Thanks,

Joe.

 

[Max]

One way ..

Source data as posted assumed in A14

In F2:G2 down are the grades and amounts, eg:
Grade F, 14,000
Grade G, 14,000

Place in H2:
=INDEX($A$2:$A$4,MATCH(G2,OFFSET($A$2:$A$4,,MATCH(F2,$B$1:$D$1,0)),0))
Copy down to return required results

 

[Ron Coderre]

Try this:

With your posted data list in A14

F1: (a salary....eg 14000)
G1: (a grade....eg Grade G

This formula returns the associated grade points:
=SUMPRODUCT(($B$2:$D$4=F1)*($B$1:$D$1=G1)*$A$2:$A$4)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Joe Murphy]

Try this:

With your posted data list in A14

F1: (a salary....eg 14000)
G1: (a grade....eg Grade G

This formula returns the associated grade points:
=SUMPRODUCT(($B$2:$D$4=F1)*($B$1:$D$1=G1)*$A$2:$A$4)

Ron, Ron, Ron.

That's awfully clever. First time my manager's eyes lit up in weeks.


Max,

Thanks, too. I'll test your solution. Looks good to me, though.

Cheers!

Joe.

 

[Max]

.. Max,
Thanks, too. I'll test your solution. Looks good to me, though.

Yes, think it works ok too. Do use Ron's shorter solution in this kind of
instance, where it's only numbers involved in the return range A2:A4. Should
A2:A4 ever contain text instead of numbers (or contain a mix of
text/numbers), eg: a,b,c instead of: 6,5,4, then you could consider using my
suggestion which works for both text and numbers.

 

[Joe Murphy]

Ron's solution works just fine. Yours is throwing up a #N/A, however.

I don't understand the formula, so I'll do some reading and get back
to you if I can spot the problem. All the cells the formula points to
look fine, though..

Joe.

 

[Max]

.. Yours is throwing up a #N/A, however.

Note that my expression assumes

In F2:G2 down are the grades and amounts, eg:
Grade F, 14,000
Grade G, 14,000

while Ron's assumptions on the inputs were the other way around,

F1: (a salary....eg 14000)
G1: (a grade....eg Grade G

That could be the reason for the error?

Anyway, for easy reference, here's a sample illustrating
both expressions under the 2 scenarios* mentioned:
http://www.freefilehosting.net/download/3b1ga
Table Extractions.xls
(I've adapted Ron's expression to suit the inputs set-up)

*Scenarios:
Scenario1: Return range A2:A4 contains only numbers
Scenario2: Return range A2:A4 contains text or mix of text/numbers

 

[Ron Coderre]

Thanks for the kind words, Joe.

(...Note: everything Max said about text/mixed_data is valid)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Joe Murphy]

Nice work, Max. That explains things *beautifully*.

Best,

Joe.

 

[Max]

Welcome, glad it clarified.