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.

You could CONCATENATE those cells in both sheets and then use a VLOOKUP? — MakeCents, Aug 21 '13 at 14:04

SeanC · Accepted Answer · 2014-02-12T20:02:27.150

9

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

edited Feb 12 '14 at 20:02

answered Aug 21 '13 at 14:27

SeanC

15,695
5
45
66

I tested using full columns (e.g., `A:A`) with Excel 2010, and it works ok. – sancho.s ReinstateMonicaCellio Feb 12 '14 at 19:41
ok... I'll change that to recommend, as a fixed row count is (usually) faster than an open row count, which could conceivably check 1e6+ rows – SeanC Feb 12 '14 at 20:01
how can we match from three columns if i have to compare values from three columns like less then equql to "<=". i am struggling in this condition. – Vikky Jun 18 '16 at 06:58
A robust version that also copes with text is shown [here](https://stackoverflow.com/a/21738182/2707864). – sancho.s ReinstateMonicaCellio Jan 24 '18 at 00:44

score 1 · Answer 2 · answered Aug 21 '13 at 14:32

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!

score 1 · Answer 3 · edited May 23 '17 at 12:01

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.

score 0 · Answer 4 · edited Dec 01 '14 at 01:05

0

Try this

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

edited Dec 01 '14 at 01:05

Kevin Kopf

13,327
14
49
66

answered Dec 01 '14 at 00:34

Vinod

1

6

please add some explanations how this answer be a solution to the OP's question – Yohanes Khosiawan 许先汉 Dec 01 '14 at 01:04
This may well solve the problem, but there are lots of newbies on Stack Overflow, and they could learn a thing or two from you if only you explain how/why this solution solves the problem. Please edit your answer and add a bit of explanation. – Taryn East Dec 01 '14 at 01:24

Returning a value if three columns match in excel

4 Answers4

Linked