Forum Discussion
How can Excel search for a word in a spreadsheet and return the column letter that it appears in?
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 tha...
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.
=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.