I have an example where I have several tabs with e-mail addresses marked as World, Germany, Finland. Each tab has a list of email addresses.

Task: I want to find out if there are any addresses from the Germany and Finland tabs in the World tab and, if so, which ones. I don't want duplicates, so if an email address is already in the Germany or Finland tab, it must be removed from the World tab.

How to do it?

An example of the test is

CodePudding user response：

use:

=COUNTIF({INDIRECT("Germany!A:A"); INDIRECT("Finlnad!A:A")}; A1)>0

eg.:

=COUNTIF({INDIRECT("BA!A:A"); INDIRECT("BB!A:A"); INDIRECT("KE!A:A"); INDIRECT("NR!A:A"); INDIRECT ("PO!A:A"); INDIRECT("TN!A:A"); INDIRECT("TT!A:A"); INDIRECT("ZA!A:A")}; A1)>0