We help IT Professionals succeed at work.

Function to return column heading number

StevenPMoffat
on
I would like to have a function that returns the number of the column in a table given I have provided the Header Name.  If this can be a inline function all the better.
Comment
Watch Question

BRONZE EXPERT

Commented:
Assuming that the headers are in row 1 you can use

=SUBSTITUTE(ADDRESS(1,MATCH("yourtitle",1:1,0),2),"$1","")

If it is some other row then change 1:1 to suit
Most Valuable Expert 2012
Top Expert 2012

Commented:
You can use this array entered function (assuming your header is on row 1):

=MATCH(1,(1:1="Header_Name")*1,0)

type the function, then instead of hitting ENTER, Confirm with CTRL-SHIFT-ENTER

Dave
BRONZE EXPERT
Commented:
Oops...column number...why not simply

=MATCH("Header_Name",1:1,0)
Most Valuable Expert 2012
Top Expert 2012

Commented:
@ssaqibh - Good one.  While both are "correct" for finding the column header on the row, yours finds the last occurrence of that header, mine finds the first.  If there's only one, that last post is a winner!

Dave
BRONZE EXPERT

Commented:
Dave, are you back from your vacation? Probably not....both formulas give same results.
Most Valuable Expert 2012
Top Expert 2012

Commented:
If there are multiple occurrences then they yield results as I posted

Yep. I'm back it was a quickie
BRONZE EXPERT

Commented:
Multiple occurences... both give same results.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Agree - I must have been looking cross-sighted :P

Author

Commented:
Very simple, thank you

Explore More ContentExplore courses, solutions, and other research materials related to this topic.