Lookup Formula for Two Letter Column Name

[legalhustler]

In cell E119 I have the lookup value: $AU$25

I would like to return the value/header name from column AU25 back.

My initial formula below works fine for columns A-Z, but not for columns with two letters (i.e. AU). Can someone help modify this for me or suggest a new way?

=IF(G4="","",INDEX(Sheet1!$D$4:$CX$4,1,CODE(MID(AU25,FIND("$",AU25)+1,FIND("$",AU25,FIND("$",AU25)+1)-(FIND("$",AU25)+1)))-64-3))

 

[Tetra201]

Try this:

=IF(G4="","",INDEX(Sheet1!$D$4:$CX$4,1,COLUMN(INDIRECT(AU25))-3))

 

[63falcondude]

How about something along the lines of =INDEX(1:1,1,COLUMN(INDIRECT($AU$25))) assuming that your headers are in row 1.

 

[legalhustler]

Try this:

=IF(G4="","",INDEX(Sheet1!$D$4:$CX$4,1,COLUMN(INDIRECT(AU25))-3))

Can we avoid using INDIRECT since I have a large spreadsheet and don't want to use a volatile function to slow down my calcs. BTW, I need to extract AU25 from $AU$25...I don't want to hardcode it.

 

[Norie]

Just curious but why doesn't E119 appear in the formula?

 

[legalhustler]

Just curious but why doesn't E119 appear in the formula?

Sorry my formula should say E119 instead of AU25. The value from AU25 is what I want to return.

 

[legalhustler]

My original (corrected) formula is this:

=IF(G4="","",INDEX(Sheet2!$D$4:$CX$4,1,CODE(MID(E119,FIND("$",E119)+1,FIND("$",E119,FIND("$",E119)+1)-(FIND("$",E119)+1)))-64-3))

In Sheet1, cell E119 contains the lookup value: $AU$25

However, I want to return the value from Sheet2 cell AU25 based on the lookup value from cell E119.

Note: My Mid formula is returning "AU" from cell E119.