问题描述
我有两个 Excel 工作表，我需要匹配三个值才能返回第四个值.类似的列是月份、代理和子域.第四列称为差异.
I have two excel sheets where I need to match three values to return a fourth. The similar columns are month, agent, and subdomain. The fourth column is called difference.
推荐答案
根据@MakeCents 的建议，Concatenate 会起作用，但如果您不想要辅助列，SUMPRODUCT 会起作用.
Concatenate would work, as per @MakeCents suggestion, but if you don't want a helper column, SUMPRODUCT would work.
示例:
=SUMPRODUCT(--(A2:A12="d"),--(B2:B12="S"),--(C2:C12="Apr"),D2:D12)
将搜索范围 A2:A12 为d"，B2:B12 为S"，C2:C12 为Apr"，并返回 D2:D12 中对应于所有 3 项都为真的值.如果多行匹配，它将为所有匹配的行添加 D2:D12 中的值.
would search range A2:A12 for "d", B2:B12 for "S" and C2:C12 for "Apr", and return the value fom D2:D12 that corresponds to where all 3 are true. If multiple lines match, it will add the value in D2:D12 for all matching rows.
-- 用于将 True/False 结果变为 0 和 1 以供乘法使用
The -- is used to change the True/False results into 0 and 1 for use in multiplication
SUMPRODUCT 的限制
- 建议明确指定范围；只是会变慢列引用
(A1:A4000 可以，A:A 不行) - 如果任何的值是错误的，它会返回一个错误
- 它只会返回数字结果 - 文本被评估为零
这篇关于如果三列在 Excel 中匹配，则返回一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！