IF, Match, Do This... Question

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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Do you ONLY want to see if the name exists?
Or are you eventually planning on returning information from another column based on that match?

If you ONLY want to know if the name exists on the other sheet, then

=SUMPRODUCT(--(AA$17:AA$100=B1),--(AB$17:AB$100=C1))>0

This will give a True/False result if the combination of B1&C1 exists in AA&AB
 
Upvote 0
You'll need to do this as an array-entered MATCH. Try:

=ISNUMBER(MATCH(1,IF(B:B=AA17,IF(C:C=BB17,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER, not just enter. This way it is stored as an array formula. You will know it is entered properly when you see {brackets} around the formula.

Note: If you use Excel 2003 or older, you need to explicitly define the ranges, as you can't use entire-column references in array formulas.
 
Upvote 0
Do you ONLY want to see if the name exists?
Or are you eventually planning on returning information from another column based on that match?

If you ONLY want to know if the name exists on the other sheet, then

=SUMPRODUCT(--(AA$17:AA$100=B1),--(AB$17:AB$100=C1))>0

This will give a True/False result if the combination of B1&C1 exists in AA&AB
Thanks for the help.

I eventually will be deleting the record based on the value in X. I was going to use a macro i wrote (after double checking each name) that searches column X for value=0 and deletes entire row

Question:

How exactly does SUMPRODUCT work? From the title it sounds like it would only deal with numbers (does adding a -- to a string make it number? [--Fishsticks] is considered a number?)
 
Upvote 0
Yes, sumproduct only deals with numbers...

--[Fishsticks] is not a number...

But --(AA$17:AA$100=B1) IS a number..
Because it's a question. Does the value in AA = the Value in B1 ?
This question has a TRUE or FALSE answer, the value in AA either DOES or DOES NOT equal the value in B1
In Excel - True = 1, False= 0
The -- converts TRUE/FALSE into 1/0


Hope that helps.
 
Upvote 0
You'll need to do this as an array-entered MATCH. Try:

=ISNUMBER(MATCH(1,IF(B:B=AA17,IF(C:C=BB17,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER, not just enter. This way it is stored as an array formula. You will know it is entered properly when you see {brackets} around the formula.

Note: If you use Excel 2003 or older, you need to explicitly define the ranges, as you can't use entire-column references in array formulas.

I am still compiling my list of current employees so I haven't tested (although I trust both of your answers work), but is the proposed change acceptable?

I'd like the values in AA,AB to be used to as the reference list instead of B,C.

=ISNUMBER(MATCH(1,IF(AA17:AA100=B17,IF(BB17:BB100=C17,1,"")),0))
 
Last edited:
Upvote 0
Yes, sumproduct only deals with numbers...

--[Fishsticks] is not a number...

But --(AA$17:AA$100=B1) IS a number..
Because it's a question. Does the value in AA = the Value in B1 ?
This question has a TRUE or FALSE answer, the value in AA either DOES or DOES NOT equal the value in B1
In Excel - True = 1, False= 0
The -- converts TRUE/FALSE into 1/0


Hope that helps.

Thank you for taking the time to explain this; it helps me tremendously.
 
Upvote 0
Somewhere I have a detailed write-up I made for the SUMPRODUCT and how the double unary (--) ties in with it... let me see if I can find it and paste it in here.
 
Upvote 0
[FONT=&quot]The -- coerces the conditional statement within the parentheses into a 1 or a 0. By doing this, it creates an array of 1s and 0s. It does this by taking each TRUE/FALSE statement and multiplying it by -1 twice.[/FONT]


[FONT=&quot]Lets take the following data for example:[/FONT]

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Amount</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Type</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid; TEXT-DECORATION: underline">Branch</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">4000</TD><TD style="BACKGROUND-COLOR: #ffffff">Deposit</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">East</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">6000</TD><TD style="BACKGROUND-COLOR: #ffffff">Deposit</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">East</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">2000</TD><TD style="BACKGROUND-COLOR: #ffffff">Withdrawl</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">East</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">3000</TD><TD style="BACKGROUND-COLOR: #ffffff">Deposit</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">West</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/2/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">5000</TD><TD style="BACKGROUND-COLOR: #ffffff">Withdrawl</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">East</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/2/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">5000</TD><TD style="BACKGROUND-COLOR: #ffffff">Withdrawl</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">West</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/3/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">10000</TD><TD style="BACKGROUND-COLOR: #ffffff">Deposit</TD><TD style="BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">West</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/3/2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">4000</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff">Withdrawl</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">East</TD></TR></TBODY></TABLE>
Sheet1


[FONT=&quot]And we want to use this SUMPRODUCT function to find the number of Deposits at the East Branch:[/FONT]
[FONT=&quot]=SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”))[/FONT]


[FONT=&quot]What the SUMPRODUCT first does (before it evaluates the double unary -- operator), is internally look at each item on a TRUE/FALSE basis:[/FONT]

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Amount</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Type</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid; TEXT-DECORATION: underline">Branch</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">4000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">TRUE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">6000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">TRUE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">2000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">TRUE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/1/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">3000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">FALSE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/2/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">5000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">TRUE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/2/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">5000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">FALSE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/3/2010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">10000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">FALSE</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">12/3/2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">4000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff">FALSE</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; BORDER-RIGHT: black 1px solid">TRUE</TD></TR></TBODY></TABLE></B>
Sheet1



[FONT=&quot]The first condition: (C2:C9=”Deposit”) returns the array {TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}[/FONT]

[FONT=&quot]The second condition: (D2:D9=”East”) returns the array[/FONT]
[FONT=&quot]{TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE}[/FONT]


[FONT=&quot]Since TRUE/FALSE holds values of 1/0, we multiply them by -1 twice (using the double unary operator) to convert them to a positive, numerical, value:[/FONT]

[FONT=&quot]--(TRUE) = (-1)(-1)(TRUE) = (-1)(-1) = 1[/FONT]
[FONT=&quot]--(FALSE) = (-1)(-1)(FALSE) = (-1)(0) = 0[/FONT]

[FONT=&quot]So now, SUMPRODUCT has two arrays to look at:

{1,1,0,1,0,0,1,0} and {1,1,1,0,1,0,0,1}

Following the nature of the SUMPRODUCT formula, it now multiplies these two arrays together and adds those products:

1*1 + 1*1 + 0*1 + 1*0 + 0*1 + 0*0 + 1*0 + 0*1 = 1+1+0+0+0+0+0+0 = 2
[/FONT]
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top