Search engine marketers deal with hundreds, if not thousands, of keyword buys on a daily basis. Client portfolios consist of creatives and keywords, which can become rather unruly as offers, products, and the websites themselves change. It’s kind of like a mother’s upkeep of her coupon collection – she has hundreds of categories, deals, and start and end dates to manage, while budgeting at the same time. I’m still not sure how my mom did it for 18 years!

When a searcher enters a keyword like “red SeeSawline stapler” into a search engine, they’re led to a search engine results page (SERP) filled with ads. Ideally, the searcher will click on an ad, which leads to a specific website. Imagine this: each keyword in a client portfolio leads to a specific URL, so each of those “landing pages” will have to be updated if any changes are made to that website.

This is where an amazing Excel formula, taught to me by my colleague Isaac Chinitz, comes in handy.

Let’s say that your keyword, “red SeeSawline stapler,” leads searchers to the URL http://www.fakestaplerstore.com/seesawline/xx_xxx=engine_blah&blah&blah&blah.

FakeStaplerStore.com recently made changes to their SeeSawline landing pages, so you have to update each landing page to http://www.fakestaplerstore.com/redseesawline in your portfolio.

To isolate your landing pages from a URL that includes codes at the end of your URLs (in this example, we want to remove xx_xxx=engine_blah&blah&blah&blah), highlight the column in which you’re working and bring up the Find and Replace box.

1. Enter ?xx_xxx* into the Find what section.
2. Leave the Replace with section empty.
3. Press Replace All.
4. You should be left with nothing but http://www.fakestaplerstore.com/seesawline/

This will remove everything after your landing page, starting at xx_xxx! Now you can Find and Replace anything that’s http://www.fakestaplerstore.com/seesawline/ with http://www.fakestaplerstore.com/redseesawline.

To isolate your codes from your landing page, highlight another column in which you’re working and bring up the Find and Replace box again.

1. Enter *? xx_xxx into the Find what section.
2. Enter xx_xxx into the Replace with section.
3. Press Replace All.
4. You should be left with nothing but xx_xxx=engine_blah&blah&blah&blah.

This will remove everything except for your landing page, starting at xx_xxx. Now you can concatenate your landing pages to the codes you preserved.

In search, the Excel vlookup function is used on a daily basis to synthesize massive amounts of data. Like men with women, we usually take vlookup’s merits for granted. We’re entirely dependent on its speed and accuracy, but when things go awry we feel hopeless.

Around here, ‘vlookup’ is a verb. We eat, sleep and vlookup.

If you have a list of keywords, each with numerical identifiers, and you need to attribute them to the correct redirect URL, enter the vlookup function like this:

=vlookup(C8, Report!$E$7:$K$327, 7, 0)

which translates to:

=vlookup(cell containing your keyword’s identification number, a range on another spreadsheet containing your redirect URLs and the keyword to which they match, the number of rows to the right of the keyword, and 0)

Problems can occur during the matching process. For example, if your data sets are in different formats, Excel won’t be able to match one to another, and will leave “#N/A” behind. In the search world, this is likened to a pile of $%($*&%&#. Even if you change the column format to text or general or numbers, nothing changes!

What may help in this particular situation is this pretty little formula:

=IFERROR((VLOOKUP((J8*1), [Report_1271326324069.xls]Report!$E$7:$K$327, 7, 0)),0)

This iferror formula converts all #N/A’s to 0’s, and multiplies the reference cells containing the numerical identifiers (J8) by 1, forcing it to be a number.