模糊查询

queenie_jing

问题如图，请问老师，怎么用公式模糊匹配数据呀？希望只要包含XXX,就返回对应数据。

jerrike

1. =LOOKUP(,0/(FIND($A$2:$A$17,D3)),$B$2:$B$17)

复制代码

沈默00

=XLOOKUP(TEXTBEFORE(D2,"-"),A:A,B:B)

你是那块小饼干

1. =LOOKUP(1,0/FIND($A$2:$A$17,D2),$B$2:$B$17)

复制代码

hugeinfo

=FILTER($B$2:$B$17,ISNUMBER(FIND($A$2:$A$17,D2)))

秃头发师

=CONCAT(IF(ISNUMBER(FIND($A$2:$A$17,D2)),$B$2:$B$17,""))

jiaxianwa

=LOOKUP(1,0/FIND(LEFT(D3,4),$A$2:$A$17),$B$2:$B$17)

jiaxianwa

=INDEX($B$2:$B$17,MATCH(0,MMULT(ISNUMBER(FIND(MID(D2,COLUMN($A:$D),1),$A$2:$A$17))-1,ROW($1:$4)),),)

my0745

1. =TOCOL(MAP($A$2:$A$150,LAMBDA(x,REGEXP(x,".*"&REGEXP(D2,"(.)",2,"\1.*")))),2)

复制代码

mjm04

高版本：
=MAP(D2:D3,LAMBDA(_a,XLOOKUP(REGEXP(_a,"^[一-龢]+"),$A$2:$A$17,$B$2:$B$17)))

低版本：
=VLOOKUP(LEFT(D2,FIND("-",D2)-1),$A$2:$B$17,2,0)
回车下拉

=====================================================================

如果拆出来的关键字与A列不一致的，加星号*处理

=MAP(D2:D3,LAMBDA(_a,XLOOKUP("*"&REGEXP(_a,"^[一-龢]+")&"*",$A$2:$A$17,$B$2:$B$17,,2)))

=VLOOKUP("*"&LEFT(D2,FIND("-",D2)-1)&"*",$A$2:$B$17,2,0)