Forum Discussion

moloco's avatar
moloco
Copper Contributor
Apr 12, 2019
Solved

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

Please view attached Excel and read comment for instruction and detail
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 12, 2019

    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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 12, 2019

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?

moloco's avatar
moloco
Copper Contributor
Apr 12, 2019
Yes
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 12, 2019

    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

    • moloco's avatar
      moloco
      Copper Contributor
      Apr 12, 2019
      Gracias !

Resources