Vlookup increment No

[Yossy]

I have multiple column that I want to apply vlookup to. Is it possible to
automatically increase the no. E.g
=Vlookup(c5,B:J,2,false), pick second row
=Vlookup(c5,B:J,3,false), pick third row
= lookup(c5,B:J,4,false), pick fourth row
=Vlookup(c5,B:J,5,false), pick fifth row
can I while dragging the filler automatically increase the 2,3,4,5 e.tc
across multiple cell without manually changing them.

All help totally appreciated. Thanks

 

[Max]

=Vlookup(c5,B:J,2,false)

Replace the above with this equivalent:
=VLOOKUP($C5,$B:$J,COLUMNS($A:A)+1,0)

Then you can simply copy it across by 3 cols to return

=Vlookup(c5,B:J,3,false)
=Vlookup(c5,B:J,4,false)
=Vlookup(c5,B:J,5,false)

The incrementer term used for the col index num is:
COLUMNS($A:A)+1

Exact matching "FALSE" can be replaced by 0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

 

[Shane Devenshire]

Hi,

You can replace your formula with

=VLOOKUP($C5,$B:$J,COLUMN(A1),0)

or if your first formula in in column A:

=VLOOKUP($C5,$B:$J,COLUMN(),0)

or if it is not in A but say C:

=VLOOKUP($C5,$B:$J,COLUMN()-2,0)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Yossy]

Thanks Shane and Max. This really helps!!! and Yes I clicked the Yes button

 

[Max]

Welcome, Yossy, and thanks for the rating!
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik