match multiple criteria & return value from array
 

I have a spreadsheet that lists employees with their discipline ie PT
(Physiotherapy), SW (Social Work), OT(Occupational Therapy) and there grid
value (1 to 5). The grid indicates which pay rate they are at.
In addition I have a pay grid of the disciplines and the rate associated
with each grid number (1 to 5). I would like to pull the correct rate for an
employee based on their discipline and their grid number. How do I go about
this.
1 2 3 4 5
PT 20.00 22.00 23.00 24.00 25.00
OT 16.50 18.00 20.50 22.00 24.50

So, If a PT is at the 3 pay grid the value returned would be 23.00

You can use index and match as illustrated here


http://www.contextures.com/xlFunctio...ml#IndexMatch3


Regards,

Peo Sjoblom

"Tat" wrote:

I have a spreadsheet that lists employees with their discipline ie PT
(Physiotherapy), SW (Social Work), OT(Occupational Therapy) and there grid
value (1 to 5). The grid indicates which pay rate they are at.
In addition I have a pay grid of the disciplines and the rate associated
with each grid number (1 to 5). I would like to pull the correct rate for an
employee based on their discipline and their grid number. How do I go about
this.
1 2 3 4 5
PT 20.00 22.00 23.00 24.00 25.00
OT 16.50 18.00 20.50 22.00 24.50

So, If a PT is at the 3 pay grid the value returned would be 23.00

Thanks. The examples were very helpful and the formula I created worked.

"Peo Sjoblom" wrote:

You can use index and match as illustrated here


http://www.contextures.com/xlFunctio...ml#IndexMatch3


Regards,

Peo Sjoblom

"Tat" wrote:

I have a spreadsheet that lists employees with their discipline ie PT
(Physiotherapy), SW (Social Work), OT(Occupational Therapy) and there grid
value (1 to 5). The grid indicates which pay rate they are at.
In addition I have a pay grid of the disciplines and the rate associated
with each grid number (1 to 5). I would like to pull the correct rate for an
employee based on their discipline and their grid number. How do I go about
this.
1 2 3 4 5
PT 20.00 22.00 23.00 24.00 25.00
OT 16.50 18.00 20.50 22.00 24.50

So, If a PT is at the 3 pay grid the value returned would be 23.00