Cross Reference Formula

Heather Druckenmiller

I am looking for a formula that will cross reference between 2 sheets. I have 2 sheets, 1 sheet is the master data. I want to cross reference so when something is input on my 2nd sheet that it goes to the Master sheet and determines if the Customer number and the Email exists on the master data and inputs Yes or No. The formula I currently am using is only checking the email address I need it to also check for the Customer #.

=IF(COUNTIFS({2. Tax ID Input sheet Range 1}, @cell = [Email Address]@row) = 0, "No", "Yes")

Robert S Fike

You could create two helper columns. One has the formula to check for the email and leave 1 for yes and 0 for no. The other checks for the number and give the same response. Then have a third helper that sums this up and leaves the response desired.

Not sure if this is what you had in mind.

MarceHolzhauzen

Hey @Heather Druckenmiller

Is this a OR check or an AND check? Do you want to see if either of the two exist or if both exist for the same customer?

To make this easy for yourself, i would create 2 helper columns in your current sheet:

1. E-mail Lookup
2. Customer ID Lookup (or other names that makes sense to you)

Then, add the following formulas into them:

1. =COUNT(COLLECT({Master Sheet Customer ID Column}, {Master Sheet E-mail Column}, [Current sheet E-mail Column]@row))
   
   This will count the number of CUSTOMER ID's that have this e-mail address appearing next to it on the Master Sheet. Theoretically you should receive a 1 or a 0 back, unless the e-mail is used with multiple customers.
   (Also assuming that you do not have blank customer ID's)
   
2. =COUNT(COLLECT({Master Sheet Customer ID Column}, {Master Sheet Customer ID Column}, [Current sheet Customer ID Column]@row))
   
   This will count the number of CUSTOMER ID's that match with the current one on the Master Sheet. Theoretically you should receive a 1 or a 0 back, unless the Customer ID is duplicated. (Also assuming that you do not have blank customer ID's)

Depending on your need, you could potentially add a 3rd column called "Duplicate Check" - using the symbol type (with flags)
And in this column you can say if([E-mail Lookup]@row+[Customer ID Lookup]@row>1,1,0) what this will do is flag all of the entries that have either an email or customer ID that is exists in the Master sheet.

If you need an in person session for advice, use the link in my signature to book a quick session :-)