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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies