Vlookup a URL with Domains only

Jake's Marvelous Formula
=VLOOKUP("*"&A2&"*",Sheet1!A:D,4,0)

Note – make sure the quote marks are redone once in excel, as if WordPress formats them, it will stop the vlookup working

Download an example spreadsheet here

For example, if you want to look up from sheet 4 – which has a list of domains

and find on sheet1 – the relevant comment from a disavow

And Sheet 1 is full of URLs, and sheet 4 is domains, not individual URLs

=VLOOKUP(“*”&A2&”*”,Sheet1!A:D,4,0)

Should work to find the correct comment for your disavow!

e.g. you have:

3pr.co.uk

On Sheet 4, in cell A2

But in Sheet 1, the relevant comment is listed next to:

http://3pr.co.uk/2014/02/25/business/article-post-today/

The wildcard look up will be able to match 3pr.co.uk with http://3pr.co.uk/2014/02/25/business/article-post-today/

*Caveat – this doesn’t seem to work with domains which contain numbers.
For example
Using this formula to find

http://www.britain1234.com

And return the comment for

http://www.britain1234.com/wales/ua/Neath%20Port%20Talbot-38/Baglan-1378/directory/catering-companies-204/

Didn’t work

I also use this for Disavows

– once I’ve got the unique domains, I’ll then pull through an example ‘full URL’ to review.

Note to self – make sure you’re looking up in the right tab!

 

If you want to return a URL, just from a list of URLs in Column of Sheet1, use this forumla:

=VLOOKUP("*"&A2&"*",Sheet1!A:D,1,0)