Use two criteria on one table to find a matching salary on a second table.

[iosiflupis]

I have a workbook with two worksheets. on the first sheet, I want to use years of service and category to then derive the salary from the second worksheet.

I believe that this will be a combined formula for lookup and index/match. Unfortunately, this is getting way beyond my abilities.

Thank you

 

[Gerald Higgins]

Hi, this is do-able, although it's complicated by the fact that your years of service in column F in the first sheet, are not whole years, they include fractions of years.
Can you do something to convert them into whole years ?

Let's assume you can, and you create a new column of data in the first worksheet, let's say in column K, to store the whole years.
Let's assume column K now includes the full year equivalent of column F, in a format that exactly matches the Year labels in row 1 on the second sheet.

Something like this, in J2 on the first sheet, should work . . .

=OFFSET(Sheet2!A1,MATCH(Sheet1!I2,Sheet2!A2:A10,0),MATCH(Sheet1!K2,Sheet2!B1:U1,0),1,1)

Adapt the cell and sheet references as required.

 

[iosiflupis]

Gerald,

Thank you for the help.