如果三列在 Excel 中匹配，则返回一个值 [英] Returning a value if three columns match in excel

问题描述

我有两个 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 中匹配，则返回一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！