Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 10 '12 at 10:52

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers

=IF(SUMPRODUCT((B2=F2)*(C2=G2)*(D2=H2))=1,A2)

enter image description here

share|improve this answer
    
Thank you adayzone. I am still having a problem because more than one value (matched requisition number) can be returned for an employee. I concatenated the 3 attributes for the employee and the requisition and then did a vlookup to have the name of the employee that matched the requisition attributes displayed. However, i get an #N/A in the cell if there are more than one employees who match. Is there a way to return multiple values? –  Susan C Nov 10 '12 at 0:43
add comment

The example spreadsheet is available as http://www.bumpclub.ee/~jyri_r/Excel/Employees_and_vacancies_matched.xls

Test layout:
Columns A:E - Employee name (A), job function (B), job grade (C), location (D
Column E: - Columns B:E concatenated
Columns I:L - Requisition number (I), job function (J), job grade (K), location (L)
Column M: - Columns J:L concatenated

Column F contains an array formula (Shift-Ctrl-Enter from formula window to insert, curled brackets are inserted by Excel, not by the user):

={SMALL((IF($M$2:$M$100=$E2,OFFSET($M$2:$M$100,0,-4),"*")),1)}

The formula searches for a match of employee data from open positions (IF($M$2:$M$100=$E2), and in case of positive finding returns the corresponding requisition number (OFFSET($M$2:$M$100,0,-4), otherwise returns "*". The results will be stored in an array, and there is no straight way to show them. Therefore, in the current column the smallest requisition number will be shown (SMALL(array;1)).

The second match will be displayed in the next column. After copying the formula, ),1)} should be replaced with ),2)}, and so on.

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.

share|improve this answer
add comment

I think you are on the right lines with concatenating the three attributes. These could then be pivoted as below:

SU503767 example

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.