Reference a range using column number

Question

I'm looking for something rather simple, but I've been trying to do it for quite some time now. I need to get values from a column.

Lets say I have several columns with headers and I need to pull data from a column with a specific header. I can easily get it's number by using MATCH to look through the row with headers. Now that I have number - I need to pull say 10 cells from this column right below the header.

For example if headers are in the first row and the one I need is in the E column then I need to pull E2:E11. And I have 5 as the column number resulting from MATCH function. I would use OFFSET as it does pretty much what I need with values like this OFFSET(A1;1;MATCH(blablabla);10;1). And it works just fine, however I need to do this from a closed workbook and OFFSET only works with currently opened ones.

There's got to be a simple solution, I can't believe Excel provides a function like COLUMN but not the reversed solution. For the sake of people using the resulting workbook I can't really switch to R1C1 view but if that would be my only option, I guess that will have to do.

Please advice what I could do in this situation.

Solution

Try using INDEX, that should work OK with a closed workbook, syntax would be like this:

=INDEX([Book1.xls]Sheet1!$B$2:$Z$11,0,MATCH("x",[Book1.xls]Sheet1!$B$1:$Z$1,0))

That will find "x" in B1:Z1 of book1 sheet1 and then give the 10 cells below (in rows 2 to 11)

Comments

[waterplea]

Great! Thanks, that worked. I didn't know you can return entire column with row_number = 0 :) Now I just need to figure out how to make that B2:Z11 to cover all area that has any value i.e. select everything from 2nd row to the last one and from first column to the last. By any chance you might know a solution to this task too? :)

[waterplea]

Yes, thank you. The current question is closed, I think I'll try to figure the other one myself first and ask it tomorrow if I will not be able to come up with a solution on my own :)