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

[moloco]

Please view attached Excel and read comment for instruction and detail

[#2]

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

[#3]

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

[#4]

Yes

[#5 · Accepted 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

[#6]

Gracias !

[#7]

@moloco , you are welcome