dimanche 31 mai 2020

Using regextract and importrange together make a "duplicate" formula

I have a spreadsheet where I look if the data (website) already exist on the master sheet.

=if(countif(importrange("Spreadsheet Key","Leads!N:N"),K2)>0,"COMPANY EXISTS!","")

But the above formula is not dynamic enough. If there are companies with co.uk and on the master sheet if it's registered under .com, it won't show "COMPANY EXISTS!"

So I changed to the formula to look for works after before and after "." on a website.

=ARRAYFORMULA(REGEXEXTRACT(UNIQUE(SUBSTITUTE(importrange("Spreadsheet Key","Leads!N:N"),"www.","")), "([0-9A-Za-z-]+)\."))

But it's not working if I try to incorporate with if and countif.

=if(COUNTIF(ARRAYFORMULA(REGEXEXTRACT(SUBSTITUTE(importrange("Spreadsheet Key","Leads!N:N"),"www.",""), "([0-9A-Za-z-]+)\."))>0,"Company Exist!",""))

It shows 'Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 1 arguments'

Can anyone help me out on where I am making the mistake?

Spreadsheet link- https://docs.google.com/spreadsheets/d/1La3oOWiM5KpzRY0MLLEUQC25LzDuQlqTjgFp-VlS8Bo/edit#gid=0

Aucun commentaire:

Enregistrer un commentaire