Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-370267%22%20slang%3D%22en-US%22%3EHow%20can%20Excel%20search%20for%20a%20word%20in%20a%20spreadsheet%20and%20return%20the%20column%20letter%20that%20it%20appears%20in%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-370267%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20formula%20could%20I%20use%20to%20have%20excel%20search%20a%20spreadsheet%20for%20a%20specific%20word%20and%20simply%20return%20the%20letter%20of%20the%20column%20that%20it%20appears%20in%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20somewhat%20complicated%20spreadsheet%20set%20up%20that%20looks%20through%20data%20when%20it%20is%20copy%2Fpasted%20in.%20However%2C%20some%20data%20sets%20have%20the%20columns%20in%20different%20order.%20My%20arrays%2Fformulas%20allow%20me%20to%20manually%20input%20the%20column%20letter%20into%20a%20cell%20(i.e.%20%22B%22%20or%20%22C%22%20or%20whatever%20column%20it%20happens%20to%20be%20in)%20and%20the%20array%20adjusts.%20However%2C%20I%20feel%20like%20Excel%20should%20be%20able%20to%20search%20and%20tell%20me%20the%20column%20letter%20itself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-370267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-373584%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20Excel%20search%20for%20a%20word%20in%20a%20spreadsheet%20and%20return%20the%20column%20letter%20that%20it%20appears%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-373584%22%20slang%3D%22en-US%22%3EIf%20the%20range%20wherein%20you%20want%20to%20search%20the%20specific%20word%20is%20A1%3AC3%2C%20the%20word%20you%20want%20to%20search%20is%20in%20E2%2C%20and%20the%20cell%20wherein%20you%20want%20to%20return%20the%20Column%20Letter%20is%20F2%2C%20the%20formula%20in%20F2%20is%3A%3CBR%20%2F%3E%3DSUBSTITUTE(ADDRESS(1%2C%3CBR%20%2F%3ESUMPRODUCT(COLUMN(A1%3AC3)*(A1%3AC3%3DE2))%2C4)%2C%3CBR%20%2F%3E1%2C%22%22)%3CBR%20%2F%3EThe%20foregoing%20formula%20assumes%20that%20there%20is%20only%201%20instance%20of%20the%20word%20and%20each%20cell%20in%20the%20range%20contains%20only%201%20word%20with%20no%20leading%20or%20trailing%20space.%3C%2FLINGO-BODY%3E
gms4b
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
Highlighted
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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies