如何在Excel数组公式中使用嵌套的IF（AND）？ [英] How do I use a nested IF(AND) in an Excel array formula?

问题描述

我将问题归结为以下示例：

注意：上述图像已更新包括数组公式花括号

在右上角，我们在L3（color）和L4（shape）中有搜索条件。在左侧，列D包含项目列表中颜色和形状的工作匹配公式。第一个表格显示了匹配公式在不使用数组公式的情况下正常工作。

第二个表格显示了匹配颜色的数组公式。

第三个表显示与形状匹配的数组公式。

右边是我尝试在数组公式中使用两个标准，如果颜色列中的值与颜色标准（L3）匹配，并且形状列中的值与形状标准（L4）匹配，则将其与AND结合使用。

我想看到MATCH！。

我找到了一个解决方法：连接值和条件，然后在一个IF中匹配它们。我觉得应该有一个更好的方式，就像如果AND按预期工作！

注意：
很多以下答案正确，但不是数组公式，具体是这个问题是什么。我看了我的原始问题，意识到我忘了在数组公式示例中显示花括号。我已经修复了图像来显示它们。对不起，感到困惑。

回答这些问题的关键是写一些作为数组公式，通过在将公式输入单元格后按CTRL + SHIFT + ENTER输入。 Excel将自动添加花括号表示它是一个数组公式。

解决方案

尝试使用下一个数组公式：

  = IF（（$ J $ 16：$ J $ 22 = $ L $ 3）*（$ K $ 16： $ K $ 22 = $ L $ 4），MATCH！， - ）
  

链接到测试工作簿。

How do I get a nested 'AND' to work inside 'IF' in an array formula?

I reduced my problem to the following example: Note: the above image has been updated to included the array formula curly braces

At the top right, we have the search criteria in L3 ("color") and L4 ("shape"). At the left, column D contains working match formulas for both color and shape in the list of items. The first table shows the match formula working properly without using an array formula.

The second table shows an array formula that matches the color.

The third table shows an array formula that matches the shape.

On the right is my attempt to use both criteria in an array formula, by combining them with AND.

IF the value in the color column matches the color criteria (L3) and the value in the shape column matches the shape criteria (L4), then I want to see "MATCH!".

I did find a workaround: concatenate the values and criteria, and then match them inside a single IF. I feel like there should be a Better Way... like if AND worked as expected!

Note: Many of the answers below work correctly but not as array formulas, which is specifically what this question is about. I looked at my original question and realized I forgot to show the curly braces in the array formula examples. I have fixed the image to show them. Sorry for the confusion.

The key to answering these questions is to write something that works as an array formula, which is entered by pressing CTRL+SHIFT+ENTER after typing the formula into a cell. Excel will automaically add the curly braces to indicate that it's an array formula.

解决方案

Try to use next array formula:

=IF(($J$16:$J$22=$L$3)*($K$16:$K$22=$L$4),"MATCH!","-")

here is a link to the test workbook.

这篇关于如何在Excel数组公式中使用嵌套的IF（AND）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！