I have a data set for employees that contains name and 3 attributes (job function, job grade and location). I then have a data set for open positions that contains the requisition number and 3 attributes (job function, job grade and job location). For every employee, i would like the three attributes associated with them compared to the same three attributes of the open positions and have the cooresponding requisition numbers displayed for each employee where there is a match.
migrated from stackoverflow.com Nov 10 '12 at 10:52
This question came from our site for professional and enthusiast programmers.
The example spreadsheet is available as http://www.bumpclub.ee/~jyri_r/Excel/Employees_and_vacancies_matched.xls
Column F contains an array formula (Shift-Ctrl-Enter from formula window to insert, curled brackets are inserted by Excel, not by the user):
The formula searches for a match of employee data from open positions (
The second match will be displayed in the next column. After copying the formula,
If such solution works for you, it's possible to work out a formula, warning the user if there are too few columns to display all matching results.
I think you are on the right lines with concatenating the three attributes. These could then be pivoted as below:
To help with legibility, at least to begin with, I have added 'Job' and 'Vacancy'. For Set AAA one person is employed (#401) and no requisition exists (Vacancy is blank). For Set ABC there is a requisition (req# 3a) and one person already employed with that skill set (#123).