# Lookups

M

#### Matt

I'm copying a VLOOKUP formula across a number of columns. How do I make the
formula col_index_num automatically change to the corresponding column cell
as I drag?

Ad

S

#### Sheeloo

If you want to start with col index 2 then use Column(B1) as the index...
when you drag it to the next column it will change to Column(C1) and give you
3, and so on.

M

#### Matt

Thanks Sheeloo,
I've either worded my post incorrectly or I might not be
understanding your instructions. I'm actually at the other end of the
formula. The column number which will contain the result I'm looking up.
VLOOKUP(\$B11,Positions!\$A\$9:\$AR\$28,5) It is the "5" I want to change to 6 -
7 and so on...

M

#### Max

If I wanted it to increment starting from say: 4,
as the VLOOKUP is copied across, then
I'd usually use: COLUMNS(\$A:A)+3

The "+3" would be the simple arithmetic adjustment
to suit the starting cell's col_index_num
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

S

#### Sheeloo

Instead of =VLOOKUP(\$B11,Positions!\$A\$9:\$AR\$28,5)
use
VLOOKUP(\$B11,Positions!\$A\$9:\$AR\$28,Column(E1)) and drag right...

Column(E1) will evaluate to 5, change to F1 which will evaluate to 6...and
so on...

M

#### Matt

Thanks Max,
Worked well for the first 10 columns, then gave REF errors?

Ad

M

#### Matt

Success. Thanks very much Sheeloo.

M

#### Max

Worked well for the first 10 columns, then gave REF errors?
That shouldn't be, unless the col_index_num exceeds your vlook's table range
Eg in B2: =VLOOKUP(\$A2,Sheet2!\$A:\$K,COLUMNS(\$A:A)+1,0)
When you copy B2 across, it'll return ok the values from Sheet2's cols B to
K. Beyond that it returns #REF! as the col_index_num would have exceeded the
vlook's table range: Sheet2!\$A:\$K. The correction to then apply, for
instance, would be to extend the vlook's table range beyond col K, eg:
Sheet2!\$A:\$Z

COLUMNS(\$A:A)+1 simply returns the series: 1,2,3,...
as you copy it across
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

M

#### Matt

Spot on Max. My array table was eceeded. Problem fixed.
Thanks very much.

Ad

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.