Return value if three columns match in excel

Question

I have two excel sheets where I need to match three values ​​to return the fourth. Similar columns are month, agent, and subdomain. The fourth column is called the difference.

Answer 1

Concatenation will work as suggested by @MakeCents, but if you don't want to use an auxiliary column, SUMPRODUCT will work.

example:

 =SUMPRODUCT(--(A2:A12="d"),--(B2:B12="S"),--(C2:C12="Apr"),D2:D12) 

will look for the range A2: A12 for "d", B2: B12 for "S" and C2: C12 for "Apr" and return the value fom D2: D12, which matches where all 3 are true. If multiple lines match, this will add a value to D2: D12 for all matching lines.

-- used to change True / False results to 0 and 1 for use in multiplication

Limitations of SUMPRODUCT

• It is recommended that you specify the range explicitly; it will be slower with column references
  ( A1: A4000 is OK, A: A is not)
• It will return an error if any values ​​are errors
• It will only return numerical results - the text evaluates to zero

Answer 2

Although I believe that @MakeCents comment / suggestion on how to do this is how I will go, as it is the easiest, you could do it differently (MANY, more processor intensive, though) using Index() and Match() and array formulas.

For example, suppose your 3 columns of data that you want to map are AC columns, and you want to return the corresponding value from column D to Sheet1

Now the three values ​​you want to match are in cells A1, B1 and C1 Sheet2, you can 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 AS MACHINE FORMULAS by pressing Ctrl + Shift + Enter

Hope this helps!

Answer 3

You are looking for a search with several criteria .

One of the most reliable 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 . Adapted from [1] (blogs.office.com) .

See also this very complete answer , which summarizes these and other parameters to perform a search with several criteria.

PS1: Please note that I used links to full columns, in accordance with this .

PS2: this can be seen as an improvement to Sean's solution for the case where the output column does not contain numbers.

References

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

Answer 4

try it

 =IF(A4=Data!$A$4:$A$741,IF(B4=Data!$B$4:$B$741,"Hai"))