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.
Test1 sheet sample

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.
Dbase sheet sample
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:
Final result

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
Wilder1626Asked:
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.

NorieAnalyst 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
Wilder1626Author 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
NorieAnalyst 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wilder1626Author Commented:
for some reason, i'm still having the same error. can you please send me the sample with your formula in it?
0
NorieAnalyst Assistant Commented:
Here's another formula that handles there being more than one column letter.

=SUBSTITUTE(SUBSTITUTE(ADDRESS(1,MATCH(B2,INDIRECT("'"&D2&"'!"&E2&":"&E2),0)),"$1",""),"$","")
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.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
0
Wilder1626Author Commented:
The formula is working:
=IFERROR(SUBSTITUTE(SUBSTITUTE(ADDRESS(1,MATCH(B2,INDIRECT("'"&D2&"'!"&E2&":"&E2),0)),"$1",""),"$",""),"")

Thank you for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.