Forum Discussion
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
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
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
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?
- Yes
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
- Gracias !