Array Formula

[Welsh Mark3]

I'm trying to use a formula to bring me the most recent date (Column N) from a range of account numbers (Column A) = A2. I have about 18000 rows with 7500 different account numbers.

This formula =MAX(IF(A:A=A2,N:N)) brings me back the highest value from the entire range but appears the if the statement is not working.

I think I need an array formula such as below. However when I enter control + shift + enter nothing happens.

{=MAX(IF(A:A=A2,N:N))}

Help is appreciated

 

[Welsh Mark3]

This
=MAX(IF(A2=A:A,N:N))
and this
=MAX(IF(A:A=A2,N:N))

are both exactly the same formula, so there is something funny going on if one returns a result & one doesn't.
That said you should never use entire columns in array functions, try limiting it like
=MAX(IF(A2:A20000=A2,N2:N20000))

Can you also post a sample of your data using the XL2BB add-in.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	CSUID		Expiry Value	End Date	Group			Last Contact Date	Last Contact Days	Users				Last Login Date	Company Reports Used - User	Company Reports Used - User (Last30Days)	Company Reports Used - Customer	Formula 1	Formula 2
2	35019225		3339	44196	PA			43938.65	124	3				08/17/2017	0		400	08/19/2020	08/19/2020
3	35019225		3339	44196	PA			43938.65	124	3				08/12/2020	400	67	400	08/19/2020	08/19/2020
4	73094812		33750	44377	PA			44054.38	8	5				08/19/2020	235	199	473	08/19/2020	08/19/2020
5	73094812		33750	44377	PA			44054.38	8	5				08/14/2020	21	14	473	08/19/2020	08/19/2020
6	73094812		33750	44377	PA			44054.38	8	5				08/17/2020	104	80	473	08/19/2020	08/19/2020
7	73094812		33750	44377	PA			44054.38	8	5				06/17/2020	0		473	08/19/2020	08/19/2020
8	73094812		33750	44377	PA			44054.38	8	5				06/17/2020	0		473	08/19/2020	08/19/2020
9	25058796		925	44367	CRT			44013.44	49	1				08/18/2020	46	33	46	08/19/2020	08/19/2020
10	70597188		1770	44206	PA			44056.48	6	1				08/18/2020	51	4	51	08/19/2020	08/19/2020
11	111577321		400	44206	MyCreditsafe			43830.37	232	1				03/25/2020	1		1	08/19/2020	08/19/2020
12	106998763		1600	44271	CRT			44053.73	9	1				07/16/2020	3		3	08/19/2020	08/19/2020
13	69830478		1200	44222	CRT			44047.52	15	1				08/13/2020	37	10	37	08/19/2020	08/19/2020
14	70501241		16700	44271	CRT Field			43959.49	103	11				08/18/2020	0		2	08/19/2020	08/19/2020
15	70501241		16700	44271	CRT Field			43959.49	103	11				05/01/2020	1		2	08/19/2020	08/19/2020
16	70501241		16700	44271	CRT Field			43959.49	103	11					0		2	08/19/2020	08/19/2020
17	70501241		16700	44271	CRT Field			43959.49	103	11				03/23/2020	0		2	08/19/2020	08/19/2020
18	70501241		16700	44271	CRT Field			43959.49	103	11				02/25/2020	0		2	08/19/2020	08/19/2020
19	70501241		16700	44271	CRT Field			43959.49	103	11				12/31/2019	0		2	08/19/2020	08/19/2020
20	70501241		16700	44271	CRT Field			43959.49	103	11				07/15/2020	0		2	08/19/2020	08/19/2020
21	70501241		16700	44271	CRT Field			43959.49	103	11				06/28/2018	0		2	08/19/2020	08/19/2020
22	70501241		16700	44271	CRT Field			43959.49	103	11				03/12/2020	0		2	08/19/2020	08/19/2020
23	70501241		16700	44271	CRT Field			43959.49	103	11				08/14/2020	1		2	08/19/2020	08/19/2020
24	70501241		16700	44271	CRT Field			43959.49	103	11				08/14/2020	1		2	08/19/2020	08/19/2020
25	112755706		600	44202	CRT			44029.51	33	3				02/13/2020	7	1	42	08/19/2020	08/19/2020
26	109688490		400	44347	CRT			44043.55	19	1				08/14/2020	7	4	7	08/19/2020	08/19/2020
27	70834114		1500	44343	CRT			44042.57	20	3				07/21/2020	4	5	4	08/19/2020	08/19/2020
28	70834114		1500	44343	CRT			44042.57	20	3				08/16/2019	0		4	08/19/2020	08/19/2020
29	70834114		1500	44343	CRT			44042.57	20	3				08/16/2019	0		4	08/19/2020	08/19/2020
30	110274473		3049	44316	PA			44061.48	1	2				07/21/2020	6	4	20	08/19/2020	08/19/2020
Sheet1

Cell Formulas
Range	Formula
R2:R30	R2	=MAX(IF(A2=$A$2:$A$150,$N$2:$N$150))
S2:S30	S2	=MAX(IF($A$2:$A$150=A2,$N$2:$N$150))

 

[Fluff]

You haven't confirmed either of those formula with Ctrl Shift Enter.
Select R2 then press F2 then press Ctrl Shift Enter & the formula should then get the {} around it, once you've done that drag the formula down.

 

[Welsh Mark3]

You haven't confirmed either of those formula with Ctrl Shift Enter.
Select R2 then press F2 then press Ctrl Shift Enter & the formula should then get the {} around it, once you've done that drag the formula down.

Perfect - Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.