mardi 16 avril 2019

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

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.

Aucun commentaire:

Enregistrer un commentaire