Lookups

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

 

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

 

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

 

[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

 

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

 

[Matt]

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

 

[Matt]

Success. Thanks very much Sheeloo.

 

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

 

[Matt]

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

 

[Max]

Welcome, glad that clarified it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik