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

  • Thread starter Thread starter sokevin
  • Start date Start date
S

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
 
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
 
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
 
Back
Top