Excel Magic Trick 1099: Compare 2 Lists with semi-colon discrepancies, Excel Table For Dynamic Range

See how to compare two e-mail lists where one list has extra semi-colons and we want a dynamic formula that can handle periodic data dumps.
1. (00:08 min) Setup of problem
2. (00:46 min) Excel Table Feature to create dynamic range.
3. (01:42 min) Is in List: MATCH, ISNUMBER and SUBSTITUTE functions, Table Formula Nomenclature (Structured Table References), non-array formula.
4. (03:37 min) Is in List: MATCH, ISNUMBER and SUBSTITUTE functions, Table Formula Nomenclature (Structured Table References), Yes: ARRAY FORMULA.
5. (05:59 min) Test the new data dump: Contract and Expand Excel Table using Mouse.

Compare Two Email Lists. Is Email in List? Is email NOT in List? Compare 2 E-mail Lists. Is E-mail in List? Is e-mail NOT in List? Compare 2 Lists with semi-colon discrepancies

[excelisfun]

Excel Magic Trick 1099: Compare 2 Lists with semi-colon discrepancies, Excel Table For Dynamic Range 

[krn]

Cool, don't think I knew about clicking and dragging up a TABLE like that.  Thanks Mike.

[geordieking]

Hi great use of Match ... but I would have used a countif to show when the email is found in the list (ie >0)
Is match a better way to do it?

[BillSzysz]

Great as always! :)
I think, we do not need SUBSTITUTE in this case (column D)
=ISNUMBER(MATCH(C2&"*", EmailL2Answer[E-mails List 2], 0))
is sufficient.;))
Happy Easter, for you and your family ! :))))