confused by COLUMN worksheet function

[Guest]

I have seen a worksheet containing the following VLOOKUP formula:

=VLOOKUP($C$27,$C$63:$N$82,COLUMN(B1))

This is a lookup table for discount factors, column C listing the discount
rates in % starting at C63. What is the purpose/result of using COLUMN(B1)??

 

[Peo Sjoblom]

It's for copying the formula across and get the lookup index column number
to increase, it returns the column number, in this case 2 for column B and
copied one cell to the right it will return 3 thus changing the column where
vlookup will pick the value from

 

[Biff]

Hi!

It's used to identify which column in the lookup table to find the value to
be returned.

COLUMN(B1) evaluates to COLUMN(2) so the result will be found in the second
column of the lookup table which is column D. Pay no attention to the number
1. In this application it has no meaning but it is necessary as part of the
COLUMN argument if using a cell address as a reference. Column(B100) also
evaluates to COLUMN(2). It could also have been written like this:
COLUMN(B:B)

The COLUMN function is also being used as a means of incrementing.

If the formula is drag copied to the right the COLUMN letter will increment:

COLUMN(C1) > COLUMN(D1) > COLUMN(E1) etc.

That in turn changes which column of the lookup table to find the value to
be returned.

COLUMN(C1) = column 3
COLUMN(D1) = column 4
COLUMN(E1) = column 5

Biff

 

[JE McGimpsey]

It's to allow the formula to be dragged across and have VLOOKUP return
values from successive columns. For instance, if the formula was copied
one cell to the right, it would read

=VLOOKUP($C$27,$C$63:$N$82,COLUMN(C1))

which would return the value form the third column of the lookup range
(e.g., column E) rather than the 2nd.

However, this implementation has a potential danger: If a column were to
be inserted before column B, then XL would automatically adjust the
formula to

=VLOOKUP($D$27,$D$63:$O$82,COLUMN(C1))

which would return the value from the wrong column.