INDEX / MATCH / MAX / MIN formula required

[JenTissen]

I think my formula may require MAX INDEX and MATCHsomehow, but I’m stumped.

I need to look at the earliest date and the latest date against memberslisted below (dates in between should be ignored). Ineed to then compare the Total Score against the earliest and latest date andif the Total score against the latest date is greater than the Total scoreagainst the earliest date, return “Improving”. If the Total Score is less for the earliest date, return “Declining”. “No Change” for same Total Score.

• Data is in member, then in Date order, but if ithelps with formula can go in total score order.
  
• There are a varied number of entries for membersbut we only need to look at first and last dates.
  
  
• I understand formula will need to go in everycell in D and am happy for result to display in all calls.
  
• Maybe is needs formula’s in D and E to get tothe result – that will be fine too.

Any help is greatly appreciated.

​	A​	B​	C​	D​	E​	F​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​
19​

<tbody> [TD="bgcolor: transparent"] Member [/TD]
[TD="bgcolor: transparent"] Date [/TD]
[TD="bgcolor: transparent"]

Total Score​

[/TD]
[TD="bgcolor: transparent"] Formulated Result [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] John G [/TD]
[TD="bgcolor: transparent"]

10/02/2017​

[/TD]
[TD="bgcolor: transparent"]

46​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] John G [/TD]
[TD="bgcolor: transparent"]

23/05/2017​

[/TD]
[TD="bgcolor: transparent"]

49​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] John G [/TD]
[TD="bgcolor: transparent"]

24/05/2018​

[/TD]
[TD="bgcolor: transparent"]

44​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] John G [/TD]
[TD="bgcolor: transparent"]

10/08/2018​

[/TD]
[TD="bgcolor: transparent"]

55​

[/TD]
[TD="bgcolor: transparent"]

Improving​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Kathleen A [/TD]
[TD="bgcolor: transparent"]

5/10/2017​

[/TD]
[TD="bgcolor: transparent"]

52​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Kathleen A [/TD]
[TD="bgcolor: transparent"]

2/05/2017​

[/TD]
[TD="bgcolor: transparent"]

50​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Kathleen A [/TD]
[TD="bgcolor: transparent"]

20/02/2018​

[/TD]
[TD="bgcolor: transparent"]

51
​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Kathleen A [/TD]
[TD="bgcolor: transparent"]

16/07/2018​

[/TD]
[TD="bgcolor: transparent"]

48​

[/TD]
[TD="bgcolor: transparent"]

Declining​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Margaret Y [/TD]
[TD="bgcolor: transparent"]

16/11/2017​

[/TD]
[TD="bgcolor: transparent"]

49​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Margaret Y [/TD]
[TD="bgcolor: transparent"]

8/02/2018​

[/TD]
[TD="bgcolor: transparent"]

52​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Margaret Y [/TD]
[TD="bgcolor: transparent"]

19/07/2018​

[/TD]
[TD="bgcolor: transparent"]

56​

[/TD]
[TD="bgcolor: transparent"]

Improving​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Enice S [/TD]
[TD="bgcolor: transparent"]

7/02/2017​

[/TD]
[TD="bgcolor: transparent"]

52​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Enice S [/TD]
[TD="bgcolor: transparent"]

5/06/2017​

[/TD]
[TD="bgcolor: transparent"]

51​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Enice S [/TD]
[TD="bgcolor: transparent"]

28/08/2018​

[/TD]
[TD="bgcolor: transparent"]

51​

[/TD]
[TD="bgcolor: transparent"]

Declining​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Valerie S [/TD]
[TD="bgcolor: transparent"]

16/10/2017​

[/TD]
[TD="bgcolor: transparent"]

46​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Valerie S [/TD]
[TD="bgcolor: transparent"]

17/08/2018​

[/TD]
[TD="bgcolor: transparent"]

46​

[/TD]
[TD="bgcolor: transparent"]

No change​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Joan B [/TD]
[TD="bgcolor: transparent"]

15/11/2017​

[/TD]
[TD="bgcolor: transparent"]

52​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Joan B [/TD]
[TD="bgcolor: transparent"]

6/03/2018​

[/TD]
[TD="bgcolor: transparent"]

53​

[/TD]
[TD="bgcolor: transparent"]

Improving
​

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
</tbody>

 

[jtakw]

Hi,

If your table is sorted by Member and Date order as you described, this is one way.

I don't have the MAXIF function, so Array formula to be confirmed by CSE (Control, Shift, Enter), instructions below:

Book1
	A	B	C	D
1	Member	Date	Total Score	Formulated Result
2	John G	2/10/2017	46
3	John G	5/23/2017	49
4	John G	5/24/2018	44
5	John G	8/10/2018	55	Improving
6	Kathleen A	5/2/2017	52
7	Kathleen A	10/5/2017	50
8	Kathleen A	2/20/2018	51
9	Kathleen A	7/16/2018	48	Declining
10	Margaret Y	11/16/2017	49
11	Margaret Y	2/8/2018	52
12	Margaret Y	7/19/2018	56	Improving
13	Enice S	2/7/2017	52
14	Enice S	6/5/2017	51
15	Enice S	8/28/2018	51	Declining
16	Valerie S	10/16/2017	46
17	Valerie S	8/17/2018	46	No change
18	Joan B	11/15/2017	52
19	Joan B	3/6/2018	53	Improving
Sheet559

Cell Formulas
Range	Formula
D2	{=IF(A2=A3,"",IF(C2=MAX(IF(A$2:A$19=A2,C$2:C$19)),IF(COUNTIFS(A$2:A$19,A2,C$2:C$19,C2)=1,"Improving","No change"),"Declining"))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Formula copied down.

 

[sheetspread]

=IF(A2=A3,"",CHOOSE(2+SIGN(C2-VLOOKUP(A2,$A$2:$C$19,3,0)),"Declining","No change", "Improving"))

works too

 

[JenTissen]

Thank you so much - it works a treat. Obviously I never would've got there! Much appreciated.