This question is based on an someone else's question that I answered. You can find the original question here, if desired. (And, maybe provide a better answer than I have.) However, everything you need to answer this question is right here.

Background

Given the following data in A1:C5

```+-------------+--------+----------+
| Invoice Nbr |  Type  |  Status  |
+-------------+--------+----------+
| A0001       | Credit | Paid     |
| A0002       | Credit | Not Paid |
| B0001       | Debit  | Paid     |
| B0002       | Debit  | Not Paid |
+-------------+--------+----------+
```

The goal was to find a particular value in the table using an array formula that evaluated two columns. That is, give me the first Invoice (Column A) where Type is Debit and Status is Not Paid.

My first answer was an attempt to fix what I thought to be a problem with the OP's conditional. I put an AND wrapper around the two conditions as follows:

```{=INDEX(\$A\$2:\$A\$5,
SMALL(IF(AND(\$B\$2:\$B\$5 = "Debit",
\$C\$2:\$C\$5 = "Not Paid"),
ROW(\$A\$2:\$A\$5)-ROW(\$A\$2)+1),
1)
)}
```

However, that didn't work.

I ended up suggesting this answer, because it actually works:

```{=INDEX(\$A\$2:\$A\$5,
SMALL(IF(\$B\$2:\$B\$5 & \$C\$2:\$C\$5 = "DebitNot Paid",
ROW(\$A\$2:\$A\$5)-ROW(\$A\$2)+1),
1)
)}
```

My Question

Array formulae in Excel are sometimes so much voodoo to me. It seems like either one should provide the result B0002. In fact, only the second formula gives the desired result. What principle or evaluation process is Excel (2013) following that makes this so? Or, in other words, what am I not understanding about how Excel manages array formulae?

Nov 7, 2022 in Others 186 views

## 1 answer to this question.

An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result.

• 23,220 points

## How to export data with functions to another workbook in excel

The issue appears to be that once ...READ MORE

## In Excel Sheet how to Eliminate or Remove, Filter and copy the selected records defined in another sheet using dynamic array list (VBA Module)

I'm attempting to generate an Excel sheet ...READ MORE

## How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab

Use SORTBY, e.g. =SORTBY(A2:B5,A2:A5,1,B2:B5,1) Or simply&nb ...READ MORE

## How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE