Index/Array Formula to create a dynamic list dependent on criteria?

    Hello -


    Problem: Is there a single INDEX or Array formula to return loan numbers in a Column, top to bottom, based on met criteria?


    I have two tabs. One with data, and another I'd like to place a single formula to return loan numbers based on criteria.


    Tab #1 = Loan Detail: To house data points in Columns A-C.

    Tab #2 = Formula Tab: To house the formula to return loan numbers based on criteria specified in Column A.


    Criteria for Loan Detail Tab:

    1. Date in Colum B must be blank.

    2. Type in Column C must be "Cash".


    Criteria for Formula Tab:

    3. Loan Number has not already been returned in row above it.


    Random Thoughts that I've been trying...

    Index entire Column A of Loan Detail tab, then use Match function to find array where each criteria is true.


    =INDEX('Loan Detail'!A:A,MATCH(1, (ISBLANK('Loan Detail'!B:B))*('Loan Detail'!C:C="FHLMC Cash")*(COUNTIF($A$1:A1, 'Loan Detail'!A:A)=0), 0))

    • Index entirety of Column A.
    • Look for return of "1" when all criteria are met.
    • ISBLANK to determine if Column B is blank.
    • C:C="FHLMC Cash" to determine if Column C has correct label.
    • COUNTIF to determine if formula has already returned loan number that previously met criteria and has been returned in row above it.

    Any help or guidance would be helpful.


    Thanks,

    -Kyle

    Hi,


    For cell E3, you can have following Array Formula

    Code
    =IFERROR(INDEX($A$1:$A$12,SMALL(IF($C$1:$C$12="Cash",IF($B$1:$B$12="",ROW($A$1:$A$12))),ROWS($A$3:A3))),"")

    Do not hesitate to come back and share your comments ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from Pecoflyer

    More recent versions of XL have the FILTER function

    Pecoflyer


    You are right ...


    But, for sure, you know the FILTER function was introduced with Excel 2021 ...


    And you have also noticed the OP is using Excel 2019

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Kyle,


    Hope you had a chance to review your test file ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login