F2单元格输入以下公式,然后向下填充公式
=IFERROR(LOOKUP(1,0/((A$2:A$6<>"")*FIND(A$2:A$6,E2)),C$2:C$6),LOOKUP(1,0/((B$2:B$6<>"")*FIND(B$2:B$6,E2)),C$2:C$6))
详见附图示例
=LOOKUP(MID(E2,2,1),$A$2:$C$6) ,E列格式固定的话,可以用这个公式。
追问不固定格式,也可能在开头,也可能在结尾
追答G2单元格输入下列公式:LOOKUP(CHAR(SMALL(CODE(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1)),1)),$A$2:$C$6)
同时按CTRL+SHIFT+ENTER结束公式
补充:或者用这个公式:
=LOOKUP(CHAR(SMALL(CODE(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1)),1)),{"A","B","C","D","E","F","G","H"},{"小黄","小黄","小红","小红","小绿","小紫","小黑","小黑"})
同时按CTRL+SHIFT+ENTER结束公式
Excel
追答在Excel中写代码