问题描述
我会像一个在第一列和第二列上迭代的公式,如果
则返回第三列。列1 =aAND
列2 =d / p>
公式应该返回相应行的第三列中的值,在这种情况下,
将为3.否则,应输出0。 p>
您可以使用公式:
= IF(MATCH( foo 的,A1:A4,0)= MATCH( 栏,B1:B4,0),INDEX(C1:C4,MATCH( 栏,B1:B4,0) ),0)
当然,您可以更改foo code>和
bar
在公式中使用另一个单元格引用的文本。无论如何,这应该让你开始至少。
编辑:
如果bar
只在列 B
中找到一次。然后可以使用
= IF(INDIRECT(A& MATCH(bar,B1:B4,0))=foo,INDEX(C1:C4,MATCH bar,B1:B4,0)),0)
最后一件事,如果在列B中找不到bar
,那么如果要更改,则它将返回#N / A
您可以将整个内容包装在一个 IFERROR()
语句中,并返回0。
I would like a formula that iterates over the first and second column and returns the third column if
Column 1 = "a" ANDColumn 2 = "d"
the formula should return the value in the third column of the corresponding row, in this caseit would be 3. otherwise, it should output 0.
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 .
这篇关于Excel匹配两列并输出第三的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!