Thank U Very MuchFor Answer Of "Color" File
In "Sheet1" G4, formula copied down :
=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$4:$C$10)/ISNUMBER(FIND($B$4:$B$10,$E$4)),ROWS($1:1))),"")
Regards
Bosco
Thanks a lotView attachment 57513
For "YBC" sheet file
1] In G5, array formula copied right to H5 and all copied down :
=SUM(INDEX($C$4:$C$9,N(IF(1,FIND(MID($F5,ROW(INDIRECT("1:"&LEN($F5))),1),"ABCXYZ"))))*IF(G$4="Sum",1,10^(LEN($F5)-ROW(INDIRECT("1:"&LEN($F5))))))
p.s. array formula to be confirmed enter with Shift+Ctrl+Enter 3 keystroke
Regards
Bosco
861171314
No Vb,
with UDF
with use of write needed cells and ranges to UDF as in samples
but without all possible error handling!
excuse meView attachment 57513
For "YBC" sheet file
1] In G5, array formula copied right to H5 and all copied down :
=SUM(INDEX($C$4:$C$9,N(IF(1,FIND(MID($F5,ROW(INDIRECT("1:"&LEN($F5))),1),"ABCXYZ"))))*IF(G$4="Sum",1,10^(LEN($F5)-ROW(INDIRECT("1:"&LEN($F5))))))
p.s. array formula to be confirmed enter with Shift+Ctrl+Enter 3 keystroke
Regards
Bosco
excuse me
sum is correct in H5 but convert to number in incorrect if we change B to 12 for example
HiView attachment 57546
For "YBC" sheet file in changing "Convert Table" number to 2 digits
1] Select G5 >> Define Name >>
Name : MatchNum
Refers to : =INDEX($C$4:$C$9,N(IF(1,FIND(MID(Sheet1!$F5,ROW(INDIRECT("1:"&LEN($F5))),1),"ABCXYZ"))))
>> OK
Then, in G5 enter formula and copied down :
=IFERROR(INDEX(MatchNum,1),"")&IFERROR(INDEX(MatchNum,2),"")&IFERROR(INDEX(MatchNum,3),"")&IFERROR(INDEX(MatchNum,4),"")&IFERROR(INDEX(MatchNum,5),"")&IFERROR(INDEX(MatchNum,6),"")
2] In H5 formula copied down :
=SUM(MatchNum)
Regards
Bosco