Finding a word in a worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1536698%22%20slang%3D%22en-US%22%3EFinding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536698%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20a%20specific%20word%20that%20is%20in%20a%20worksheet%20and%20return%20the%20cell%20address.%26nbsp%3B%20The%20word%20will%20only%20be%20in%20the%20sheet%201%20time%2C%20but%20it%20can%20be%20located%20anywhere%20in%20the%20sheet.%26nbsp%3B%20Because%20it%20can%20be%20anywhere%2C%20I%20haven't%20been%20able%20to%20use%20any%20of%20the%20traditional%20lookup%20formulas.%26nbsp%3B%20Does%20anyone%20have%20any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1536698%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-1536711%22%20slang%3D%22de-DE%22%3ESubject%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536711%22%20slang%3D%22de-DE%22%3ECTRL%20%2B%20F%20%3CBR%20%2F%3E%20for%20search%2C%20%3CBR%20%2F%3E%20write%20the%20word%20you%20search...%20and%20if%20there..%20excel%20find%20it%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20I%20would%20be%20happy%20to%20find%20out%20if%20I%20could%20help.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536730%22%20slang%3D%22en-US%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536730%22%20slang%3D%22en-US%22%3EHi%2C%20thank%20you%20but%20it%20needs%20to%20be%20a%20formula%20as%20there%20is%20a%20long%20list%20of%20words%20that%20need%20to%20be%20found%20and%20then%20those%20references%20need%20to%20be%20used%20in%20a%20longer%20formula.%20Not%20sure%20if%20this%20is%20possible%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536761%22%20slang%3D%22de-DE%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536761%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676142%22%20target%3D%22_blank%22%3E%40TM20204%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22find_word.JPG%22%20style%3D%22width%3A%20771px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206946i940EA7DD9D9AF724%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22find_word.JPG%22%20alt%3D%22find_word.%20Jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536781%22%20slang%3D%22de-DE%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536781%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20so%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22find_word2.JPG%22%20style%3D%22width%3A%20424px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206947iC7B38212B3014D24%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22find_word2.JPG%22%20alt%3D%22Matrix%20Formula%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EMatrix%20Formula%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAttention%2C%20matrix%20formula!%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3EDon't%20type%20the%20braces%20but%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ethe%20cell%20with%3C%2FSPAN%3E%3CBR%20%2F%3EShift%20%2B%3CSPAN%3ECtrl%20%2B%20Enter%3C%2FSPAN%3E%3CBR%20%2F%3Eleave%20alone%20instead%20of%3CSPAN%3Eenter.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20find%20out%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536797%22%20slang%3D%22en-US%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536797%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I'm%20not%20sure%20this%20will%20work.%26nbsp%3B%20I'll%20try%20to%20be%20clearer.%26nbsp%3B%20On%20one%20sheet%20I%20have%20a%20list%20of%20words%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreen%3C%2FP%3E%3CP%3EBoy%3C%2FP%3E%3CP%3EApple%3C%2FP%3E%3CP%3EThomas%3C%2FP%3E%3CP%3ECat%3C%2FP%3E%3CP%3EHouse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20another%20sheet%2C%20I%20have%20a%20giant%20data%20set%20where%20the%20words%20can%20be%20anywhere%20(There%20are%20no%20blank%20cells%20but%20have%20left%20some%20blank%20to%20illustrate%20my%20point).%26nbsp%3B%20I%20need%20to%20know%20what%20cell%20each%20word%20is%20in.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EG%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EGreen%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EBoy%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EApple%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3ECat%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EHouse%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3EThomas%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20first%20sheet%20it%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EGreen%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EA1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CP%3EBoy%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EE3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EApple%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EB4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EThomas%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EF7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3ECat%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EG5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EHouse%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EC6%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536834%22%20slang%3D%22en-US%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676142%22%20target%3D%22_blank%22%3E%40TM20204%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20name%20the%20region%20with%20words%20as%20Range%20(performance%20is%20significantly%20depends%20on%20its%20size)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20820px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206965iCEF3F9C1E88E5D0F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DADDRESS(SUMPRODUCT((Range%3D%24C3)*ROW(Range))-%40ROW(Range)%2B1%2C%0A%20%20%20%20%20%20%20%20%20SUMPRODUCT((Range%3D%24C3)*COLUMN(Range))-%40COLUMN(Range)%2B1%2C%0A4)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536898%22%20slang%3D%22en-US%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536898%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20this%20worked!%20I'm%20not%20quite%20sure%20how%20it's%20working%20but%20I%20was%20able%20to%20modify%20the%20areas%20I%20needed.%26nbsp%3B%20Is%20there%20a%20link%20somewhere%20that%20I%20can%20learn%20how%20the%20sumproduct%20is%20working%20in%20this%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538716%22%20slang%3D%22en-US%22%3ERE%3A%20Finding%20a%20word%20in%20a%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538716%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676142%22%20target%3D%22_blank%22%3E%40TM20204%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBasic%20formula%20is%20explained%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fget-location-of-value-in-2d-array%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGet%20location%20of%20value%20in%202D%20array%3C%2FA%3E%26nbsp%3B%2C%20the%20only%20it%20shall%20be%20slightly%20modified%20if%20you%20are%20on%20Excel%20wit%20dynamic%20arrays.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi!

 

I am trying to find a specific word that is in a worksheet and return the cell address.  The word will only be in the sheet 1 time, but it can be located anywhere in the sheet.  Because it can be anywhere, I haven't been able to use any of the traditional lookup formulas.  Does anyone have any suggestions?

8 Replies
STRG + F
for search,
write the word you search...and if there..excel find it

I would be happy to find out if I could help.


Nikolino
I know I don't know anything (Socrates)
Hi, thank you but it needs to be a formula as there is a long list of words that need to be found and then those references need to be used in a longer formula. Not sure if this is possible?

@TM20204 

 

find_word.JPG

@Nikolino 

 

or so

 

Matrix FormelMatrix Formel

Attention, matrix formula!
Don't type the braces {}
but the cell with
Shift + Ctrl + Enter
leave alone instead of enter.

 

I would be happy to find out if I could help.

 

 

Nikolino

I know I don't know anything (Socrates)

 

Hi I'm not sure this will work.  I'll try to be clearer.  On one sheet I have a list of words:

 

Green

Boy

Apple

Thomas

Cat

House

 

On another sheet, I have a giant data set where the words can be anywhere (There are no blank cells but have left some blank to illustrate my point).  I need to know what cell each word is in. 

 

 ABCDEFG
1Green      
2       
3    Boy  
4 Apple     
5      Cat
6  House    
7     Thomas 

 

 

So in the first sheet it looks like this:

 

GreenA1

Boy

E3
AppleB4
ThomasF7
CatG5
HouseC6

 

 

 

@Nikolino 

@TM20204 

If name the region with words as Range (performance is significantly depends on its size)

image.png

formula could be

=ADDRESS(SUMPRODUCT((Range=$C3)*ROW(Range))-@ROW(Range)+1,
         SUMPRODUCT((Range=$C3)*COLUMN(Range))-@COLUMN(Range)+1,
4)

Thank you this worked! I'm not quite sure how it's working but I was able to modify the areas I needed.  Is there a link somewhere that I can learn how the sumproduct is working in this formula?

 

Thank you so much!

 

 

 

@Sergei Baklan 

@TM20204 

Basic formula is explained here Get location of value in 2D array , the only it shall be slightly modified if you are on Excel wit dynamic arrays.