How do i hlookup with the "row index number" changing

[sokevin]

i want to combine 2 worksheets, so i want to do a hlookup


the headings of the colums are the same but are ordered differentl
(from a data dump) thats why i can't do a straight copy and paste.

i need my formula to do something like this

Row 1 contains the column headings

Row 2 to 1999 contains data from the 1st file

Sheet1 contains data from the 2nd file.


a2000=HLOOKUP($A$1,Sheet1!$A:$AJ,2,FALSE)
a2001=HLOOKUP($A$1,Sheet1!$A:$AJ,3,FALSE)
a2002=HLOOKUP($A$1,Sheet1!$A:$AJ,4,FALSE)

notice the row index number changes

how do i create a formula where the row index number +1 for eac
iteration?


thank

 

[Biff]

Hi sokevin!

Use the ROW() function as the argument for the row index
number. If your first formula looks for row 2, try:

=HLOOKUP($A$1,Sheet1!$A:$AJ,ROW(A1)+1,FALSE)

Then, as you copy down the ROW(A1) will increment to ROW
(A2) etc.....

Biff

 

[SB]

Suggest that you don't hard code the numbers 2,3,4,etc into the
formulae.

Why don't you put those numbers in a free column, then refer to that
column from the formula.

Stuart