• Excel Book Excerpt

Excel Embed a Small Lookup Table In Formula

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.

Embed a Small Lookup Table In Formula

Problem: I have a small 5-row lookup table hidden out in column AA:AB. The sales reps who use the spreadsheet might inadvertently delete a row in their data, deleting the lookup table. Can I put the lookup table somewhere that they won’t destroy it?

Strategy: One solution is to move the lookup table to a new worksheet and hide the worksheet. However, if the lookup table is small, you can embed it right in the VLOOKUP formula. Follow these steps:

1. Select the cell with the VLOOKUP formula.

2. Press F2 to put the cell in edit mode

3. Select the characters that represent the lookup table.


Figure 461 Select the table array portion of the formula.

4. Press F9. This calculates the selected portion of the formula. In this case, it puts {“A",28;"B",35;"C",28;"D",14;"E",35} into the formula.


Figure 462 Press F9. Excel inserts the array into the formula.

5. Enter the formula. Copy it down to the other rows. You can now safely delete the lookup table.

It helps to understand Excel’s array syntax. The curly braces indicate that this is an array. Each comma means you should move to a new column. Each semicolon means that you should move to a new row.

Gotcha: It is difficult to later edit the table. You can try to puzzle it out by staring at the commas and semi-colons in the formula bar. Or, you can copy the array and put it back in the worksheet. You have to follow these steps:

1. Select the array from the formula, including the curly braces.

2. Ctrl+C to copy the characters from the clipboard.

3. Select a five-row by 2 column blank section of the spreadsheet.

4. Type an equals sign. Press Ctrl+V. Press Ctrl+Shift+Enter. Excel will put the array back into the worksheet as an array formula. This looks OK, but you can not edit individual cells in the range.


Figure 463 The table is back, but it is not editable, yet.

5. With the entire range selected, do a Copy and then Paste Values. You can now edit individual cells in the table.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: