Excel: SMALL(), IF() and ROW() to look up row numbers of values using multiple criteria - AND() not working

Question

I have a reference spreadsheet called Purchases that looks similar to this:

        A           B              C              D
1       Ord_ID      Supplier       PO_Date        Receipt_Quantity
2       PO101       Aa             11/1/2017      5
3       PO102       Bb             12/1/2017      12
4       PO103       Cc             12/15/2017     100
5       PO104       Bb             1/15/2018      8
6       PO105       Dd             2/1/2018       30
7       PO106       Bb             3/1/2018       15
8       PO107       Bb             4/1/2018       10
...

I have a separate sheet called Supplier Bb Data that is supposed to return the row numbers of Purchases data for Supplier Bb. The formula looks like this, and is in cells A10, A11, A12, etc.:

=SMALL(IF('Purchases'!$B:$B=$A$1, ROW('Purchases'!$B:$B)), ROW(1:1))
=SMALL(IF('Purchases'!$B:$B=$A$1, ROW('Purchases'!$B:$B)), ROW(2:2))
=SMALL(IF('Purchases'!$B:$B=$A$1, ROW('Purchases'!$B:$B)), ROW(3:3))
...

A1 in Supplier Bb Data contains the the value to look up, "Bb".

This formula works; it correctlt returns row numbers from Purchases that include POs for Supplier Bb. (3, 5, 7, 8, etc.)

However, I want to be able to return more specific data: rows where Supplier = Bb AND PO_Date >= 1/1/2018, AND PO_Date <= 3/1/2018. Consequently, returning 5 and 7.

It seemed like it would be simple; I tried this (assuming 1/1/2018 and 3/1/2018 are in cells A2 and A3, respectively):

=SMALL(IF(AND('Purchases'!$B:$B=$A$1, 'Purchases'!$C$C>=$A$2, 'Purchases!$C$C<=$A$3), 
ROW('Purchases'!$B:$B)), ROW(1:1))

When I try this, excel simply returns 0 for the first row, and errors for all following rows.

Does AND() simply not work within an IF() statement? What else can I try to get this to work?

I always press Ctrl+Shift+Enter for these cells, because they are array formulas, so that is not the issue.

user11370591 · Accepted Answer · 2019-04-16 21:34:48Z

The AND function performs cyclic (array style) calculation by itself. Subsequently, AND or OR functions do not play well with others in an array style (CSE) formula. Stack the conditions in a nested IF or use AGGREGATE for your purposes.

'array style with CSE
=SMALL(IF(Purchases!$B:$B=$A$1, IF(Purchases!$C:$C>=$A$2, IF(Purchases!$C:$C<=$A$3, ROW($B:$B)))), ROW(1:1))

'standard style without CSE
=AGGREGATE(15, 7, ROW(B:B)/((Purchases!B:B=A$1)*(Purchases!C:C>=A$2)*(Purchases!C:C<=A$3)), ROW(1:1))

You were missing a : in 'Purchases'!$C$C.
All worksheets in a workbook have the same number of rows. ROW(B:B) can be used in place of ROW('Purchases'!$B:$B)).
The ROW(1:1) indicates that you are dragging down for successive k parameters. There is no need to lock the columns as absolute.
Array formulas or functions with cyclic calculation perform much more efficiently if you don't use full column references. Consider the following:
```
=AGGREGATE(15, 7, ROW($1:$999)/((Purchases!B$1:B$999=A$1)*(Purchases!C$1:C$999>=A$2)*(Purchases!C$1:C$999<=A$3)), ROW(1:1))
```

Justyna MK · Accepted Answer · 2019-04-16 21:49:20Z

0

Alternatively, you can combine your SMALL+IF with SUMPRODUCT function (Ctrl+Shift+Enter):

=IFERROR(SUMPRODUCT(SMALL(IF((Purchases!$B$1:$B$8=$A$1)*(Purchases!$C$1:$C$8>=$A$2)*(Purchases!$C$1:$C$8<=$A$3)=0,"",(ROW(Purchases!$B$1:$B$8))),ROW(1:1))),"")

answered Apr 16, 2019 at 21:49

Justyna MK

3,5633 gold badges15 silver badges25 bronze badges

Collectives™ on Stack Overflow

Excel: SMALL(), IF() and ROW() to look up row numbers of values using multiple criteria - AND() not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related