英文:
Excel - How to vlookup data from various columns
问题
现在，为了找到答案（我想知道数据中是否有1和4），我需要手动合并列1和列2的数据，并创建一个新的列。
如何从不同的列中使用vlookup()来查找答案？不需要手动合并数据吗？数据中有数百列，每一列都包含成千上万的数据。
英文:
Right now , in order to find the answer (I want to know if there is 1 and 4 in the data)
I need to manually combine the data from Column 1 and Column 2 and create a new Column
How to vlookup() the answer from various columns? not need to manually combine the data? There are hundreds of columns in the data, and each column contains thousands of data
答案1
得分: 2
根据评论，COUNTIFS() 可能适合您。请尝试 -
=IF(COUNTIFS(C3:D5,A3)>0,"是","否")
英文:
As per comment, COUNTIFS() may work for you. Try-
=IF(COUNTIFS(C3:D5,A3)>0,"Yes","No")
答案2
得分: 2
匹配2个数值
溢出
=LET(Data,C2:M21,Value1,1,Value2,4,y,"是",n,"否",
br,BYROW(Data,LAMBDA(r,
ISNUMBER(XMATCH(Value1,r)*XMATCH(Value2,r)))),
IF(br,y,n))
对于单行
真/假
=ISNUMBER(MATCH(1,C2:M2,0)*(MATCH(4,C2:M2,0)))
是/否
=IF(ISNUMBER(MATCH(1,C2:M2,0)*MATCH(4,C2:M2,0)),"是","否")
编辑：更灵活
- 您可以使用
TOCOL将值写入值列（v），排除空单元格和包含错误值的单元格。然后，您可以将每行的数字匹配数（COUNT()）与值列（v）的行数（c）进行比较。 - 现在
Values参数变得非常灵活。
=LET(Data,C2:M21,Values,O2:P11,y,"是",n,"否",
v,TOCOL(Values,3),c,ROWS(v),
br,BYROW(Data,LAMBDA(r,
COUNT(XMATCH(v,r))=c)),
IF(br,y,n))
英文:
Match 2 Values
Spilling
=LET(Data,C2:M21,Value1,1,Value2,4,y,"Yes",n,"No",
br,BYROW(Data,LAMBDA(r,
ISNUMBER(XMATCH(Value1,r)*XMATCH(Value2,r)))),
IF(br,y,n))
For a Single Row
TRUE/FALSE
=ISNUMBER(MATCH(1,C2:M2,0)*(MATCH(4,C2:M2,0)))
Yes/No
=IF(ISNUMBER(MATCH(1,C2:M2,0)*MATCH(4,C2:M2,0)),"Yes","No")
EDIT: More Flexibility
- You can use
TOCOLto write the values to the Value column (v) excluding empty cells and cells containing error values. Then you can compare the number of numeric matches per row (COUNT()) with the number of rows (c) of the Value column. - Now the
Valuesargument has become very flexible.
=LET(Data,C2:M21,Values,O2:P11,y,"Yes",n,"No",
v,TOCOL(Values,3),c,ROWS(v),
br,BYROW(Data,LAMBDA(r,
COUNT(XMATCH(v,r))=c)),
IF(br,y,n))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论