# Help with "IF" formula

#### SandyD

##### New Member
I am trying to sep up a spreadsheet that will caculate the pay for longevity. I have the emplyee names in A2, the years of service in B2. I want Excel to look at the number of years and enter the amount to be paid. My data is as follows:

1 yr = 200
2 yr = 400
5 yr = 700
10yr = 1000
15 yr = 1200
20 yr = 1400

I have tried every combination of "IF" statements with no luck. Can anyone help.

Thanks,

Sandy D.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
why not use a vlookup?

I am using Excel 2003 and am not familiar with vlookup? I sthis something that you could offer some guiadiance with? I sure hope so. This would make this so much easier for payroll.

Sandy D.

Take a look at Excel's help on VLOOKUP. They have a detailed explanation, though I think the examples make it more apparent on how to use it.

Here you go.
Basically the table to the left has the years of service correlating with the amount of pay. The VLOOKUP formula always rounds down, so someone with 14 YOS would get the 10 year pay. Make sense? Hopefully.
Master test.xls
EFGHIJ
57NameYrsofservicePay
581200James2400
592400Patty4400
605700Eric111000
61101000Jimmy151200
62151200Sheila181200
Sheet1

Thanks for all of the help. I think this VLOOKUP will do what I need. I will need to make sure it doesn't round down, or I'll have some employees ready to run me out of town- LOL.

Sandy D.

I will need to make sure it doesn't round down,
Take a look at the 4th argument in the VLOOKUP function in Excel help.

If you set it to FALSE or 0, it will only return exact matches.
If you set it to TRUE or 1, then "if an exact match is not found, the next largest value that is less than lookup_value is returned".

I will need to make sure it doesn't round down,
Take a look at the 4th argument in the VLOOKUP function in Excel help.

If you set it to FALSE or 0, it will only return exact matches.
If you set it to TRUE or 1, then "if an exact match is not found, the next largest value that is less than lookup_value is returned".

Replies
1
Views
324
Replies
5
Views
172
Replies
7
Views
191
Replies
2
Views
186
Replies
1
Views
493

1,219,974
Messages
6,151,230
Members
451,018
Latest member
dids86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back