VLOOKUP query

**kirstywing** · 02-19-2009, 11:15 AM

If I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns but a pain for lots.

eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read
=VLOOKUP($D2,$AB:$AJ,1,FALSE), =VLOOKUP($D2,$AB:$AJ,2,FALSE), =VLOOKUP($D2,$AB:$AJ,3,FALSE), =VLOOKUP($D2,$AB:$AJ,4,FALSE)
etc

The value doesn't increase when you drag it.

is there a short cut for doing this automatically?

**sweep** · 02-19-2009, 11:22 AM

Hi,

You could use the COLUMN() function which will return 1 for A, 2 for B, 3 for C etc, so,

=VLOOKUP($D2,$AB:$AJ,COLUMN(),FALSE)

for example

**kirstywing** · 02-20-2009, 06:30 AM

Originally Posted by sweep

Hi,

You could use the COLUMN() function which will return 1 for A, 2 for B, 3 for C etc, so,

=VLOOKUP($D2,$AB:$AJ,COLUMN(),FALSE)

for example

thanks for the reply - if I do that though I still need to manually change the A, B, C etc as dragging the formula across columns doesn't change that part of the formula, so essentially I'm still manually typing it in one by one, just with letters instead of numbers

is there any way to get it to increase column by column as I drag the formula?

**DonkeyOte** · 02-20-2009, 06:46 AM

kirstywing, have you actually tested sweep's solution ?
I think not...

COLUMN() will return the column integer of the active cell...eg

A1: =COLUMN() --> 1
B1: =COLUMN() --> 2

Thus as you copy COLUMN() across so the integer increments accordingly.

I would say use of COLUMNS($A1:A1) may be preferable given COLUMN() is Volatile and COLUMNS is not but the effect remains the same and COLUMNS is perhaps easier to adjust as you can simply set $A1:A1 to be the first column in which you're executing the VLOOKUP (such that it returns 1 without need to adjust result)

**kirstywing** · 02-20-2009, 06:58 AM

Of course I tested it! but I maybe didn't understand exactly how to type it in.

I tried, for example =VLOOKUP($A2,$AB:$AJ,COLUMN(AB),FALSE), and dragging that across, and the AB didn't change as it was dragged.

I'm fairly limited in the formula commands I know how to use in excel (and I also didn't understand any of your last sentence!), so it might help if you could spell it out for
me exactly - how exactly would you type the full formula, if for example you want to returning the first column of a table that begins in column G, and then returns column H in the next column and so on?

**sweep** · 02-20-2009, 07:05 AM

Try using =column() in various cells (don't put anything in the brackets). It will return the column number of the cell you have entered it in.

**DonkeyOte** · 02-20-2009, 07:05 AM

You've not stated where you're posting the first VLOOKUP column wise... let's assume your first VLOOKUP is going into Column B

B2: =VLOOKUP($A2,$AB:$AJ,COLUMNS($A2:B2),FALSE)

the above would return column AC from your VLOOKUP, copied to C2 would return AD and so on....

An alternative would be to use INDEX/MATCH

B2: =INDEX(AC:AC,MATCH($A2,$AB:$AB,0))

copied across AC becomes AD and so on an so forth... this would be my preferred choice given less dependencies than a VLOOKUP.

(Also rather than using entire columns try to keep them as small as possible... so if you have 1000 rows of data use AC$1:AC$1000 / $AB$1:$AB$1000 etc....)

**kirstywing** · 02-20-2009, 07:19 AM

Thanks for your replies - I maybe shouldn't try to understand this while sick with a head full of cotton wool.

If you don't mind, I'll read through these reponses carefully when I think I can make a real attempt to understand them, and see if I can get it to work. I posted this query yesterday, but today my brain is not really geared up for learning somthing new.

I appreciate the help, and I'm sure when I can test it properly it will fix the problem.

**RooTFantastic** · 02-20-2009, 10:03 AM

i know im a new guy here... but from what i undertand.... the AB isnt changing cause you have the cell "locked" ( $ ). hit f4 on this part of the formula and make sure yo ahve no $ on the cell, then try again.

ex. $A$1 Locks cell A1
$A1 Locks columnA
A$1 Locks row 1

or i could be missing the whole point.

**DonkeyOte** · 02-20-2009, 10:07 AM

I think so... but a nice quick synopsis of absolute & relative referencing nonetheless.

In the latter examples provided AB must remain locked given this is the criteria column, what must alter is the results column -- toggling AB to relative is not going to have the desired result.... quite the opposite in fact.

**alski** · 02-20-2009, 10:28 AM

Hi Kirsty,

I've attached a file - please have a look at sample 1. I have used the column number as a reference to a cell so use the auto numbering feature to drag out the columns you want AB = 28 and then in the column number use a reference

A2 = 28

A3 = =vlookup(A1,Data Table,A2,False)

That should do it and wen you're done hide the row with your numbers in.

Any good?

Alex