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

regresss

Board Regular
Joined
May 20, 2015
Messages
68
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Please can you explain in more detail your requirements, all you are stating is the lookup range?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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.)

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]dogs[/TD]
[TD="width: 64"]cats[/TD]
[TD="width: 64"]fish[/TD]
[/TR]
[TR]
[TD]boys[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]girls[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]men[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]women[/TD]
[TD]17[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]boys[/TD]
[TD]cats[/TD]
[TD]6[/TD]
[TD] =INDEX(1:5,MATCH(A8,A1:A5,0),MATCH(B8,1:1,0))[/TD]
[/TR]
</tbody>[/TABLE]

You can use match for row and column references.
 
Upvote 0
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).
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top