How can Excel search for a word in a spreadsheet and return the column letter that it appears in?

Brass Contributor

What formula could I use to have excel search a spreadsheet for a specific word and simply return the letter of the column that it appears in?

 

I have a somewhat complicated spreadsheet set up that looks through data when it is copy/pasted in. However, some data sets have the columns in different order. My arrays/formulas allow me to manually input the column letter into a cell (i.e. "B" or "C" or whatever column it happens to be in) and the array adjusts. However, I feel like Excel should be able to search and tell me the column letter itself.

 

Any ideas?

 

Thanks,

 

Greg

 

 

1 Reply
If the range wherein you want to search the specific word is A1:C3, the word you want to search is in E2, and the cell wherein you want to return the Column Letter is F2, the formula in F2 is:
=SUBSTITUTE(ADDRESS(1,
SUMPRODUCT(COLUMN(A1:C3)*(A1:C3=E2)),4),
1,"")
The foregoing formula assumes that there is only 1 instance of the word and each cell in the range contains only 1 word with no leading or trailing space.