How To Create a Value Based Upon The Contents of A List

[jeb]

Our company provides quarterly employee reviews.

What would be the best function to use in the following scenario:

We create (re-use) a monthly spreadsheet of employees, title, department,
main duties, & contact information.

We have a list of titles & departments (on a separate tab) divided by the
month that the title or department is due for review:
Column A:JANUARY
ADMINISTRATIVE ASSISTANTS
PAYROLL

Column B:
FEBRUARY
INTERNS
MARKETING

We want to add a column to the far left of the monthly spreadsheet so that
each employee has a cell to indicate if they are up for review.

Review? Name Title Department Duties Phone Email
R TEST PERSON ADMINISTRATIVE ASST. ADMINISTRATIVE DUTIES
x-1234 (e-mail address removed)

Would an IF or VLOOKUP function be best for this?

 

[Luke M]

When you say, up for review, do you mean they should be reviewed if their job
title is under the current month (example for now would be February) heading
on sheet1?

=IF(ISNUMBER(MATCH(C33,INDEX(Sheet1!$A$2:$L$3,0,MONTH(TODAY()),1),0)),"R","")

 

[jeb]

Yes.
Tab 1(page 1) would have the employee's information.
Tab 2 (page 2) would have the review schedule. There would be 12 columns.
Each column would have titles & departments for review.