Update the Destin URL to match the Sitemap URL that most closely matches the AdGroup (kw)

[thankyou]

Hi. Thanks for stopping by my post. I appreciate your time (and your genious of course).

Here's the setup: I have 3 columns (all others are hidden): "B" (Adgroup...think of this as "keywords"), "H" (destination url) and "AB" (sitemap urls).

Looking for a formula or array that I can paste into "H" that will take the existing url in "AB" that most closely matches "B" (see example in table below) AND the existing ".htm" (or .html if it's .html since these go to different pages) extension and rewrite just the destination URL portion in that same cell right there within "H" without changing anything else (even the ".htm" or ".html"). The matching sitemap url should also have the same ext (either .htm or .html) as in "H". A perfect match would be where a url within "AB" has all words match in order, no more or less, as column "B". Next best would be 1) singular or plural; 2) 1 extra word (on either side of the adgroup phrase) 3. etc.
Please note the sitemap will always have dashes added within their urls.

Example:

Column B	Column H	Column AB
(ad group column)	(Destination URL column)	(existing sitemap urls)
B2:B659	H2:H659	AB1:AB578
fresh apples	http://www.fruit.com/badURL1.htm?src=google	fresh-oranges.html
fresh oranges	http://www.fruit.com/badURL2.html?src=google	fresh-apples.html
fresh grapes	http://www.fruit.com/badURL3.htm?src=google	fresh-apples.htm
fresh strawberries	http://www.fruit.com/badURL4.htm?src=google	fresh-grapes.htm

<tbody>
</tbody>

You notice how column H urls have only 2 extensions: either .htm or .html

<tbody>
</tbody>

Same goes for the sitemap, Column AB, ie has those same 2 extensions (but, importantly, without the leading "http://www.fruit.com/" and without the trailing ?src=google . And sitemap urls will always have hyphens separating their "kw-rich-urls.htm" eg fresh-oranges.html or fresh-grapes.htm

Appreciate your help in creating a formula or array that I can paste down into some (not necessarily all) of the cells in column H, so that that dest URL changes update, live in that cell, to the *closest matching* url within our existing sitemap in column AB. I would like to keep columns the same for pasting back into editor.

In short, your formula or array will update the URL portion of column H's destination url so that it hunts for the sitemap url that contains the adgroup phrase (think exact match then phrase match, but never broad match), and ensuring the sitemap url found has the same extension as the existing destination url.

Thanks for your questions if you have any.

 

[chicagocomputerclasses]

="http://www.fruit.com/"&INDEX($C$2:$C$5,IFERROR(MATCH(SUBSTITUTE(A2," ","-")&"*.htm",$C$2:$C$5,0),MATCH(SUBSTITUTE(A2," ","-")&"*.html",$C$2:$C$5,0)))&"?src=google"

 

[thankyou]

="http://www.fruit.com/"&INDEX($C$2:$C$5,IFERROR(MATCH(SUBSTITUTE(A2," ","-")&"*.htm",$C$2:$C$5,0),MATCH(SUBSTITUTE(A2," ","-")&"*.html",$C$2:$C$5,0)))&"?src=google"

Thanks very much. I'll try that now ...

 

[thankyou]

ccc, Some questions:

1. when I try that in (with or without CSE), it shows #N/A
2. I was curious that I didn't see any of the ranges I provided in your array? I saw A's & C's? Thanks. Also I've updated the AB range from starting at "1" to "2" (I needed to add a header to that column to keep organized).

B2:B659 H2:H659 AB1:AB578

Please let me know if that changes anything, and also if CSE is nec.

Kind regards.
Thank you

 

[chicagocomputerclasses]

$C$2:$C$5 need to be replaced with your column AB range, like $AB$2:$AB$578
and A2 replaced with B2
this is not an array calculation, so just apply with enter and fill down.

not sure what you mean by CSE

 

[thankyou]

First, thanks! Oh, for your question, I just CSE whic sometimes is used to mean CTRL SHIFT ENTER after you past an array. I can't remember, but I think if there's an "=" sign at the start, you don't need it. Am I right or way off base? thanks.

Also, one other question, since this formula or array is to be placed in "H" column, remember one of the criteria is to preserve the ".htm" or ".html" within the "H" cell, finding a same-matching sitemap url that has the closest matching adwords group phrase WITH a matching extension to what's already in that H column. Will this require a modification?
Thank you sir. You're so appreciated.
It's super late here, and so I'll be back tom morning to check on your awesome help.
THANK YOU!

 

[chicagocomputerclasses]

The current formula will prioritize .htm over .html
Which extension do you want to preserve, the one matching the keyword line or the one matching the URL line?

 

[thankyou]

The current formula will prioritize .htm over .html
Which extension do you want to preserve, the one matching the keyword line or the one matching the URL line?

Preserve the existing extension within Column H. Please also include any add'l instructions needed, since, once I place the formula into certain "H" cells, it will immediately replace the existing cell contents, erasing, in this example, the old destination url including the extension that we want to preserve.We'd just need the formula to be able to "see" the extension already in place before the dest urls in H are replaced.

Also, something important, when I place the formula, I can see we're making progress. However, the main criteria, namely to find the closest-matching sitemap url (AB) that most closely matches the term in "B" is not working correctly yet. It's choosing the wrong sitemap urls. However, the final "form" within H is appearing.

So just these 2 points. Thank you very much for your help so far.

 

[thankyou]

you can ignore the sentence,

However, the final "form" within H is appearing.

. I meant, the formula is otherwise showing all the elements properly, ie https://www.domain.com/URL.htm(or.html)?src=google

 

[thankyou]

In order for the formula to see whether column H has an .htm or .html in its current destin URL, I can duplicate column H (eg on another sheet, and then copy all the values back in to my main sheet so that everything remains in position for when I reimport (paste) back into adwords.

Awaiting your response.