Formula or VBA: Update column H with the closest-matching column AB URL to Column B

We have some adwords destination urls that need updating. The sitemap, on the other hand, is up to date with kw-rich-urls. We want the destination urls in column H to be updated with the closest matching url in the sitemap (column AB) that best matches the kw (well, ad group--same thing here) in column "B". Here's an example using some generic fruit:

screenshot of table.png

OVERVIEW: We need to update our destination urls (Column H) within Adwords, and we want to do that with the far more accurate kw-rich, existing SITEMAP urls (Column AB)

Something that may or may not be pertinent re formula vs VBA: the destination URLs that need updating are: H170:H580 and H635:H659, in other words a lot of the cells in H already have a good destination url, so we don't want to undo those if at all possible. Also it's ok to make an additional working column for "H" if necessary, and we can copy the values back over to H later. Here's the criteria we're looking for in the following order for "CLOSEST MATCHING" url in AB to the kw in B. The OUTPUT should ideally replace the current urls in H with the closest matching sitemap url to the kw in B, and keep the general format of the urls shown in H.

RULE 1--We want the formula to find the existing sitemap url that's the CLOSEST MATCH to the kw (ad group) in Column B

RULE 2 -- "Closest match" criteria to use:

a. BEST MATCH = exact match
b. NEXT BEST MATCH (nbm) = exact match but with or without the plural being the difference
c. NEXT BEST MATCH = PHRASE match (using Google's definition we all know), eg term = 1-2, so 1-2-3 or 0-1-2-3 might be closest matching sitemap url
d. nbm = Phrase with plural variants

RULE 3--NO MATCH using any of the above criteria: In those rare cases, use this landing page we’ll provide you later.

RULE 4--"What if I have 2 sitemap urls that are identical, but one has .htm and the other .html?

Answer: Choose the .html one.

Questions are welcomed. Thank you.

Sample table attached, thank you.

By the way, this text field is very hard to work with. Maybe you can fix the drag to width tool in the lower right of this text field to include horizontal adjustment, and not just vertical {ENTER}

I'm back now.

Thanks.

IO4IZE