horizontal lookup/fill

[pfchang]

Hi,
below is an example of an array on a sheet:

A B C D
bob 1 2 3
joe 7 8 9

right now i'm using vlookup in a second sheet to lookup "bob" and the corresponding values in the columns from the first sheet. ex: =vlookup($A1,sheet1!$A:$D,2,0), A1="bob" in the second sheet.

i want to be able to drag that formula across to column D and have the column index go up sequentially(2, 3, 4) so that i get the same values as the first line in the first sheet. is vlookup able to do this? if not, is there another formula i can use? is there a symbol/formula i can enter in place of the row index that will move it sequentially? seems like i've been using vlookup for far too long and i need to venture out to some formulas that might be more efficient. any suggestion is appreciated.

thanks.

[martindwilson]

replace the column number with
COLUMNS($A$1:A1) (thats = to 1)
columns simply counts the cols in a given range when dragged across it will change to
COLUMNS($A$1:B1) Ie 2
COLUMNS($A$1:C1) ie 3
so in your case starting from 2 use
COLUMNS($A$1:B1)

vlookup($A1,sheet1!$A:$D,COLUMNS($A$1:B1),0)
note you dont have to use the columns you're in

COLUMNS($BA$1:BA1) for example would also = 1 no matter where you use it on the sheet
you can also do the same with rows() on dragging down
rows($A$1:A1) =1
changes to
rows($A$1:A2) =2

[HammerTime]

pfchang,

try this:

=vlookup($A1,sheet1!$A:$D,COLUMN(),0)

However, beware of the COLUMN() function, it returns the column number where the formula is typed and hence is not a relative reference. If you are using it in other situations it would be wise to check what it is returning before inserting into a larger formula such as the one above.

[martindwilson]

trouble with column() is you have to adjust it
so if you are in col g it will return 7 so to get 2 you have to use column()-5
a bit of a pain really and would put the formula out if you inserted a column in the wrong place

[HammerTime]

trouble with column() is you have to adjust it
so if you are in col g it will return 7 so to get 2 you have to use column()-5
a bit of a pain really and would put the formula out if you inserted a column in the wrong place

martindwilson,
Absolutely, I agree! Hence the warning at the bottom of my post.

pfchang,
I have to defer to the Forum Guru on this, his solution is clearly more robust than mine. But now we have both learnt something

[pfchang]

Awesome! Thanks so much! that cuts down a big chuck of time.