INDEX MATCH ARRAY with MAX

[Dtex20]

Hi Guys,

I am having a problem understanding how to incorporate a MAX criteria into this formula:

{=INDEX(J:J,MATCH(G13&F13,$G:$G&$F:$F,0))}

In column A:A there are dates, i want the MAX date of which G13 & F13 Match the criteria. So in the example below, car would be G13, Red would be F13... and i want to return J:J but for the 03/01/2019 since that is the MAX.

01/01/2019 Car Red 10,000
02/01/2019 Car Red 10,000
03/01/2019 Car Red 10,001

Also is there a faster way to do the same process that isn't an array on an entire column?

Thanks,
Dtex

 

[Toadstool]

You can avoid the array formula by using AGGREGATE:

Book1
	A	B	C	D	E	F	G	H	I	J
1			Color	Vehicle
2			Red	Car	10001
3
10
11	1/1/2019					Red	Car			10,000
12	2/1/2019					Red	Car			10,000
13	3/1/2019					Red	Car			10,001
Sheet2

Cell Formulas
Range	Formula
E2	E2	=INDEX(J:J,MATCH(AGGREGATE(14,6,A:A/(F:F=$C$2)*(G:G=$D$2),1),A:A,0))

 

[steve the fish]

Toadstool that wont work as you are just matching on the date not all criteria.

 

[steve the fish]

This should work:

=LOOKUP(2,1/((A:A=MAXIFS(A:A,F:F,F1,G:G,G1))*(F:F=F1)*(G:G=G1)),J:J)

If you dont want to use full column references then change them to something appropriate.

 

[Toadstool]

Toadstool that wont work as you are just matching on the date not all criteria.

It works because the "/(F:F=$C$2)*(G:G=$D$2)" forces a #DIV/0! error unless that row also matches the values in C2 and D2.

 

[steve the fish]

It works because the "/(F:F=$C$2)*(G:G=$D$2)" forces a #DIV/0! error unless that row also matches the values in C2 and D2.

It doesnt work because the aggregate just produces a date. There obviously could be dates in the column that dont fulfil ALL the criteria. If it encounters them first then the index will produce that. If you dont believe me then just recreate the scenario and put the max date further up column A than the 'hit'. Do not fill in column F or G and place a random word in the same row in column J. Your formula will produce the random word.

 

[steve the fish]

For example place this in A10:J13. You formula will produce J10 not J13:

03/01/2019​
01/01/2019​					Red	Car			10,000​
02/01/2019​					Red	Car			10,000​
03/01/2019​					Red	Car			10,001​