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.

Solution 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

Solution 2

You are looking for a Lookup with multiple criteria.

One of the most robust options is

=INDEX(D:D,SUMPRODUCT(--(A:A="d"),--(B:B="S"),--(C:C="Apr"),ROW(D:D)),0)

It does not need to be entered as an array formula. Taken from [1] (blogs.office.com).

See also this very complete answer, which summarizes this and other options for performing a lookup with multiple criteria.

PS1: Note that I used references to full columns, as per this.

PS2: This can be considered an enhancement to the solution by Sean for the case when the output column does not contain numbers.

References

[1] This post is written by JP Pinto, the winner of the Great White Shark Award given for the best article written about VLOOKUP during VLOOKUP Week.

Solution 3

Although I believe @MakeCents comment / suggestion on how to do this is the way I would go since it is the simplest, you could accomplish this a different way (MUCH more processor-intensive, though) using the Index() and Match() functions and Array formulas.

For example, suppose your 3 columns of data you're looking to match against are columns A-C and you're looking to return the matching value from column D in Sheet1

Now, the 3 values you're looking to have matched are in cells A1, B1 & C1 of Sheet2, you could use the following formula:

=INDEX(Sheet1!D:D,MATCH(1,(Sheet1!A:A=A1)*(Sheet1!B:B=B1)*(Sheet1!C:C=C1),0))

And ENTER IT AS AN ARRAY FORMULA by pressing Ctrl + Shift + Enter

Hope this helps!