問題描述
Excel匹配兩列並輸出第三個 (Excel match two columns and output third)
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.
參考解法
方法 1:
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 .
方法 2:
is that what you need ?
=IF(AND(A1="foo",B1="boo"),"boo",0)
方法 3:
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.
方法 4:
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.
(by zzzzzzzzzzz、chancea、Udy、Eric Thomas、HIN)