look up specific word in a cell with multiple words and return adjacent cell word, very complicated

[mat20]

Hello Everyone,

I would really appreciate your help with some data that needs to be organized for analysis purposes.

I would like excel to look up the preygenename such as PRKDC going down the Total #GPs column and return all the associated Go Slim Terms in one cell adjacent to the corresponding Preygenename for example for PRKDC the result should be GO:0034641cellular nitrogen compound metabolic process, GO:0006464cellular protein modification process
I would like this to happen for all the preygenename items and there are about 155 Total #Gps and about 90 Preygenename

Sorry about the redundancy in the Total # GPs column as I could not find a way to remove the duplicates

Thank you in advance and here is a sample of the table

GO Slim Term	Total # GPs	PreyGeneName	Result
GO:0034641cellular nitrogen compound metabolic process	389 ( ACP1 ACSL3 ACSL3 Acsl3 ACSL3 Acsl3 ACSL3 ADSL ADSL ADSL Adsl ADSL ADSL ADSL ADSL ADSL adsl Adsl AIMP1 AIMP2 ASNS ASNS asnS ASNS ASNS Asns ASNS ASNS asnS asns ASNS Asns ASNSasns ASNS ASNS asnS ASNS ASNS CAND1 Cand1 CAND1 CAND1 CAND1 Cand1 Ctps CTPS ctps Ctps dcd dcd dcd dcd EIF2S1 EIF4A1 EIF4G2 EIF4G2 Eif4g2 EIF4G2 EIF4G2 EIF4G2 EIF4G2 EIF4G2 EIF4G2 EIF4G2EIF4G2 EIF4G2 EIF4G2 EIF4G2 EIF4G2 EIF4G2 Eif4g2 Eif4g2 EIF4G2 Eif4g2 EIF4G2 eIF4G2 EIF4G2 EIF4G2 EIF4G2 EIF4G2 Eno1 ENO1 Eno1 ENO1 ENO1 ERC1 ERC1 FANCI FANCI Fanci FANCI FANCI FANCI FANCIFANCI FANCI fanci FANCI Fanci FANCI FANCI FANCI GART GART GART GART GART GART GART gart Gart GART Gart GART GART GART GART GART GART GART Gart GART GMPS Gmps Gmps GMPS gmps GmpsGMPS GMPS GMPS GMPS GMPS GSPT1 Gspt1 Hk1 Hnrnpl HNRNPL Hnrnpl HNRNPL HNRNPL Hnrnpl Hnrnpl HNRNPL HUWE1 HUWE1 Huwe1 IARS Iars IARS Iars iars IARS IARS IARS IARS IARS IARS KPNB1 Lars LARSLARS Lars LARS LARS LARS LARS LARS LARS LARS MCM3 MCM3 Mcm3 mcm3 MCM3 MCM3 Mcm3 Mcm3 MCM3 mcm3 MCM3 MCM3 MCM3 mcm3 MCM3 MCM3 MCM3 MCM3 MCM3 MCM5 Mcm5 MCM5 MCM5 MCM5MCM5 Mcm5 MCM5 MCM5 MCM5 mcm5 MCM5 mcm5 MCM5 mcm5 Mcm5 mcm5 Mcm5 Mcm7 MCM7 MCM7 Mcm7 MCM7 Mcm7 MCM7 MCM7 mcm7 mcm7 mcm7 mcm7 MTHFD1 MTHFD1 Mthfd1 MTHFD1 MTHFD1 MTHFD1Mthfd1 Mthfd1 MTHFD1 MTHFD1 MTHFD1 MTHFD1 MTHFD1 Mthfd1 NAMPT Nampt Nampt NAMPT NAMPT NAMPT NAMPT Nampt NUP155 pcna pcna PCNA PCNA PCNA PCNA Pcna Pcna PCNA pcna PCNA pcna PCNA PCNApcna PCNA PDIA6 Pfn1 PFN1 PFN1 Pfn1 PFN1 PFN1 PGK1 PHGDH PPP2R1A Prkdc PRKDC PRKDC PRKDC PRKDC PRKDC PRKDC PRKDC PRKDC PRKDC PRKDC PRKDC Prkdc PRPS1 PRPS1 PRPS1 Prps1 PRPS1 Prps1PRPS1 PRPS1 PRPS1 PSMC5 PSMC5 PSMC5 Psmc5 Psmc5 PSMC5 PSMC5 PSMC5 PSMC5 PSMD3 PSMD7 PTGES3 QARS QARS QARS qars QARS QARS QARS QARS QARS QARS QARS RARS RARS rars RARS RARSRARS RARS RARS Rars RARS RARS Rars STUB1 Stub1 STUB1 TARS Tars tars TARS TARS TARS TARS Tars TARS TARS TARS TARS Tars Tmed2 TMED2 TMED2 Tmed2 TMED2 TMED2 TMED2 TRIM28 Trim28TRIM28 Trim28 TRIP13 TRIP13 Trip13 TRIP13 trip13 TRIP13 trip13 TRIP13 Trip13 TROVE2 UBB UBB VARS VARS VARS Vars VARS VARS VARS VARS VARS Vars VARS vars VARS VARS Xpo1 Xpo1 XPO1 XPO1XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 )	PRKDC	GO:0034641cellular nitrogen compound metabolic process, GO:0006464cellular protein modification process
GO:0009058biosynthetic process	378 ( ACAA2 ACP1 ACP1 ACP1 ACSL3 ACSL3 ACSL3 ACSL3 ACSL3 Acsl3 ACSL3 Acsl3 ACSL3 ADSL ADSL ADSL Adsl ADSL ADSL ADSL ADSL ADSL adsl Adsl AIMP1 Aimp1 Aimp2 AIMP2 Aimp2 AIMP2 ASNS ASNSasnS ASNS ASNS Asns ASNS ASNS asnS asns ASNS Asns ASNS asns ASNS ASNS asnS ASNS ASNS CAND1 Cand1 CAND1 CAND1 CAND1 Cand1 Ctps CTPS ctps Ctps dcd Eif2s1 EIF2S1 EIF2S1 EIF2S1 EIF2S1EIF2S1 EIF2S1 EIF2S1 Eif2s1 eif2s1 EIF4A1 EIF4A1 Eif4a1 EIF4G2 Eif4g2 Eif4g2 EIF4G2 EIF4G2 EIF4G2 eIF4G2 EIF4G2 eif5a EIF5A EIF5A Eif5a Eif5a EIF5A EIF5A EIF5A EIF5A eif5a EIF5A ENO1 Eno1 ENO1 ENO1ENO1 ENO1 Eno1 ENO1 ENO1 ERC1 ERC1 FDPS FDPS FDPS FDPS Fdps fdps Fdps FDPS FDPS Fdps GART GART GART GART GART GART GART gart Gart GART Gart GART GART GART GART GART GART GARTGART Gart GART GCN1L1 GMPS Gmps Gmps GMPS gmps Gmps GMPS GMPS GMPS GMPS GMPS Gspt1 IARS IARS Iars Iars iars IARS IARS IARS IARS IARS IARS Lars LARS LARS Lars LARS LARS LARS LARSLARS LARS LARS MCM3 MCM3 Mcm3 mcm3 MCM3 MCM3 Mcm3 Mcm3 MCM3 mcm3 MCM3 MCM3 MCM3 mcm3 MCM3 MCM3 MCM3 MCM3 MCM3 MCM5 Mcm5 MCM5 MCM5 MCM5 MCM5 Mcm5 MCM5 MCM5 MCM5 mcm5MCM5 mcm5 MCM5 mcm5 Mcm5 mcm5 Mcm5 Mcm7 MCM7 MCM7 Mcm7 MCM7 Mcm7 MCM7 MCM7 mcm7 mcm7 mcm7 mcm7 MTHFD1 MTHFD1 Mthfd1 MTHFD1 MTHFD1 MTHFD1 Mthfd1 Mthfd1 MTHFD1 MTHFD1MTHFD1 MTHFD1 MTHFD1 Mthfd1 NAMPT Nampt Nampt NAMPT NAMPT NAMPT NAMPT Nampt pcna pcna PCNA PCNA PCNA PCNA Pcna Pcna PCNA pcna PCNA pcna PCNA PCNA pcna PCNA Pfn1 PFN1 PFN1 Pfn1 PFN1PFN1 PGK1 Pgk1 pgk1 PGK1 PGK1 PGK1 Pgk1 PGK1 PHGDH PHGDH Phgdh PHGDH PHGDH PHGDH PHGDH Phgdh PHGDH phgdh PPP2R1A Prkdc PRKDC PRKDC PRKDC Prkdc PRPS1 PRPS1 PRPS1 Prps1 PRPS1 Prps1PRPS1 PRPS1 PRPS1 PSMC5 PSMC5 PSMC5 Psmc5 Psmc5 PSMC5 PSMC5 PSMC5 PSMC5 Ptges3 PTGES3 PTGES3 Ptges3 PTGES3 QARS QARS QARS qars QARS QARS QARS QARS QARS QARS Qars QARS RARSRARS rars RARS RARS RARS RARS RARS Rars RARS RARS Rars TARS Tars tars TARS TARS TARS TARS Tars TARS TARS TARS TARS Tars TRIM28 Trim28 TRIM28 Trim28 TRIP13 TROVE2 UBB UBB VARS VARSVARS Vars VARS VARS VARS VARS VARS Vars VARS vars VARS VARS Xpo1 Xpo1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 )	UBB
GO:0006810transport	270 ( ACSL3 ACSL3 Acsl3 ACSL3 Acsl3 ACSL3 Ap1g1 AP1G1 AP1G1 AP1G1 AP1G1 ap1g1 AP1G1 AP1G1 AP1G1 ap1g1 AP1G1 AP1G1 AP1G1 AP1G1 AP1G1 AP1G1 Ap1g1 AP1G1 AP1G1 AP1G1 AP1G1 asns BCAP31BCAP31 BCAP31 BCAP31 BCAP31 Bcap31 bcap31 Bcap31 BCAP31 BCAP31 BCAP31 BCAP31 cand1 Clic4 clic4 Clic4 CLIC4 CLTC CLTC CLTC CLTC CLTC CLTC Cltc Cltc CLTC CLTC CLTC CLTC CLTC CLTC CLTC CLTCCltc Cltc Cltc CLTC CLTC COPB1 COPB1 COPB1 COPB1 Copb1 COPB1 COPB1 COPB1 COPB1 COPB1 copb1 Copb1 copG COPG CSE1L CSE1L Cse1l CSE1L cse1l CSE1L CSE1L CSE1L CSE1L CSE1L Cse1l CSE1L ctpsDync1h1 Dync1h1 DYNC1H1 eif2s1 eif5a Eif5a Eif5a EIF5A EIF5A Erc1 ERC1 ERC1 ERC1 ERC1 Erc1 ERC1 ERC1 FKBP4 Fkbp4 Fkbp4 FKBP4 Hk1 HK1 Hk1 KIF5B KIF5B Kif5b Kif5b KIF5B KIF5B KIF5B KPNB1 KPNB1Kpnb1 KPNB1 KPNB1 kpnb1 KPNB1 KPNB1 KPNB1 KPNB1 KPNB1 KPNB1 Kpnb1 KPNB1 KPNB1 NUP155 NUP155 NUP155 NUP155 NUP155 nup155 NUP155 NUP155 nup155 Nup155 NUP155 NUP155 NUP155 Nup155nup155 Nup155 Nup205 NUP205 NUP93 Nup93 NUP93 NUP93 Nup93 NUP93 NUP93 NUP93 NUP93 NUP93 NUP93 NUP93 NUP93 NUP93 NUP93 nup93 NUP93 NUP93 nup93 NUP93 NUP93 pcna PFKL PFKL PFKL PFKLPfkl PFKL Pfkl PFN1 PGK1 psmC5 psmD3 psmD7 Synj2bp SYNJ2BP Synj2bp Timm44 TIMM44 TIMM44 TIMM44 TIMM44 Timm44 TIMM44 timm44 TIMM50 TIMM50 TIMM50 TIMM50 TIMM50 timm50 TIMM50 Timm50TIMM50 Tmed2 TMED2 TMED2 tmed2 TMED2 TMED2 TMED2 TMED2 Tmed2 TMED2 TMED2 TMED2 UBB Vps35 vps35 VPS35 VPS35 VPS35 Vps35 vps35 VPS35 xpo1 XPO1 XPO1 Xpo1 Xpo1 XPO1 XPO1 XPO1 XPO1XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 xpo5 XPO5 Xpo5 XPO5 XPO5 XPO5 XPO5 Xpo5 XPO5 xpot Xpot XPOT XPOT XPOT xpot XPOT XPOT XPOT Xpot )	XPOT
GO:0044281small molecule metabolic process	266 ( ACAA2 ACAA2 Acaa2 Acaa2 Acot7 ACOT7 ACOT7 ACOT7 ACOT7 ACOT7 ACOT7 Acot7 ACP1 ACP1 ACP1 ACSL3 ACSL3 ACSL3 ACSL3 ACSL3 Acsl3 ACSL3 Acsl3 ACSL3 ADSL ADSL ADSL Adsl ADSL ADSLADSL ADSL ADSL adsl Adsl AIMP1 AIMP2 ASNS ASNS asnS ASNS ASNS Asns ASNS ASNS asnS asns ASNS Asns ASNS asns ASNS ASNS asnS ASNS ASNS Ctps CTPS ctps Ctps dcd dcd dcd dcd eif5a EIF5AEIF5A Eif5a Eif5a EIF5A EIF5A EIF5A eif5a EIF5A ENO1 ENO1 ERC1 FDPS Fdps FDPS FDPS Fdps GART GART GART GART GART GART GART gart Gart GART Gart GART GART GART GART GART GART GART GartGART GMPS Gmps Gmps GMPS gmps Gmps GMPS GMPS GMPS GMPS GMPS GSPT1 HK1 Hk1 IARS Iars IARS Iars iars IARS IARS IARS IARS IARS IARS Lars LARS LARS Lars LARS LARS LARS LARS LARS LARSLARS mcm5 mcm7 MTHFD1 MTHFD1 Mthfd1 MTHFD1 MTHFD1 MTHFD1 Mthfd1 Mthfd1 MTHFD1 MTHFD1 MTHFD1 MTHFD1 MTHFD1 Mthfd1 NAMPT Nampt Nampt NAMPT NAMPT NAMPT NAMPT Nampt NUP155 NUP205NUP93 PDIA6 PDIA6 PDIA6 Pdia6 PDIA6 PDIA6 PDIA6 Pdia6 PDIA6 PDIA6 PFKL PFKP PGK1 PGK1 PHGDH PHGDH Phgdh PHGDH PHGDH PHGDH PHGDH Phgdh PHGDH phgdh PRPS1 PRPS1 PRPS1 Prps1 PRPS1 Prps1PRPS1 PRPS1 PRPS1 Psmc5 Psmc5 PSMC5 PSMD3 PSMD7 Ptges3 PTGES3 PTGES3 Ptges3 PTGES3 QARS QARS QARS qars QARS QARS QARS QARS QARS QARS QARS RARS RARS rars RARS RARS RARS RARSRARS Rars RARS RARS Rars RDH11 TARS Tars tars TARS TARS TARS TARS Tars TARS TARS TARS TARS Tars Tmed2 TMED2 TMED2 Tmed2 TMED2 TMED2 TMED2 VARS VARS VARS Vars VARS VARS VARSVARS VARS Vars VARS vars VARS VARS )	UFM1
GO:0009056catabolic process	161 ( Acaa2 Acaa2 Acot7 ACOT7 ACOT7 Acot7 ACOT7 ACOT7 ACOT7 ACOT7 dcd dcd dcd EIF4A1 EIF4A1 ENO1 ENO1 ENO1 ENO1 ENO1 ENO1 ENO1 ENO1 Eno1 ENO1 ENO1 ENO1 ENO1 eno1 ENO1 ENO1 ENO1ENO1 GSPT1 Gspt1 Hk1 HK1 HK1 HK1 HK1 HK1 HK1 HK1 HK1 hk1 Hk1 HK1 HK1 HK1 HUWE1 Huwe1 KPNB1 mcm5 mcm7 PFKL PFKL Pfkl PFKL PFKL Pfkl PFKL PFKL PFKL PFKL PFKP PFKP PFKP PFKP PFKP PFKPPFKP PFKP Pfkp PFKP PFKP Pfkp PFKP PFKP PFKP PFKP Pfkp Pfkp PFKP PGK1 PGK1 PGK1 PGK1 PGK1 PGK1 Pgk1 PGK1 PGK1 pgk1 PGK1 PGK1 PGK1 PGK1 Pgk1 PGK1 PGK1 pgk1 PGK1 PGK1 PGK1 PGK1 PGK1PGK1 PGK1 PGK1 PKM2 PKM2 PKM2 PKM2 PPP2R1A PSMC5 psmc5 PSMC5 psmc5 PSMC5 Psmc5 Psmc5 PSMC5 psmC5 Psmd3 psmd3 PSMD3 psmD3 PSMD3 Psmd3 PSMD3 PSMD3 PSMD7 psmD7 stub1 STUB1 STUB1STUB1 Stub1 STUB1 Stub1 STUB1 Tmed2 TMED2 Tmed2 TMED2 TMED2 TMED2 TMED2 UBB XPO1 XPO1 XPO1 XPO1 XPO1 XPO1 Xpo1 Xpo1 XPO1 XPO1 XPO1 XPO1 )	HK1
GO:0006464cellular protein modification process	150 ( Acp1 acp1 Acp1 acp1 Aimp2 AIMP2 AIMP2 AIMP2 Aimp2 AIMP2 AIMP2 ASNS Cand1 CAND1 CAND1 CAND1 Cand1 CAND1 Cand1 CAND1 cand1 Eif2s1 EIF2S1 EIF2S1 EIF2S1 Eif2s1 EIF2S1 EIF2S1 eif5a Eif5aEIF5A EIF5A Eif5a EIF5A EIF5A EIF5A eif5a EIF5A ERC1 Erc1 ERC1 Erc1 ERC1 ERC1 ERC1 Fanci Fanci FANCI FANCI FANCI Fkbp4 FKBP4 FKBP4 FKBP4 fkbp4 Fkbp4 GSPT1 GSPT1 Gspt1 GSPT1 Gspt1 GSPT1 GSPT1Hk1 Hk1 HUWE1 HUWE1 huwe1 Huwe1 MCM3 MCM5 PDIA6 Ppp2r1a PPP2R1A Ppp2r1a PPP2R1A PPP2R1A PPP2R1A PPP2R1A Prkdc PRKDC PRKDC PRKDC Prkdc PSMC5 PSMD3 PSMD7 SAE1 Sae1 SAE1 sae1 Sae1sae1 sae1 SAE1 sae1 SAE1 SAE1 SAE1 stub1 STUB1 STUB1 STUB1 Stub1 STUB1 Stub1 STUB1 timm50 Timm50 TIMM50 TIMM50 TRIM28 Trim28 TRIM28 Trim28 UBB UBB Ube2m UBE2M Ube2m ube2m UBE2Mube2m ube2m UFC1 UFC1 ufc1 Ufc1 ufc1 Ufc1 UFC1 UFC1 ufc1 ufc1 UFM1 Ufm1 UFM1 UFM1 ufm1 UFM1 UFM1 Ufm1 UFM1 ufm1 UFM1 UFM1 UFM1 ufm1 ufm1 ufm1 )	GSPT1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[mat20]

