lookup function

daveturner80

New Member
Joined
May 11, 2011
Messages
18
I have a spreadsheet that we use to award service pins when our members reach different years of service.
In column A we have members names and in column C we have their start date. Columns D thru Q are years where we put in a 1 for every year of service. Column R sums up Columns D thru Q. In the next column I would like to put in the members years of service and have it just put in the number of years in increments of 5 years, so if a member has 5 years it would put in a 5, but if he had 8 years the cell would just be blank. Would this be possible to do fairly easy?
Thanks for any help.
Dave
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
it didn't seem to work.What I am getting from your code is that it is looking in row R2 and if it finds a 5, put it in the cell.
Is this correct?
If it is I have columns from A3 to A110 so would the code have to be changed to recognize these cells?
Also the years could go from 5 years all the way up to 60 in 5 year increments.
What do I need to do?
Thanks,
Dave
 
Upvote 0
it didn't seem to work.What I am getting from your code is that it is looking in row R2 and if it finds a 5, put it in the cell.
Is this correct?
If it is I have columns from A3 to A110 so would the code have to be changed to recognize these cells?
Also the years could go from 5 years all the way up to 60 in 5 year increments.
What do I need to do?
Thanks,
Dave
The formula is testing to see if the cell contains a number that is a multiple of 5. If it is, the formula returns that value otherwise it returns a blank. For example...

R2 = 25

The formula will return 25

R2 = 16

The formula will return a blank
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top