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.

Answer 1

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)