Excel match two columns and output third

Question

I would like a formula that iterates over the first and second column and returns the third column if

Column 1 = "a" AND Column 2 = "d"

the formula should return the value in the third column of the corresponding row, in this case it would be 3. otherwise, it should output 0.

Solution

You can use the formula:

=IF(MATCH("foo",A1:A4,0)=MATCH("bar",B1:B4,0),INDEX(C1:C4,MATCH("bar",B1:B4,0)),0)

of course you can change the "foo" and "bar" text within the formula to use another cell reference. Anyways, this should get you started at least.

Edit:

If "bar" is only found once in column B Then you can use

=IF(INDIRECT("A"&MATCH("bar",B1:B4,0))="foo",INDEX(C1:C4,MATCH("bar",B1:B4,0)),0)

One last thing, for both cases, if "bar" is never found in column B it will return #N/A if you want to change that you can wrap the whole thing in an IFERROR() statement and return your 0 .

Comments

[zzzzzzzzzzz]

this is what i am looking for. however, if i have multiple matches in the first column, it does not work. I tried to change the match mode to 1, but this gives odd behavior.

[zzzzzzzzzzz]

i need to apply this to a range though

[chancea]

@Matt multiple matches like you have "foo" and "bar" together more than once?

[zzzzzzzzzzz]

No, they only appear together once, but there are many "foo" in column 1

[chancea]

@Matt can there be more than one "bar" in column 2?