# Formula for mentioning Column Name in Another formula

#### excel1980

##### New Member
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

##### MrExcel MVP
Just curious: What do you have in G - numbers?

#### excel1980

##### New Member

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.

##### MrExcel MVP

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?

#### excel1980

##### New Member
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.

#### excel1980

##### New Member
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.

##### MrExcel MVP
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.

#### excel1980

##### New Member
=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.

#### konew1

##### Well-known Member
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:
• Osvaldo Palmeiro

##### MrExcel MVP
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)))),"")
``````

Replies
3
Views
484
Replies
1
Views
590
Replies
3
Views
407
Replies
10
Views
948
Replies
8
Views
701

### Forum statistics

1,195,671
Messages
6,011,075
Members
441,581
Latest member
rp4717 ### 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.

### Which adblocker are you using?    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

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