It's day one of VLOOKUP Week and I'm thrilled to kick things off with a guest post from Bill Jelen, aka MrExcel, discussing the benefits of using a hidden helper row in Excel!
The VLOOKUP function is one of the more powerful functions. Intermediate and advanced users of Excel will often use VLOOKUP to return a value from a lookup table. There are certain challenges that you face when you need to return all of the columns from a lookup table. You can carefully craft the first formula, copy it across your worksheet, but then you still have to edit each individual formula. You can avoid this problem by using a hidden helper row in Excel.
Figure 1 shows a product lookup table. While the middle rows have been hidden, you can see that the table runs from L2 to Q843. The table is designed to look up an item number and return various fields, such as description, warehouse, list price, and so on.
Figure 2 shows the beginning of a price quotation model. Quantities and item numbers have been entered in columns A & B. You need a way to use the item number and the lookup table to return information about the selected item numbers. The answer, of course, is VLOOKUP.
The general syntax for VLOOKUP is:
=VLOOKUP(item number, lookup table range, which column #, False)
• Item number is the cell containing the item number, such as B6.
• Lookup table range is L2:Q843
• Which column # would be column 2 in order to return the description from the second column of the lookup table
• False – the fourth argument indicates that you are not doing lookup where the first column contains numeric ranges of values
You might consider a formula such as =VLOOKUP(B6,L2:Q843,2,False). While this formula would work for cell C6, the formula would not work when you attempt to copy it to the other cells in your model. If you copied this formula over to cells D6:G6, the first two arguments would shift to the right, producing incorrect results. The solution is to add dollar signs to the cell references as you enter the formula. A dollar sign before a column letter will lock the reference to always point to that column. A dollar sign before the row number will lock the reference to always point to that row.
In the formula, after the B6 but before the comma, press the F4 key three times. The F4 key will add dollar signs to the adjacent cell reference. The first press of F4 will lock both the row and column: $B$6. The second press of F4 will lock only the row: B$6. The third press of F4 will lock the column $B6. Using a single dollar sign in $B6 allows the row number to change as you copy the formula down your model, but makes sure that you are always reaching back to column B to lookup the item number.
Use F4 to change L2:Q843 to $L$2:$Q$843. This will ensure that the second argument always points to the complete lookup table. If you left the dollar signs off and copied the formula down, the copied formulas would point at a table starting in rows 3, 4, 5, and so on, potentially missing items at the top of the table.
Problem: You will have to edit the third argument as you copy
After entering the formula =VLOOKUP($B6, $L$2:$Q$843,2,False) in cell B6, try copying it to cell C6. The same description that appeared in B6 will appear in C6. Press F2 to edit the formula. The problem is the third argument is still returning column 2, as shown in Figure 3. You need to edit this formula to change the 2 to a 3 as shown in Figure 4.
As you continue to copy the formula across the columns of your model, you will have to edit each formula, changing the third argument to a different column number in each formula.
Solution: Use a helper row, then hide the helper row
In Figure 5, the values 2, 3, 4, 5, 6 have been entered in the blank row 3 of the model. The numbers indicate in which lookup table column the answer appears. In this particular case, the fields in the lookup table are in the same sequence as you want them to appear in the model, but if they were in a different sequence, you could easily enter 2, 4, 5, 6, 3 to return the UOM column last.
In cell C6, enter the formula =VLOOKUP($B6,$L$2:$Q$843,C$3,FALSE). The third argument C$3 uses a single dollar sign before the row number in order to make sure that the formula always points back to row 3 to retrieve the column number.
Note that this formula uses all three types of absolute references: the $B6 locks the column, the C$3 locks the row, and the $L$2:$Q$843 locks both the row and column. As you copy the formula from B6 to the rest of the rows and columns in your model, the formula will continue to work.
Figure 6 shows the copied formulas, with cell D8 in edit mode. The third argument of the formula in D8 reaches back to D$3 to retrieve column 3 from the lookup table.
Hide the helper row
I can already hear your manager complaining that he or she doesn’t want to see those distracting red numbers in row 3 of the model. Select C3:G3 and change the font color to white as shown in Figure 7. This will prevent the numbers from printing, but will allow you to see the numbers if you select row 3.
Bill Jelen is the author of Excel 2010 In Depth. This article appears as part of VLOOKUP Week March 25-31, 2012.