function - Returning a value if three columns match in excel

Question

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.

Answer 1

Concatenate would work, as per @MakeCents suggestion, but if you don't want a helper column, SUMPRODUCT would work.

example:

=SUMPRODUCT(--(A2:A12="d"),--(B2:B12="S"),--(C2:C12="Apr"),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.

The -- is used to change the True/False results into 0 and 1 for use in multiplication

Limitations of SUMPRODUCT

• Recommended to specify the range explicitly; it will be slower with just column references
  (A1:A4000 is ok, A:A is not)
• It will return an error if any of the values are errors
• It will return numeric results only - text is evaluated as Zero