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.