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

Add New Items to the Middle Of Your Lookup Table

April 08, 2022 - by

Add New Items to the Middle Of Your Lookup Table

Problem: I have to add BG33-9 to my lookup table. When I enter it in row 31, the #N/A error does not go away.

Strategy: You would have to rewrite the VLOOKUP to point to $L$3:$M$30. Instead, you could use any of these clever strategies:

  • Insert new cells anywhere in the middle of your lookup table. For example choose L7:M7 and do Alt+I+E followed by Enter. This will Insert Cells and shift the remaining items down.
  • Specify L:M as the lookup table. This uses the whole column as the lookup table. Now, you can add items to the bottom without rewriting the formula. Excel is smart enough to only use the non-blank cells when calculating.
  • Ctrl+T the lookup table before you add new values. When you type new values in row 31, the table expands to include the new row. In one of those scary bits of Excel magic, they actually rewrite your formulas to point to the extra row in the VLOOKUP formula. This happens even if you are not using Table Formula Nomenclature.

This article is an excerpt from Power Excel With MrExcel

Title photo by Raban Haaijk on Unsplash