Non-volatile way to return values from column knowing its position

[regresss]

Say I am interested in the first 100 rows of column_j and I know the j. How can I retrieve this values in non-volatile way, using formulas?

 

[MARK858]

Please can you explain in more detail your requirements, all you are stating is the lookup range?

 

[regresss]

I am interested in values in a column. The column has a header which I know. Using index match for header name I locate the column number (I added numbering above headers of columns) but now struggle to return the values in efficient way. My dataset is fairly small so no need for vba.

 

[MARK858]

but now struggle to return the values in efficient way.

You re still not explaining what values you want returned (all you have stated is the first hundred rows of column J which a couple of ways are =IF(ROWS($B$1:B1)<101,INDEX(J:J,ROWS($B$1:B1)),"") or =IF(ROWS($B$1:B1)<101,INDEX(A:J,ROWS($B$1:B1),10),"") dragged down) or how you are defining what values you want returned.

Please post a small sample of your data including the results you want (and where) then perhaps we can work out what you want from there.

 

[C with no eyes]

Does this help explain how index works? (You don't need to number your columns, if excel asks you for a column reference it wants a number A=1, B=2 etc.)

	dogs	cats	fish
boys	7	6	5
girls	9	11	2
men	3	10	12
women	17	7	5
boys	cats	6	=INDEX(1:5,MATCH(A8,A1:A5,0),MATCH(B8,1:1,0))

<tbody>
</tbody>

You can use match for row and column references.

 

[regresss]

C with no eyes: Beautiful solution. Learned something new.

You are right with no need to number columns. I still though return their position to optimize runtime of index and not calculate everytime (the second match).

 

[FDibbins]

C with no eyes: Beautiful solution. Learned something new.

You are right with no need to number columns. I still though return their position to optimize runtime of index and not calculate everytime (the second match).

With a small data set, as you said, I see no reason not to use MATCH to return the column number, you will not see any speed or efficiency loss