CODEWITHSUNDEEP
excelexcel-2007excel-formula
zzzzzzzzzzz
zzzzzzzzzzz

Reputation: 250

Excel match two columns and output third

enter image description here

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.

Upvotes: 2

Views: 30298

Answers (4)

Udy
Udy

Reputation: 2532

is that what you need ?

=IF(AND(A1="foo",B1="boo"),"boo",0)

Upvotes: 0

HIN
HIN

Reputation: 21

I do not think Excel has this feature. If you are looking up numbers, there is a work around though:

=IF(COUNTIFS($A$1:$A$4,"a",$B$1:$B$4,"d") = 1, SUMIFS($C$1:$C$4,$A$1:$A$4,"a",$B$1:$B$4,"d"), "ERR")

This will yield the number if there is exactly one match, and "ERR" if there are none or many matches. If you try to use it to look up text, it will return 0.

Upvotes: 0

Eric Thomas
Eric Thomas

Reputation: 717

I think, you are looking for something like this, assuming column 1 is A1 and column 2 is B1:

=IF(AND(A1="foo",B1="bar"),"bo",0)

If you have multiple values that you need column 3 to be you can do an embedded if statement like for your second row:

=IF(AND(A1="foo",B1="bar"),"bo",IF(AND(A1="fui",B1 = "bas"),"bis",0))

Basically where you would have 0, you write the next if statement and it will run through, until it hits true or defaults to 0.

Upvotes: 0

chancea
chancea

Reputation: 5958

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 .

Upvotes: 4

Related Questions