Microsoft Office Tutorials and References
In Depth Information
Using COLUMN to Assist with VLOOKUP When Filling a Wide Table
after copying the formula over to D:N, you have to edit the third ar-
33. Create a range above your table, perhaps in row 4, that contains the
numbers 2 through 13. You can then use cells in this row when building
the third argument in the formula. In cell C4, enter the function
=COLUMN(B2). Because column B is the second column, this formula
44. Select cell C4. Drag the fill handle to the right to copy the formula
through column N. The cell B2 reference is relative, resulting in the
formula returning the numbers 2, 3, 4, and so on.
55. In cell C6, enter =VLOOKUP(B6
=VLOOKUP(B6. When you later copy this formula,
you always want the formula to point to column B, but you want to
allow the formula to point to rows 7, 8, and so on. If you press the F4
key three times, the reference changes to $B6. Type a comma.
66. Type A21:M176
A21:M176. Press F4 to change this reference to $A$21:$M$176.
Type a comma.
77. For the third argument, you want to point to the number 2 in cell C4.
You always want this part of the formula to point to row 4, and you
want to allow the column letter to change as the formula is copied to
the right. Press the F4 key twice to change the reference to C$4.
88. Finish the formula with ,FALSE)
,FALSE). Press Ctrl+Enter to accept the
formula and stay in cell C4.
99. Optionally, add a conditional format to cell C4 to highlight the cell
if this formula is true: =C6>=$A6.
10. Double-click the fill handle to copy the formula to C4:C13.
11. Drag the fill handle from the corner of C13 to the right until you
have filled in the formula in the range of C:N.
The result is a table that shows the current inventory for each item, by
warehouse. If you added the conditional formatting in step 9, you can quickly
see which warehouses can fulfill most of the order.
Although having the COLUMN function in row 4 enables you to visually un-
derstand the example better, you can eliminate row 4 and rewrite the formula
in cell C6 as =VLOOKUP($B6,$A$21:$M$176,COLUMN(B1),FALSE).