Range reference using column number

Question

I am looking for something rather simple, but I have been trying to do this for quite some time. I need to get values ​​from a column.

Suppose I have several columns with headings, and I need to pull data from a column with a specific heading. I can easily get it using MATCH to view the title bar. Now that I have the number, I need to output 10 cells from this column right below the heading.

For example, if the headers are in the first row, and the one that I need is in column E , then I need to press E2: E11 . And I have 5 as the column number as a result of the MATCH function. I would use OFFSET , since I need a lot of it with values ​​such as OFFSET (A1; 1; MATCH (blablabla); 10; 1) . And it works very well, however I need to do this from a closed book, and OFFSET only works with open at the moment.

There should be a simple solution, I cannot believe that Excel provides a function like COLUMN , but not the opposite solution. For the sake of people using the resulting book, I can't switch to the R1C1 view , but if that is my only option, I think it will need to be done.

Please advise what I could do in this situation.

Answer 1

Try using INDEX , which should work fine with a closed book, the syntax will look like this:

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

This will find the “x” in B1: Z1 of sheet book11, and then give 10 cells below (in lines 2 through 11)