Find data in a cell from a grid of words

[tonywatsonhelp]

Hi Everyone,

In K4 I have a sentence,

in range AD3:AZ23 I have words that can possibly be in that sentence.

I'd like a formula that can look at the words in range AD3:AZ23 and see if any of them can be found within the sentence that's in K4, if so return that word. If not return other. (if it finds more than one word just return the first)

please help if you can as i've got no idea how to do this.

Thansk
Tony

 

[awoohaw]

This is sort of the opposite. Parsing your sentence to find words. But, you can probably reverse the formula. You'll have to adjust your cell references as the xl2bb of column K to column AZ is pretty large. Also, this is a 365 solution:

Book1
	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1
2
3				WJJX	KDYX	ZOJW	NMCU	SPAN	EPQE	RNSQ	FDIX	QACG	FYCM	LXML	YYON	ZGAT	JWSY	TDDH	YRSR	TCZS	QWDP	GBGP	EOJW	CAAO	LERN	IGVJ
4	In K4 I have a sentence			MWZO	EBAX	CAIQ	FISW	IAJN	EAWF	AOQS	NCHE	VBXN	UYGR	RPPA	YMMX	FOUU	UYRH	RDDI	GWNQ	LBZM	PQLI	BERE	RLNQ	EWYM	AENS	QLHJ
5				WBFO	IBQL	ZNZX	YRLX	ODGJ	DUUF	BCKS	TYFO	CIXQ	YLTT	RJEW	OLNR	EOOT	IRMO	WINF	XDBS	OZEM	ZRGZ	FPYT	EPJH	LWZJ	IEPH	QOBN
6				HNOK	RQND	In	JRDG	PEZP	YHFN	UOBA	XZZA	LVFR	VRFH	RGYI	VFJK	CCDN	VEBC	ZFKI	UDPY	WRGH	DKCY	IJFJ	BDCG	FPIX	GRRJ	AGHT
7				ISCF	AWYY	OAGR	MLKJ	GOKT	YSJV	TPMB	ELFI	IPJZ	KQZR	QZYT	JKCN	IDCV	TQWB	LHHS	NZDH	LYTU	FXBU	VUZX	FDTO	UQJC	VNWY	UDJO
8				YAIV	YQNL	TRRR	BBYG	ZOBQ	URAU	OVTV	RVWF	UBXU	YQPH	ISJR	YOML	UBXI	DWIG	SCYF	IAQN	VBEW	TKUW	IPTE	WVJM	GXHU	JJTS	GKWO
9				VQOQ	MQFV	KNNN	WPIR	BPWQ	OBGF	IHRN	DVLJ	BJZN	ZXSB	BBXN	PBRH	LHDN	LGTG	UYIH	BKKT	SPDI	WCTQ	QEUI	HXLZ	UVQL	SCHJ	CADG
10				TIGP	GVTS	DVQT	ZXJJ	IIIV	YCKH	NSTS	FHPY	AHTN	DKKE	FGHL	SFXB	FCEA	FEIP	LHMB	LBQN	GVCC	CDMO	ENRP	BYVT	NDNO	LRIJ	IYOY
11				AMTJ	HVKW	PNCQ	VPVT	IMPA	K4	MDUY	ZRJY	WSWV	QOBZ	PDYS	JLDW	JXEG	TTOR	NZBL	UDFW	KZCL	QQGS	SLKS	DNBR	VRLW	KSPG	PECH
12				GSUR	KWXK	ZUWK	CDSO	LHBB	IMLQ	LRSK	HGEY	QBHG	DYYA	KBOT	ATGH	CWVL	IZAC	BMRB	YXPR	TBQS	VIGF	LOXH	ZLHN	RCTP	PEJG	GFBE
13				YVGM	ODCO	BOJJ	ABQE	GPJC	TUJL	UMTB	NZNE	XODG	NZPQ	FGCM	WQSW	JOSK	LQEX	YCBI	YZZU	CKUK	MXXA	WDLE	ZSXL	XHFA	QHHP	WDGT
14				MNUN	XGIF	XXGC	DXMZ	CYKD	VMGR	EAOD	SSTI	FJDI	UXCX	WJUC	HMYZ	OCJM	YRFQ	FRYI	FWFS	BGJC	IRMJ	WTBC	BHXB	AQWC	ZHCW	FPND
15				JSLZ	I	CTLC	PJCA	NYFQ	YRPE	HMMV	IYGB	UUTP	KVQD	LBVD	ZENN	EAKP	PMQA	GMFK	HIBW	NWKY	QMPZ	DSHG	EPEA	LHPG	ZXUD	ABHA
16				NZKR	MCUP	FWJP	SDPO	GENP	ZWKC	KBEA	GFHU	QFQB	KBRC	VELW	WAQK	UMVK	KOHO	HRTH	MPHA	NYFO	YTCB	DJDB	NTPT	FDZZ	PTGJ	MXJV
17				CRUK	XTGW	XCLF	GYOM	have	OUBI	EIUD	JCYI	UMHW	CSSQ	ISYL	BUZZ	CLAJ	SYYL	FNDI	JRYV	SLSW	WDMM	MSHR	AFVW	SFOZ	CAIR	ODCR
18				OVAT	QOCX	DEEU	PUHT	RFNE	RNCL	EKMZ	YHHI	NGRW	AEGD	ORJB	HDJL	PUDQ	BKVE	AIQX	ZVKW	ZATJ	MEQM	PSQB	OYKO	SGIE	QFBB	BPHH
19				FGYN	LAHJ	CVDN	LPWQ	JOXT	EOGJ	IEIK	QBUX	JKVV	FWSD	DCKL	JTBH	RUOZ	LMOU	JMJV	MTUX	SQFE	HPAC	OIHO	DWCI	FAPY	ICKD	DFUX
20				HBVP	MYEE	BYXE	FNJQ	SBKN	YPNK	a	GHXG	KIGB	MHUM	NHCR	REQI	EMTP	CRCL	FPMU	HHXY	NJVD	CTJI	IQKF	NILC	VSGD	TZVQ	TXTN
21				FCFF	MMFL	JUCT	EJGH	YRBB	RIAO	ELLI	DUBH	BZCF	PBXH	LQBD	IOVN	PHXM	ERQI	KNFA	PQLA	BCTB	PQFU	ISHQ	NEAG	CMWX	KHDD	FQXY
22				NZWN	UVSN	RGNG	LKVR	NXWB	OOWC	EGRC	EMDI	RKML	sentence	CPZG	JEDX	DOOF	HECS	KZGM	PISY	EJFF	VTMH	ZSBM	FKKR	CWQS	GPHJ	CJWF
23
24	In		In
25	K4		K4
26	I		I
27	have		have
28	a		a
29	sentence		sentence
Sheet1

