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
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
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies