Formula for mentioning Column Name in Another formula

excel1980

New Member
Joined
Feb 6, 2014
Messages
12
I need to select last entry in a column, let say column G, then I am using this formula =INDEX(G:G,MAX((G:G<>"")*(ROW(G:G)))).

Now my columns can change depending on certain parameters, so I need to select column J instead. Now, how do I replace the part G:G with a dynamic formula which can fill other column names. So lets say in a certain cell (G20) I get the mentioned first cell in column J as $J$30, then I am using this formula to extract the column name =char(column(indirect(G20))+64) to get the alphabet J. When I use this formula in the above index formula, its not selecting the entire column J. Can someone guide me how to do this
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi Aladin,

Yes they are numbers. As of now I need a formula of how to target things like G:G in the above formula or any formula which selects the entire column and where the column can change depending on certain conditions.
 
Upvote 0
Hi Aladin,

Yes they are numbers.

In that case:

=LOOKUP(9.99999999999999E+307,G:G)

will be way faster.

As of now I need a formula of how to target things like G:G in the above formula or any formula which selects the entire column and where the column can change depending on certain conditions.

No clue what you mean. What conditions must be met to take up the last numeric value of the target column?
 
Upvote 0
Lets say I have 3 series in columns G, H and I which expand after updating each and every time. I then select one of the series based on a certain condition and try to find the last value in that column, lets say G here. Now lets say I need it to find for H:H. I dont want to manually write H:H in that lookup formula. I need to find a way where I can refer to H using a reference formula.
 
Upvote 0
So lets say I first find the min of the 1st row of G, H and I. Then based on this min, I find the last value of that column. How to do it without manually writing G:G, H:H or I:I. Hope this is clear.
 
Upvote 0
So lets say I first find the min of the 1st row of G, H and I. Then based on this min, I find the last value of that column. How to do it without manually writing G:G, H:H or I:I. Hope this is clear.

=LOOKUP(9.99999999999999E+307,INDEX(G:I,0,MATCH(MIN(INDEX(G:I,1,0)),INDEX(G:I,1,0),0)))

Note that this will pick out the column whose first cell houses the min value, say, H1. Mind you I1 might house an identical value. Example: G1 = 3, H1 = 7, I1 =3.
 
Upvote 0
=LOOKUP(9.99999999999999E+307,INDEX(G:I,0,MATCH(MIN(INDEX(G:I,1,0)),INDEX(G:I,1,0),0)))

Note that this will pick out the column whose first cell houses the min value, say, H1. Mind you I1 might house an identical value. Example: G1 = 3, H1 = 7, I1 =3.

Thanks Aladin. Can you also tell me how to find the address of the same.
 
Upvote 0
As I understand your request, you know the column that you want to access, but don't want to modify the formula.
In a single cell somewhere (I used A1) put the letter of the column you want, or create a formula that generates the letter. If you want column G:G just enter "G" in A1. If you then want H:H change A1 to "H".

This formula will get the last row with numeric data from the required column
=LOOKUP(9.99999999999999E+307,INDIRECT(A1&":"&A1))
 
Last edited:
Upvote 0
Thanks Aladin. Can you also tell me how to find the address of the same.

Sure. But it's still unclear to me where you are heading...

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
min
2​
2​
4​
2​
2​
count
2​
0.14​
2.5​
3​
last value list
7​
1​
4​
7​
3​
9​
5​
25​
6​
9​
30​
7​
28​
8​
25​
9​

B1, just enter:
Rich (BB code):

=MIN(INDEX(G:I,1,0))

B2, just enter:
Rich (BB code):

=COUNTIFS(INDEX(G:I,1,0),B1)

In A4 control+shift+enter, not just enter, and copy down:
Rich (BB code):

=IF(ROWS($A$4:A4)<=B$2,LOOKUP(9.99999999999999E+307,
    INDEX(G:I,0,SMALL(IF(INDEX(G:I,1,0)=B$1,COLUMN(G:I)-COLUMN(G:G)+1),
    ROWS($A$4:A4)))),"")
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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