麻烦大神帮我看看这个EXCEL的公式

hwbgdchina

我想在表1的A列输入姓名后，B列从表2的中查找出对应表1 A列输入姓名的物品，但是用了几个公式都不行，要么是N/A，要么返回的是0。求助大神解救！

苏小亓

B列都是空的你要返回啥？

qwer401245

=REGEXP(VLOOKUP("*"&A2&"*",Sheet2!A:A,1,),"(?<=购入).+")

mykitami

=LET(a,SUBSTITUTE("、"&Sheet2!$A$2:$A$4,"购入","、购入"),TEXTJOIN("、",1,TEXTAFTER(FILTER(a,ISNUMBER(FIND("、"&A2&"、",a))),"购入")))

ww87725244

=MID(VLOOKUP("*"&A2&"*",Sheet2!$A$2:$A$4,1,),FIND("入",VLOOKUP("*"&A2&"*",Sheet2!$A$2:$A$4,1,))+1,99)

袁希

B2=LET(aa,FILTER(Sheet2!$A$2:$A$4,IFERROR(SEARCH(A2,Sheet2!$A$2:$A$4),0)),MID(aa,SEARCH("购入",aa)+2,20))

袁勇

=IFERROR(TEXT(-1,SUBSTITUTE(VLOOKUP("*"&A2&"*",Sheet2!$A$2:$B$4,1,FALSE),"购入",";")),"未找到匹配项")

梦从东方来Frank

B2单元格输入

1. =RIGHT(VLOOKUP("*"&A2&"*",Sheet2!A:A,1,0),LEN(C2)-FIND("入",C2))

复制代码

hwbgdchina

谢谢楼上各位大佬的回复

inrever

1. =DROP(REDUCE("",A2:A5,LAMBDA(m,n,VSTACK(m,TEXTJOIN(",",1,IFERROR(IF(FIND(n,Sheet2!A2:A4),TEXTAFTER(Sheet2!A2:A4,"购入"),),""))))),1)

复制代码