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
Highlighted
moloco
New Contributor
Please view attached Excel and read comment for instruction and detail
6 Replies
Highlighted
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
Highlighted

@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?

Highlighted
Highlighted
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

Highlighted
Highlighted
Related Conversations
SharePoint Lists - Expanded View Roadmap ID: 57302
Kotus-Tech in SharePoint on
9 Replies
Multi-value fields display no separator
Florian Hein in SharePoint on
1 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies