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.

One way ..

Source data as posted assumed in A1:D4

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joe Murphy" wrote in message
...
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.

Try this:

With your posted data list in A1:D4

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" wrote in message
...
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.

On 24 Jan, 13:14, "Ron Coderre"
wrote:
Try this:

With your posted data list in A1:D4

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.

"Joe Murphy" wrote:
.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On 24 Jan, 14:40, "Max" wrote:
"Joe Murphy" wrote:
.. Max,
Thanks, too. I'll test your solution. Looks good to me, though.

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.

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joe Murphy" wrote in message
...
On 24 Jan, 14:40, "Max" wrote:
"Joe Murphy" wrote:
.. Max,
Thanks, too. I'll test your solution. Looks good to me, though.

Ron's solution works just fine.
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.

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" wrote in message
...
On 24 Jan, 13:14, "Ron Coderre"
wrote:
Try this:

With your posted data list in A1:D4

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.

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

Best,

Joe.

Welcome, glad it clarified.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joe Murphy" wrote in message
...
Nice work, Max. That explains things *beautifully*.
Best,
Joe.