How to match data from two sources

[Shockingviz]

I have two sets of data (Sample Source A and B) in hundreds. I would need to match the interviewer's result with the candidate's application (candidate who have chosen the post and was interviewed). As the candidate can choose up to three posts, the candidate maybe found suitable for more than 1 post based on the ranking order by the Dept. This makes the matching more tedious (via manually). I would like to achieve the Sample Outcome in the attached file where it can help to match the dept's outcome and the applicant's ranking order of the post. This is to allow matching of the officer to the post easier and accurately based on both ranking order by candidate and dept (incl the ranking order to be reflected. Thanks!

 

[Chihiro]

Can you explain logic? Either there's a lot of errors in your data, or I'm missing something in your logic.

Post G - Does not exist in either Source A or B but you have data in outcome

Candidate Z in Outcome has Post C as Post Applied, but it's Candidate W that applied for Post C (Candidate Z is marked as unsuitable for Post B).

Candidate Y is missing from Source B...
etc, etc.

Also, what's the logic for Ranking order? Why 10 for Candidate ABC in Source B for example...

 

[Shockingviz]

I have updated the file

 

[NARAYANK991]

Hi ,

Going by what you have shown , the following formula should help :

=INDEX($C$18:$C$29, MATCH(A3 & B3,$B$18:$B$29 & $A$18:$A$29, 0))

This is an array formula , and has to be entered using CTRL SHIFT ENTER.

Narayan

 

[bosco_yip]

Try this non-array LOOKUP formula,

In D3, formula copy down :

=LOOKUP(2,1/((B$18:B$29=A3)*(A$18:A$29=B3)),C$18:C$29)

Or,

If you like to use shorter formula, try this VLOOKUP array formula :

=VLOOKUP(B3,IF(B$18:B$29=A3,A$18:C$29),3,0)

Confirm press together with SHIFT+CTRL+ENTER 3 keystrokes

Regards
Bosco

 

[Khalid NGO]

Hi,

Or SUMIFS with just enter:

=SUMIFS(C$18:C$29,B$18:B$29,A3,A$18:A$29,B3)

Format the cell as:
#;-#;"Unsuitable"

Regards,

 

[bosco_yip]

Another option to use SUMIFS function without using Custom Format,

=IFERROR(1/(1/(SUMIFS(C$18:C$29,B$18:B$29,A3,A$18:A$29,B3))),"Unsuitable")

Regards
Bosco

 

[Chihiro]

Just for fun, non formula method.

Since both tables have identical entry for column 1 & 2...

1. Sort Source A by "Post Applied" then by "Candidate"

2. Sort Source B by "Post Applied" then by "Candidate Applied".

Copy and paste last column of Source B beside Source A.

I'd imagine this may not be feasible in real life scenario, as there could be missing entry from one to the other.