Apr 11 2019 06:45 PM
Apr 11 2019 08:31 PM
Apr 12 2019 01:41 AM
@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?
Apr 12 2019 05:14 AM
Apr 12 2019 06:29 AM
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
Apr 12 2019 08:01 AM
Apr 12 2019 08:04 AM
@moloco , you are welcome
Apr 12 2019 06:29 AM
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