Vlookup increment No

Y

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
 
M

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

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
 

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

Top