Need Lookup Formula to Give Column Range

I'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."

I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.

Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.

Thanks!

Sounds like you coud use INDEX/MATCH (or just MATCH) for this, if you have a sample workbook, we can see what we can come up with

May be this.....

Please Login or Register  to view this content.

The formula will search for Name column in row1 and give you the count for first name from the name column. When the formula is dragged down, it will count the next name in the name column.

Sorry, sometimes I try to get away without posting sample files when I think it's possible to get an answer by just explaining -- but it's almost never that easy. I had to change things around a little bit, since it's just a sample; it's the same basic ideas as above, but the column range is now A:A instead of K:K and instead of looking up "Name," I'm looking up "District":

ColumnRangeSample2.xlsx

May be this.....

On Summary Sheet
In C2

Please Login or Register  to view this content.

and copy down.
Does this help?

You're welcome.
You may also click on * (star) to Add Reputation if the solution provided helped you.