## VLOOKUP

"Excel will adjust cell references in formulas when you insert or delete rows or columns.From a

For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

The problem then occurs withVLOOKUP. Its column index number argument is a simple number, not a reference.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

For Example:

You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B. In cell D1 enter the formula

- Choose a blank worksheet

- In cells A1 and A2, enter the values 1 and 2.

- In B1 and B2, enter Jan and Feb.

- Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.
=VLOOKUP(C1,A1:B12,2). Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name. To demonstrate the problem, right-click on the heading of column B and choose Insert. The formula changes to=VLOOKUP (D1,A1:C12,2), which returns. Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number. Press Ctrl-Z to undo the column insertion. The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references. You could use the0COLUMN()function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns:=VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1). A more esthetically pleasing, or sophisticated, function might be:COLUMNS(This returns the number of columns in an array or reference. The modified lookup function looks like this:array)=VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)). Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

**PC Magazine**article

*By Neil J. Rubenking*

Also: eHow:

**Using the Column Function**

OzGrid:

**Copy Rows**

**(**

*Scroll down to about the middle of the page*)

**See all**

**Topics**
## No comments:

Post a Comment