correction I meant to say the Result column should have a formula or code to lookup PreyGenename adjacent to it, match it in the Total # GPs column and return the corresponding GO Slim Term for all the matches, for example the result for PRKDC would be GO:0034641cellular nitrogen compound metabolic process, GO:0009058biosynthetic process, GO:0006464cellular protein modification process

I have tried vlookup variations with multiple matches and multiple vb scripts from many forums but they returned with errors even after I modified the code to correspond my data, I think it has to do with the fact that the data in Total # GPs column is not separated by commas, will it be easier to convert the data to be separated with commas and maybe remove all the duplicates? also I think maybe the number in the beginning of each is causing issues

 

[mat20]

any help would be appreciated

 

[mat20]

I tried

=INDEX($B$2:$B$7, SMALL(IF(ISNUMBER(SEARCH($C$2, $A$2:$A$7)), MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7))), ROW(A1)))

it is returning #N/A

Edit: I replaced ISNUMBER with ISTEXT and it is only returning 1 occurance now (GO:0034641cellular nitrogen compound metabolic process)

 

[mat20]

I tried again and ran it with isnumber and Ctrl Shift Enter and it seems to populate more

{=INDEX($B$2:$B$7, SMALL(IF(ISNUMBER(SEARCH($C$2, $A$2:$A$7)), MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7))), ROW(A1)))}

 

[mat20]

is it possible to return the results in one cell?