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.
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.