Lookups
 

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?
--
Cheers
Matt

Lookups
 

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.

--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Matt" wrote:

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?
--
Cheers
Matt

Lookups
 

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...
--
Cheers
Matt


"Sheeloo" wrote:

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.

--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Matt" wrote:

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?
--
Cheers
Matt

Lookups
 

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
---
"Matt" wrote:
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?

Lookups
 

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...
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Matt" wrote:

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...
--
Cheers
Matt


"Sheeloo" wrote:

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.

--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Matt" wrote:

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?
--
Cheers
Matt

Lookups
 

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


"Max" wrote:

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
---
"Matt" wrote:
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?

Lookups
 

Success. Thanks very much Sheeloo.
--
Cheers
Matt


"Sheeloo" wrote:

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...
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Matt" wrote:

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...
--
Cheers
Matt


"Sheeloo" wrote:

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.

--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Matt" wrote:

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?
--
Cheers
Matt

Lookups
 

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

Lookups
 

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


"Max" wrote:

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

Lookups
 

Welcome, glad that clarified it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik
---
"Matt" wrote in message
...
Spot on Max. My array table was exceeded. Problem fixed.
Thanks very much.
--
Cheers
Matt