MrExcel.com - Your One Stop for Excel Tips & Solutions

Calculation Bug When Changing VLOOKUP Table

February 23, 2018 - by

Calculation Bug When Changing VLOOKUP Table

There is an odd bug that can cause calculation errors in Excel when you make changes to the lookup table. Given that the Excel team's motto is "Recalc or Die", I am not sure why they won't patch this bug.

The figure below shows a VLOOKUP formula in column C. It is looking up the item in B, returning the 4th column from the orange lookup table. Everything is fine at this point.

A typical VLOOKUP function.
A typical VLOOKUP function.
Excel is fast thanks to a Intelligent Recalculation algorithm. In this case, the algorithm is opting not to recalc cells that need to be calculated.

If someone inadvertently deletes a column or inserts a column in the lookup table, an odd thing happens.

Insert column H and the worksheet only partially recalcs.
Insert column H and the worksheet only partially recalcs.

What is going on here? It looks like:

  • The formula in C2 is dependent on columns F:K so it recalculates. We've screwed things up because the VLOOKUP is still returning the 4th column of the table. This gives us Color instead of Price and makes the Total formula in D2 fail.
  • Now, if I were the Excel Recalc Engine and if I was sentient and if I had a personality, I might say to myself, "Hmmm. The value in C2 changed. Perhaps I should go recalc any other identical formula in this column." That thought would cause me to recalc C3, C4, and C5. But Excel does not recalc those cells. It doesn't have anything to do with the error in D2. Even without the formula in D2, the formulas in C3, C4, and C5 are not calculated at this point.
  • Cells C3, C4, and C5 remain wrong until you press Ctrl + Alt + Shift + F9 for a full recalc.

Don't get me wrong. I love VLOOKUP. But the people who complain about VLOOKUP would suggest using a MATCH as the third argument in VLOOKUP to handle this situation.

Add a match formula as the third VLOOKUP argument.
Add a match formula as the third VLOOKUP argument.

If you use the formula above, the recalc problem will not appear.

I've let the Excel team know about this bug, but they oddly have no priority to fixing the problem. It has been around since at least Excel 2010.

Every Friday, I examine a bug or other fishy behavior in Excel.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"The only thing better than VLOOKUP in an Excel spreadsheet is everything"

Title Photo: Isabelle Portes / pixabay