Excel Add New Items to the Middle Of Your Lookup Table
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.