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

问题描述

如何让嵌套的AND"在数组公式中的IF"内工作?

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

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

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.

右边是我尝试在数组公式中使用这两个条件，将它们与 AND 结合起来.

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

如果颜色列中的值与颜色标准(L3)匹配并且形状列中的值与形状标准(L4)匹配，那么我想看到匹配！".

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!".

我确实找到了一种解决方法:连接值和条件，然后在单个 IF 中匹配它们.我觉得应该有更好的方法......就像如果并按预期工作一样！

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.

回答这些问题的关键是编写一些类似于 数组公式，在单元格中输入公式后按 CTRL+SHIFT+ENTER 输入.Excel 会自动添加花括号以表明它是一个数组公式.

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.

推荐答案

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

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

这是测试工作簿的链接.

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