Cell Formulas
Range	Formula
AA24:AA29	AA24	=TEXTSPLIT(AA4,," ")
AC24:AC29	AC24	=INDEX(wordlist,SUM(IFNA(SEQUENCE(ROWS(wordlist))*(AA24=wordlist),0)),SUM(IFNA(SEQUENCE(,COLUMNS(wordlist))*(AA24=wordlist),0)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
wordlist	=Sheet1!$AD$3:$AZ$22	AC24:AC29

 

[tonywatsonhelp]

Hi, thanks for your help, I think i can make this work
Thansk
Tony

 

[awoohaw]

Great. Happy to help.

Best Wishes!

 

[Peter_SSs]

I think i can make this work

Are you sure?

in range AD3:AZ23 I have words that can possibly be in that sentence.

My reading of that is that not all the words in the sentence might be in AD3:AZ23. If I am correct, for the layout in post #2, try changing cell AA4 to "This sentence has five words?

return that word. If not return other. (if it finds more than one word just return the first)

My suggestion would be ..

23 11 28.xlsm
	K	L																		AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
3																				WJJX	KDYX	ZOJW	NMCU	SPAN	EPQE	RNSQ	FDIX	QACG	FYCM	LXML	YYON	ZGAT	JWSY	TDDH	YRSR	TCZS	QWDP	GBGP	EOJW	CAAO	LERN	IGVJ
4	This sentence has five words																			MWZO	EBAX	CAIQ	FISW	IAJN	EAWF	AOQS	NCHE	VBXN	UYGR	RPPA	YMMX	FOUU	UYRH	RDDI	GWNQ	LBZM	PQLI	BERE	RLNQ	EWYM	AENS	QLHJ
5																				WBFO	IBQL	ZNZX	YRLX	ODGJ	DUUF	BCKS	TYFO	CIXQ	YLTT	RJEW	OLNR	EOOT	IRMO	WINF	XDBS	OZEM	ZRGZ	FPYT	EPJH	LWZJ	IEPH	QOBN
6	sentence																			HNOK	RQND	xxx	JRDG	PEZP	YHFN	UOBA	XZZA	LVFR	VRFH	RGYI	VFJK	CCDN	VEBC	ZFKI	UDPY	WRGH	DKCY	IJFJ	BDCG	FPIX	GRRJ	AGHT
7																				ISCF	AWYY	OAGR	MLKJ	words	YSJV	TPMB	ELFI	IPJZ	KQZR	QZYT	JKCN	IDCV	TQWB	LHHS	NZDH	LYTU	FXBU	VUZX	FDTO	UQJC	VNWY	UDJO
8																				YAIV	YQNL	TRRR	BBYG	ZOBQ	URAU	OVTV	RVWF	UBXU	YQPH	ISJR	YOML	UBXI	DWIG	SCYF	IAQN	VBEW	TKUW	IPTE	WVJM	GXHU	JJTS	GKWO
9																				VQOQ	MQFV	KNNN	WPIR	BPWQ	OBGF	IHRN	DVLJ	BJZN	ZXSB	BBXN	PBRH	LHDN	LGTG	UYIH	BKKT	SPDI	WCTQ	QEUI	HXLZ	UVQL	SCHJ	CADG
10																				TIGP	GVTS	DVQT	ZXJJ	IIIV	YCKH	NSTS	FHPY	AHTN	DKKE	FGHL	SFXB	FCEA	FEIP	LHMB	LBQN	GVCC	CDMO	ENRP	BYVT	NDNO	LRIJ	IYOY
11																				AMTJ	HVKW	PNCQ	VPVT	IMPA	AAA	MDUY	ZRJY	WSWV	QOBZ	PDYS	JLDW	JXEG	TTOR	NZBL	UDFW	KZCL	QQGS	SLKS	DNBR	VRLW	KSPG	PECH
12																				GSUR	KWXK	ZUWK	CDSO	LHBB	IMLQ	LRSK	HGEY	QBHG	DYYA	KBOT	ATGH	CWVL	IZAC	BMRB	YXPR	TBQS	VIGF	LOXH	ZLHN	RCTP	PEJG	GFBE
13																				YVGM	ODCO	BOJJ	ABQE	GPJC	TUJL	UMTB	NZNE	XODG	NZPQ	FGCM	WQSW	JOSK	LQEX	YCBI	YZZU	CKUK	MXXA	WDLE	ZSXL	XHFA	QHHP	WDGT
14																				MNUN	XGIF	XXGC	DXMZ	CYKD	VMGR	EAOD	SSTI	FJDI	UXCX	WJUC	HMYZ	OCJM	YRFQ	FRYI	FWFS	BGJC	IRMJ	WTBC	BHXB	AQWC	ZHCW	FPND
15																				JSLZ	III	CTLC	PJCA	NYFQ	YRPE	HMMV	IYGB	UUTP	KVQD	LBVD	ZENN	EAKP	PMQA	GMFK	HIBW	NWKY	QMPZ	DSHG	EPEA	LHPG	ZXUD	ABHA
16																				NZKR	MCUP	FWJP	SDPO	GENP	ZWKC	KBEA	GFHU	QFQB	KBRC	VELW	WAQK	UMVK	KOHO	HRTH	MPHA	NYFO	YTCB	DJDB	NTPT	FDZZ	PTGJ	MXJV
17																				CRUK	XTGW	XCLF	GYOM	have	OUBI	EIUD	JCYI	UMHW	CSSQ	ISYL	BUZZ	CLAJ	SYYL	FNDI	JRYV	SLSW	WDMM	MSHR	AFVW	SFOZ	CAIR	ODCR
18																				OVAT	QOCX	DEEU	PUHT	RFNE	RNCL	EKMZ	YHHI	NGRW	AEGD	ORJB	HDJL	PUDQ	BKVE	AIQX	ZVKW	ZATJ	MEQM	PSQB	OYKO	SGIE	QFBB	BPHH
19																				FGYN	LAHJ	CVDN	LPWQ	JOXT	EOGJ	IEIK	QBUX	JKVV	FWSD	DCKL	JTBH	RUOZ	LMOU	JMJV	MTUX	SQFE	HPAC	OIHO	DWCI	FAPY	ICKD	DFUX
20																				HBVP	MYEE	BYXE	FNJQ	SBKN	YPNK	a	GHXG	KIGB	MHUM	NHCR	REQI	EMTP	CRCL	FPMU	HHXY	NJVD	CTJI	IQKF	NILC	VSGD	TZVQ	TXTN
21																				FCFF	MMFL	JUCT	EJGH	YRBB	RIAO	ELLI	DUBH	BZCF	PBXH	LQBD	IOVN	PHXM	ERQI	KNFA	PQLA	BCTB	PQFU	ISHQ	NEAG	CMWX	KHDD	FQXY
22																				NZWN	UVSN	RGNG	LKVR	NXWB	OOWC	EGRC	EMDI	RKML	sentence	CPZG	JEDX	DOOF	HECS	KZGM	PISY	EJFF	VTMH	ZSBM	FKKR	CWQS	GPHJ	CJWF
23																				j	k	l	m	n	o	p	q	r	s	t	u	v	w	x	y	z	aa	ab	ac	ad	ae	af
Find word (3)

Cell Formulas
Range	Formula
K6	K6	=LET(w,TEXTSPLIT(K4," "),INDEX(FILTER(w,ISNUMBER(MATCH(w,TOCOL(AD3:AZ23),0)),"other"),1))