Column number on Vlookup

**smokeydawson555** · 08-11-2009, 02:41 AM

Hi,

I am using the vlookup formula and copying and pasting it into some more cells to the right.

However when I do this the column number does not automativally change up one number. Is there a way I can make it do this?

Any help would be greatly appreciated.

Cheers,

Smokey

**mrice** · 08-11-2009, 02:47 AM

You can use the CELL function using the optional reference argument to do this.

e.g.

=VLOOKUP($I4,$A$3:$E$7,CELL("col",J4)-8,0)

pasted into J4.

**DonkeyOte** · 08-11-2009, 03:13 AM

CELL is a Volatile function so better I think to use standard approach of COLUMNS:

J4: =VLOOKUP($I4,$A$3:$E$7,COLUMNS($I4:J4),0)
copied across

(assuming J4 is the first cell - alter ranges accordingly)

**smokeydawson555** · 08-11-2009, 06:53 PM

Hi,

Thanks for your help, but I am still confused, and thinking that I did not ask the question well.

My formula is =+VLOOKUP($B$1,'Data Entry'!$A$2:$L$1000,2,FALSE)

All of it is working how I want it to except for the column number it reports back. When I past this into cells to the right I want the '2' to go up one number each time. What is the best way to do this?

Thanks

**Cheeky Charlie** · 08-11-2009, 07:27 PM

What DO suggested is the best way - but to be fair we are in "New Users"...

The only way you will be able to make this work is to understand what we're doing...
take this:
=VLOOKUP($B$1,'Data Entry'!$A$2:$L$1000,COLUMNS($I4:J4),FALSE)
Argument 1:
We look for B1's value in
Argument 2:
the range A2:L1000 on sheet "Data Entry" (aside: you know you can point a vlookup at a whole load of columns by selecting across the tops of the columns - that is, on the letters - so it would look like ...'Data Entry'!$A:$L,... much easier to read, no? end aside)
Argument 3:
tells us which column of the second argument to return data from (1 corresponds to "the column you found the first thing in)
Argument 4:
If false, tells Excel to find an exact match, and that the values are not necessarily in ascending order (incidentally, if you can keep values in ascending order, you can leave this argument out - Excel defaults it to true - Excel will function slightly better)

So... getting to argument 3:
We need a number which gets bigger as you copy the formula across sideways - COLUMNS(<range>) counts the number of columns in a range - note the single '$' (very important). The $ in front of the I fixes that part of the range (always from I), whereas the J4 part of the reference will adjust with the formula - the total effect of this is:
1st column:
COLUMNS($I4:J4)
there are two columns from I to J
2nd column
COLUMNS($I4:K4)
there are three columns from I to K
etc...

so what we have is a bit of "formula" which creates an ascending number as you copy across sideways.

Simples

CC