我想用VLOOKUP函数，模糊匹配包含三个关键字，匹配一个编号，怎么写这个公式呢

墨墨20240831

我想用VLOOKUP函数，模糊匹配包含三个关键字，匹配一个编号，请问怎么写这个公式呢

loirol

折磨王。。

=VLOOKUP(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"颜色分类:",""),"【",""),"】地垫;款式:",""),FIND(";",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"颜色分类:",""),"【",""),"】地垫;款式:",""))-1),IF({1,0},编号对应表!$B$2:$B$8&编号对应表!$A$2:$A$8&编号对应表!$C$2:$C$8,编号对应表!$D$2:$D$8),2,)

wp231957

=IFERROR(INDEX(编号对应表!$D$2:$D$8,MATCH(1,ISNUMBER(FIND(编号对应表!$A$2:$A$8,A2))*ISNUMBER(FIND(编号对应表!$B$2:$B$8,A2))*ISNUMBER(FIND(编号对应表!$C$2:$C$8,A2)),0)),"无符合要求编号")

mjm04

你都用WPS了，放过VLOOKUP吧，多条件查找 本来就不是VLOOKUP的活


下面三个函数，那个不比它好用好理解？  


=XLOOKUP(1,1/(REGEXP(A2,编号对应表!$A$1:$A$8,1)*REGEXP(A2,编号对应表!$B$1:$B$8,1)*REGEXP(A2,编号对应表!$C$1:$C$8,1)),编号对应表!$D$1:$D$8,"未查到")



=IFNA(LOOKUP(1,0/(REGEXP(A2,编号对应表!$A$1:$A$8,1)*REGEXP(A2,编号对应表!$B$1:$B$8,1)*REGEXP(A2,编号对应表!$C$1:$C$8,1)),编号对应表!$D$1:$D$8),"未查到")



=FILTER(编号对应表!$D$1:$D$8,(REGEXP(A2,编号对应表!$A$1:$A$8,1)*REGEXP(A2,编号对应表!$B$1:$B$8,1)*REGEXP(A2,编号对应表!$C$1:$C$8,1)),"未查到")


需均要下拉

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


你的编号表里  【蓝色小熊】  的编号是重复的，3个条件都是一样的！

422566739

兄弟请测试下

hcm19522

B2{=TEXT(INDEX(G:G,RIGHT(MAX(TEXT(MMULT(1-ISERR(FIND($D$2:$F$7,A2)),{5;3;1})/1%+ROW($2:$7),"[>100]0;99")*1),2)),"[=]無;#")

Excel_muke

用VLOOKUP的话，配合通配符"*"可以实现

1. =LET(a,TEXTJOIN(",",1,IF(ISTEXT(VLOOKUP("*"&编号对应表!$B$2:$B$8&"*"&编号对应表!$A$2:$A$8&"*"&编号对应表!$C$2:$C$8&"*",A2,1,0)),编号对应表!$D$2:$D$8,"")),IF(a="","未查到",a))

复制代码

wyslw2004

=XLOOKUP(0,BYROW(--ISERR(FIND(编号对应表!$A$2:$C$8,产品!A2)),SUM),编号对应表!$D$2:$D$8,"")
如果编号对应没有重复，也可以

=FILTER(编号对应表!$D$2:$D$8,BYROW(--ISERR(FIND(编号对应表!$A$2:$C$8,A2)),SUM)=0,"")