Understanding How Excel Processes Array Functions

0 votes

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 in Others by gaurav
• 22,040 points
28 views

1 answer to this question.

0 votes

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.

answered Nov 8 by gaurav
• 22,040 points

Related Questions In Others

0 votes
1 answer

How to export data with functions to another workbook in excel

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

answered Oct 30 in Others by narikkadan
• 37,660 points
31 views
0 votes
1 answer

How to check if array is multidimensional or not?

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

answered Nov 5, 2018 in Others by DataKing99
• 8,240 points
4,141 views
0 votes
1 answer

How can you create an Array in JavaScript?

You can define arrays using the array literal as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
274 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
137 views
0 votes
1 answer

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

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

answered Feb 16 in Others by Edureka
• 13,640 points
230 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17 in Others by Edureka
• 13,640 points
101 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18 in Others by gaurav
• 22,040 points
68 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10 in Others by gaurav
• 22,040 points
274 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
199 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP