INDEX, MATCH, MAX Formula

1. INDEX, MATCH, MAX Formula

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!

2. Re: INDEX, MATCH, MAX Formula

hi macki60. try this array formula in A2:
Formula:
`Please Login or Register  to view this content.`

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again. copy downwards & to column C too.

a slight change for F2:
Formula:
`Please Login or Register  to view this content.`

Hi macki60,

4. Re: INDEX, MATCH, MAX Formula

Thank you so much!

There are currently 1 users browsing this thread. (0 members and 1 guests)