Desu Nota from Columbus
Well-known Member
- Joined
- Mar 17, 2011
- Messages
- 556
I am trying to do a cross reference with First Name, Last Name. I am comparing a list of current employees to a list employees who have undergone training.
The training list starts in 2005 up until today, so naturally, many of them no longer are employed.
What I need to happen:
I need to compare a list of current employees to the names on the Training List. If there is a match, return a 1 in column X, otherwise return a 0 in column X.
I don't have the range for the current employees, but lets assume it is setup as follows:
On Sheet 1:
Column AA(17-100): First Name
Column AB(17-100): Last Name
Training List Layout: (Sheet 1) Starting on row 17
Column B is First Name
Column C is Last Name
I don't know how best to do this, this was my attempt but it failed (as expected)
In X17:
=IF(ISERROR(AND(MATCH(AA17:AA100,B:B ),MATCH(AB17:AB100,C:C))),0,1)
However this is done, the formula must search FIRST and Lastname as a pair. (Ex. AA17, AB17 must match B55, C55)
If the name John Smith is to be matched, it should only return a 1 if the name John Smith is found. With what I have tried and seen, if the names John White and Nancy Smith were on the list to being matched, John Smith would 'exist'. Hope this makes sense.
The training list starts in 2005 up until today, so naturally, many of them no longer are employed.
What I need to happen:
I need to compare a list of current employees to the names on the Training List. If there is a match, return a 1 in column X, otherwise return a 0 in column X.
I don't have the range for the current employees, but lets assume it is setup as follows:
On Sheet 1:
Column AA(17-100): First Name
Column AB(17-100): Last Name
Training List Layout: (Sheet 1) Starting on row 17
Column B is First Name
Column C is Last Name
I don't know how best to do this, this was my attempt but it failed (as expected)
In X17:
=IF(ISERROR(AND(MATCH(AA17:AA100,B:B ),MATCH(AB17:AB100,C:C))),0,1)
However this is done, the formula must search FIRST and Lastname as a pair. (Ex. AA17, AB17 must match B55, C55)
If the name John Smith is to be matched, it should only return a 1 if the name John Smith is found. With what I have tried and seen, if the names John White and Nancy Smith were on the list to being matched, John Smith would 'exist'. Hope this makes sense.
