asked on

Excel formula - Find column letter from another sheet based on the header name from specific row

Hi,

I need your help with an excel formula that would retrieve the column letter from another sheet inside the same workbook that have specific header name.

Let me explain;

In the workbook, i have 2 sheets: Test1 and Dbase.

Sheet Test1 will have headers that could start at any rows. In below case, the header are at row 4.

Then I have the Dbase sheet that will have the same header names in column B, I will tell also the sheet name that I want to pull the column letter from in column D, and also the Row headers in column E where the headers are positioned in the sheet from the column D.

Now, I want to have a formula in column C that will pull the column letters for each headers.
The result will look like this:

I have some formulas like : SUBSTITUTE(ADDRESS(1,COLUMN(),4,”1”,””) and also UPPER(CHAR(COLUMN()+96)), but this is only applicable when retrieving from the same sheet.

How can I do this?

Thank you for your help.
find-column-letters-from-another-sh.xlsx

Norie

You could use this to get the column numbers for the headers on the other sheet,

=MATCH(B2, INDIRECT("'" & D2 & "'!"&E2&":"&E2), 0)

and to get the column letters.

=UPPER(CHAR(MATCH(B2, INDIRECT("'" & D2 & "'!"&E2&":"&E2), 0)+96))

Note the 2nd formula is basically UPPER(CHAR(COLUMN()+96)) with COLUMN() replaced with the first formula.

Wilder1626

ASKER

Hi Norie

When trying the formula. i'm getting a "We found a problem with this formula" error. was it working on your side?

Norie

I copied the formulas directly from Excel where they are both working.

I'll copy them again.

Column number: =MATCH(B2, INDIRECT("'" & D2 & "'!"&E2&":"&E2), 0)

Column letter: =UPPER(CHAR(MATCH(B2, INDIRECT("'" & D2 & "'!"&E2&":"&E2), 0)+96))

Wilder1626

ASKER

for some reason, i'm still having the same error. can you please send me the sample with your formula in it?

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

Here's the file.
find-column-letters-from-another-sh.xlsx

Wilder1626

ASKER

The formula is working:
=IFERROR(SUBSTITUTE(SUBSTITUTE(ADDRESS(1,MATCH(B2,INDIRECT("'"&D2&"'!"&E2&":"&E2),0)),"$1",""),"$",""),"")

Thank you for your help