Index/Match not picking up with nth instances in list

Hey everyone,

I'm currently working on a rather large report to help my executive management team identify the number of "New" vs. "Current" customers who financed with our company during 2015 (sample chart shown below, and Excel attached). For the purpose of this sample, let's say I work for ABC Company.

Column "Analysis 1" is a helper column that I added to identify which customers are definitely current customers based on whether a date exists under "Inactive Date" that is +/- 15 days of the date under "Final Approval Date". If column "ABC Company Status" shows "active", then they are also a "current customer". Every other customer = "New Customer".

The formula that I'm currently using (and having trouble with) is under column "Current or New":
=IF([@[ABC Co. Status]]="Active", "Current Customer",IF(INDEX([Analysis 1],MATCH([@Grouping],[Grouping],0))<>"", INDEX([Analysis 1],MATCH([@Grouping],[Grouping],0)),"New Customer")).

• The issue I'm running into though is that this formula doesn't pick up on instances where the entry isn't the first instance in the grouping (even if it matches the criteria)... For example, Row 7 "Jake" should be a current customer based on the "Analysis 1" designation, as should Row 19 "Katie". I think that Small could be used.... but I'm not familiar enough with that function to know where to start with it (if it fits at all for that matter).

Row #	Grouping	ID #	Name	Final Approval Date	File Status	Liability	Liability Co. Name	Liability Acct #	ABC Co. Status	Inactive Date	Analysis 1	Current or New
1	1	123456	John	01-30-15	10	1st Mortgage	ABC Company	123	Active			Current Customer
2	2	987654	Jill	04-01-15	10	1st Mortgage	ABC Co.	456	Inactive	04-05-15	Current Customer	Current Customer
3	2	987654	Jill	04-01-15	10	1st Mortgage	Wells Fargo	789				Current Customer
4	2	987654	Jill	04-04-15	10	1st Mortgage	BMX Industrial	987				Current Customer
5	3	7894561	Jake	06-18-15	10	1st Mortgage	BNY Mellon	654				New Customer
6	3	7894561	Jake	06-18-15	10	1st Mortgage	Bank Of America	321				New Customer
7	3	7894561	Jake	06-18-15	10	1st Mortgage	ABC Company	135	Inactive	06-19-15	Current Customer	New Customer
8	3	7894561	Jake	06-18-15	10	1st Mortgage	Rochester Bank	679				New Customer
9	4	654321	John	09-07-15	10	1st Mortgage	BNY Mellon	976				New Customer
10	4	654321	John	09-07-15	10	1st Mortgage	Discover Card	523				New Customer
11	5	555555	Henry	10-12-15	10	1st Mortgage	Bank Of America	741				New Customer
12	5	555555	Henry	10-12-15	10	1st Mortgage	BMX Industrial	852				New Customer
13	5	555555	Henry	10-12-15	10	1st Mortgage	ABC Co.	963	Inactive	06-01-03	New Customer	New Customer
14	6	222222	Katie	11-02-15	10	1st Mortgage	Quicken Loans	111				New Customer
15	6	222222	Katie	11-02-15	10	1st Mortgage	Bank of New York	222				New Customer
16	6	222222	Katie	11-02-15	10	1st Mortgage	Nationstar	333				New Customer
17	6	222222	Katie	11-02-15	10	1st Mortgage	Lenders Choice	999				New Customer
18	6	222222	Katie	11-02-15	10	1st Mortgage	Rochester Bank	888				New Customer
19	6	222222	Katie	11-02-15	10	1st Mortgage	ABC Co.	777	Inactive	11-03-15	Current Customer	New Customer

Any suggestions on how to solve for this would be much appreciated. Thank you in advance!

Try this formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

Try this modification:

Please Login or Register  to view this content.

Let me know if you have any questions.

Hmm, I think that I have my information set up the same as the example in post #4 and I am getting a result of "New Customer" in L3:L5, which is what I think you want. Check the attached file and see if if the set up matches yours, perhaps my formula for 'Analysis 1' doesn't match.
Copy of New Customer Sample.xlsx
Let me know if you have any questions.