How to find data in Sheet1 and list them in Sheet2

[Michael]

I have the following range of Data in Sheet 1

Table 1 Table 2
A A1
B B3
C C4
D D1.1

The problem is that I don't know exactly in what column "Table 2" might
exist. It can be in A1, B1, C1, D1 etc ....

In Sheet2 A1 I want to create a formula that achieves the following:

Search Sheet1 range A1: Z 10000, find the heading "Table2" and then copy all
of the items below Table2 in cells Sheet2!A2:A1000.

Any ideas how to do this? I tried using a combination of Vlookup and Hlookup
functions with no luck.

Thanks
Michael

 

[Ian]

Try this in A2 on sheet 2, then copy it down to A1000

=HLOOKUP("Table 2",Sheet1!$A$1:$Z$1000,ROW(),FALSE)

This will only work if you want the data in eg row 2 on sheet 1 to be in row
2 on sheet 2. If not, you'll need to add/subtract an offset to ROW() as this
returns the row number it is in.

Ian

 

[Michael]

Thanks! This worked just great!

-- Michael

Try this in A2 on sheet 2, then copy it down to A1000

=HLOOKUP("Table 2",Sheet1!$A$1:$Z$1000,ROW(),FALSE)

This will only work if you want the data in eg row 2 on sheet 1 to be in
row 2 on sheet 2. If not, you'll need to add/subtract an offset to ROW()
as this returns the row number it is in.

Ian

 

[Ian]

Glad I could help. Thanks for the feedback.

Ian

Thanks! This worked just great!

-- Michael