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.