• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 56
  • Last Modified:

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
0
Wilder1626
Asked:
Wilder1626
  • 4
  • 3
1 Solution
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now