# Lookup Formula for Two Letter Column Name

#### legalhustler

##### Well-known Member
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))

Last edited:

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Tetra201

##### MrExcel MVP
Try this:

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

#### legalhustler

##### Well-known Member
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.

Last edited:

#### Norie

##### Well-known Member
Just curious but why doesn't E119 appear in the formula?

#### legalhustler

##### Well-known Member
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.

Last edited:

#### legalhustler

##### Well-known Member
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.

Last edited:

Replies
5
Views
194
Replies
16
Views
196
Replies
5
Views
74
Replies
3
Views
175
Replies
0
Views
237

1,191,170
Messages
5,985,066
Members
439,938
Latest member
MAlhash

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