# 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
LVL 11
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Analyst Assistant Commented:
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.
0
Author Commented:
Hi Norie

When trying the formula. i'm getting a "We found a problem with this formula" error. was it working on your side?
0
Analyst Assistant Commented:
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))
0
Author Commented:
for some reason, i'm still having the same error. can you please send me the sample with your formula in it?
0
Analyst Assistant Commented:
Here's another formula that handles there being more than one column letter.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Analyst Assistant Commented:
0
Author Commented:
The formula is working: