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

Advertisements

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

Advertisements

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

Advertisements


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.

Ask a Question

Similar Threads


Top