英文:
Excel - Vlookup with if,or,and
问题
在给定的数据范围B2:E22中 
我随后使用=FILTER(B3:B22,C3:C22>=7)来生成下面的三个较小的列。
然后，我尝试创建一个公式，将检查B2:B22中的每个值，并查看它是否出现在：
C27:C33 与 D27:D33 或 C27:C33 与 E27:E34。
我尝试过以下公式，但出现了#值错误。 有人可以帮忙吗？
=IF(OR(AND(VLOOKUP(B4,C27#,1,FALSE),VLOOKUP(B4,D27#,1,FALSE)),AND(VLOOKUP(B4,C27#,1,FALSE),VLOOKUP(B4,E27#,1,FALSE))), "是", "否")
谢谢
英文:
Given the data in b2:e22
I am then using =FILTER(B3:B22,C3:C22>=7) to produce the three smaller columns below.
What i am then trying to do is create a forumla which will check each value in B2:B22 and see if it occurs in:
C27:C33 AND D27:D33 or C27:C33 AND E27:E34.
I've tried the below but it fails with a #value error. Can anyone assist please?
=IF(OR(AND(VLOOKUP(B4,C27#,1,FALSE),VLOOKUP(B4,D27#,1,FALSE)),AND(VLOOKUP(B4,C27#,1,FALSE),VLOOKUP(B4,E27#,1,FALSE))),"Yup","Nope")
Thanks
答案1
得分: 1
An IF OR/AND Combination
在单元格 F3 中：
- 你不需要“小表”。
=LET(data,C3:E22,min,7,
data1,INDEX(data,,1),data2,INDEX(data,,2),data3,INDEX(data,,3),
IF((data1>=min)*(data2>=min)+(data1>=min)*(data3>=min),"是","否"))
源自简单的（拷贝下来）：
=IF(OR(AND(C3>=7,D3>=7),AND(C3>=7,E3>=7)),"是","否")
如果坚持，你可以像以下这样做（调整 H3#）：
=LET(n,B3:B22,d,H3#,min,7,
data1,INDEX(d,,1),data2,INDEX(d,,2),data3,INDEX(d,,3),
BYROW(n,LAMBDA(r,
IF(ISNUMBER(XMATCH(r,data1)*XMATCH(r,data2))
+ISNUMBER(XMATCH(r,data1)*XMATCH(r,data3)),"是","否"))))
在单元格 H3（溢出小表）：
=LET(n,B3:B22,v,C3:E22,min,7,
DROP(REDUCE("",SEQUENCE(,COLUMNS(v)),LAMBDA(rr,c,
IFERROR(HSTACK(rr,FILTER(n,INDEX(v,,c)>=min,"")),""))),,1))
注意，你需要锁定 B 列以向左拷贝，即溢出三列：
=FILTER($B3:$B22,C3:C22>=7)
英文:
An IF OR/AND Combination
In cell F3:
- You don't need the 'small table'.
=LET(data,C3:E22,min,7,
data1,INDEX(data,,1),data2,INDEX(data,,2),data3,INDEX(data,,3),
IF((data1>=min)*(data2>=min)+(data1>=min)*(data3>=min),"Yup","Nope"))
derived from the simple (copy down):
=IF(OR(AND(C3>=7,D3>=7),AND(C3>=7,E3>=7)),"Yup","Nope")
If you insist, you could do something like the following (adjust the H3#):
=LET(n,B3:B22,d,H3#,min,7,
data1,INDEX(d,,1),data2,INDEX(d,,2),data3,INDEX(d,,3),
BYROW(n,LAMBDA(r,
IF(ISNUMBER(XMATCH(r,data1)*XMATCH(r,data2))
+ISNUMBER(XMATCH(r,data1)*XMATCH(r,data3)),"Yup","Nope"))))
In cell H3 (spilling the small table)
=LET(n,B3:B22,v,C3:E22,min,7,
DROP(REDUCE("",SEQUENCE(,COLUMNS(v)),LAMBDA(rr,c,
IFERROR(HSTACK(rr,FILTER(n,INDEX(v,,c)>=min,"")),""))),,1))
- Note that you need to lock the
Bcolumn to copy to the left i.e. to spill three columns:
=FILTER($B3:$B22,C3:C22>=7)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论