Hello,
if you are really interested in the number of matches only, then you may use a formula using following assumption:
LEADS: Range A2:A40
CALLED: Range B2:B20
Formula for number of matches:
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(B2:B20,"[()]","","g"),A2:A40,1,0)),0))
or using named ranges (which I’d prefer here):
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(CALLED,"[()]","","g"),LEADS,1,0)),0))
Note(s).
- I’m pretty sure there will be some guys here having a smarter formula
- The formula only works, if both columns do not contain any deviation from their respective format
- Use of
SUMPRODUCT
is only to force array function to work and to avoid {}
using CTRL+SHIFT+ENTER
but {=COUNTIF(ISERROR(VLOOKUP(REGEX(CALLED,"[()]","","g"),LEADS,1,0)),0)}
will work as well.
Update (OP states different format of column B in comment)
If column B contains a whitespace character after the area code you may use:
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(REGEX(B2:B20,"[()]","","g"),"\s","-",1),A2:A40,1,0)),0))
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(REGEX(CALLED,"[()]","","g"),"\s","-",1),LEADS,1,0)),0))
Another variant (avoidung nested REGEX)
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(B2:B20,"[()\s-]","","g"),REGEX(A2:A40,"[()\s-]","","g"),1,0)),0))