There are over 600+ customer ID and 400+ Unique ID’s that is based on the customer. (Sheet 1)
I am trying to analyze the expense incurred to the due’s collected.
1. Customer ID is the same but could change for some factors
2. Unique ID is based on the customers themselves so like 2545 when the person went from individual to entity it stayed the same and when the customer number changed (Unique ID 21) when they cancelled membership and resigned and customer ID Changed.
What I want to achive in SHEET 2
Cloumn A – 1. Reference the unique ID sheet 2 – B2 to sheet one and return the customer ID with Max Annual Pay
Column B- Will be filled in with the Unique ID (no formula needed)
Column C- To return the name from sheet 1 associate with the highest annual pay
Column F – To return the most recent date of membership contract based on the Unique ID
I have filled in what the valued should be in sheet 2. I was using the formula below also the formula in Clolumn G
=INDEX(Sheet1!F:F,MATCH(Sheet2!B2,Sheet1!B:B),0)
But it does not work unless the data is sorted the right way which is hard to do because there are a lot of records.
I would appreciate any help!
Bookmarks