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-

gument repeatedly.

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)

=COLUMN(B2). Because column B is the second column, this formula

returns 2.

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.

10. Double-click the fill handle to copy the formula to C4:C13.

11.

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).