SOLVED
Home

How to : create a formula that look for a certain keyword, match from a list and provide output

%3CLINGO-SUB%20id%3D%22lingo-sub-442038%22%20slang%3D%22en-US%22%3EHow%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-442038%22%20slang%3D%22en-US%22%3EPlease%20view%20attached%20Excel%20and%20read%20comment%20for%20instruction%20and%20detail%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-442038%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-442331%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-442331%22%20slang%3D%22en-US%22%3ECan%20you%20do%20the%20same%20thing%20as%20this%20video%20but%20instead%20of%20looking%20for%20an%20exact%20match%20it%20search%20if%20A1%20(in%20return%20reference%20sheet)%20contains%20text%20from%20the%20range%20of%20customer%20ID%20that%20he%20selected%20(in%20customers%20sheet)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FTKcdtSmZvyo%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FTKcdtSmZvyo%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-443453%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-443453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F318870%22%20target%3D%22_blank%22%3E%40moloco%3C%2FA%3E%26nbsp%3B%2C%20do%20you%20mean%20extract%20code%20from%20the%20name%20like%20%22E011223_Centrum_DSR_Om8%22%20(DSR%20in%20this%20case)%20and%20based%20on%20it%20return%20Deliverable%20Name%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-444410%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-444410%22%20slang%3D%22en-US%22%3EYes%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-444858%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-444858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F318870%22%20target%3D%22_blank%22%3E%40moloco%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24G%243%3A%24G%249%2C%0A%20%20MATCH(MID(A3%2C%0A%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(MID(A3%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(A3)))%2C1)%3D%22_%22)*ROW(INDIRECT(%221%3A%22%26amp%3BLEN(A3)))%2C2)%2B1%2C%0A%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(MID(A3%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(A3)))%2C1)%3D%22_%22)*ROW(INDIRECT(%221%3A%22%26amp%3BLEN(A3)))%2C1)%20-%0A%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(MID(A3%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(A3)))%2C1)%3D%22_%22)*ROW(INDIRECT(%221%3A%22%26amp%3BLEN(A3)))%2C2)-1)%2C%0A%20%20%20%20%20%24F%243%3A%24F%249%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3EAGGREGATE%20finds%20last%20and%20previous%20to%20it%20position%20of%20%22_%22%20between%20which%20we%20extract%20the%20code%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-445678%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-445678%22%20slang%3D%22en-US%22%3EGracias%20!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-445700%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20%3A%20create%20a%20formula%20that%20look%20for%20a%20certain%20keyword%2C%20match%20from%20a%20list%20and%20provide%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-445700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F318870%22%20target%3D%22_blank%22%3E%40moloco%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
moloco
New Contributor
Please view attached Excel and read comment for instruction and detail
6 Replies
Can you do the same thing as this video but instead of looking for an exact match it search if A1 (in return reference sheet) contains text from the range of customer ID that he selected (in customers sheet)

https://youtu.be/TKcdtSmZvyo

@moloco , do you mean extract code from the name like "E011223_Centrum_DSR_Om8" (DSR in this case) and based on it return Deliverable Name?

Solution

@moloco , that could be like

=IFERROR(INDEX($G$3:$G$9,
  MATCH(MID(A3,
      AGGREGATE(14,6,1/(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="_")*ROW(INDIRECT("1:"&LEN(A3))),2)+1,
      AGGREGATE(14,6,1/(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="_")*ROW(INDIRECT("1:"&LEN(A3))),1) -
            AGGREGATE(14,6,1/(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="_")*ROW(INDIRECT("1:"&LEN(A3))),2)-1),
     $F$3:$F$9,0)),"no such")

AGGREGATE finds last and previous to it position of "_" between which we extract the code

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies