Excel Cruft

Here’s a long ugly formula.

=IFERROR(IFERROR(IF([@wiffle]="","",VLOOKUP([@piffle],kerfuffle!A:R,4,FALSE)),VLOOKUP([@wiffle],waffle!A:D,3,FALSE)),"")

This is a real formula. I’ve changed all the private information with silly words because the world lacks whimsy. Someone who isn’t well versed in Excel might think this is a complex formula and while it is a complicated formula it doesn’t need to be. Let’s break it apart.

The IFERROR function has two arguments (value, value if error). In this case, it returns a blank (“”) if the first formula returns us an error. Easy enough.

Lets remove it and see what’s left:

=IFERROR(IF([@wiffle]="","",VLOOKUP([@piffle],kerfuffle!A:R,4,FALSE)),VLOOKUP([@wiffle],waffle!A:D,3,FALSE))

Another IFERROR! Bastards. This IFERROR’s first argument is:

IF([@wiffle]="","",VLOOKUP([@piffle],kerfuffle!A:R,4,FALSE)) 

In plain English, if the current row in the current table is blank then return blank. That blank value returns up to both IFERRORS the result of the whole formula is blank. If the current row of the current table is not blank, then lookup in another sheet the entire sheet column A:R and return the 4th column.

Here are some problems with that; Excel is being told here to look at approximately 1 million rows of data for the first match. It’s also including A:R and then only going to the 4th column (the 4 in the VLOOKUP), why did you grab A:R? Because someone merged columns and now the formula includes data it doesn’t need to.

The second argument in the IFERROR is another VLOOKUP:

VLOOKUP([@wiffle],waffle!A:D,3,FALSE))

This is to have a backup VLOOKUP in case the value of the current row in the current table is not found with the first VLOOKUP (remember, this only takes place when the first VLOOKUP evaluates to an error).

So a few options, some formula improvement and some general workbook design.

Combine your tables so you don’t have single formulas with 2 lookups, that is honestly best to avoid as often as possible. But if you can’t, and sometimes you can’t, there is a “new” formula that’s only been out for 5 years that would simplify this a great deal.

XLOOKUP - it has error handling inside of it. So instead of the monstrosity you could have

=xlookup(@piffle,kerfuffle!A1:A1000,kerfuffle!D1:D1000,xlookup(@wiffle,waffle!A:A,waffle!C:C,""))

and you would have the same result. This would be even shorter and clearer if you had table references and even clearer than that if you combined your lookups so that they could reference a single table.