How to include "Aggregate" to index match

[Patcheen]

HI all

Im currently using this formula

=INDEX(EMD!A2:A2000,MATCH(1,(U1=EMD!B2:B2000)*(V1=EMD!C2:C2000),0))

a2:a2000 are dates

B2:B2000 is numbers 100 to 1000 and c2:c2000 are also numbers 100 to 1000

im trying to include Aggregate so i can see when 2 numbers appeared together more recently.

the above formula works perfectly but only gives me the first time they appeared together

TIA

 

[AhoyNC]

Since you have Excel 365. Maybe use the FILTER function.

Book2
	A	B	C	D																T	U	V
1	Date	Number1	Number2																		100	200
2	10/25/2023	100	200																		Dates
3	10/26/2023	200	400																		10/25/2023
4	10/27/2023	100	300																		10/31/2023
5	10/28/2023	300	300																		11/2/2023
6	10/29/2023	400	200
7	10/30/2023	150	160
8	10/31/2023	100	200
9	11/1/2023	260	365
10	11/2/2023	100	200
11	11/3/2023	560	235
12	11/4/2023	100	1000
Sheet1

Cell Formulas
Range	Formula
U3:U5	U3	=FILTER(A2:A2000,(B2:B2000=$U$1)*(C2:C2000=$V$1))
Dynamic array formulas.

 

[Patcheen]

thank you for the quick reply

=FILTER(A2:A2000,(B2:B2000=$U$1)*(C2:C2000=$V$1)) where in here do i reference the sheet EMD

My original - =INDEX(EMD!A2:A2000,MATCH(1,(U1=EMD!B2:B2000)*(V1=EMD!C2:C2000),0))


Thank you again for your help

 

[AhoyNC]

=FILTER(EMD!A2:A2000,(EMD!B2:B2000=$U$1)*(EMD!C2:C2000=$V$1))

 

[Patcheen]

Thank you again for the reply

but when i enter formula it auto fills the column and greys it out.

 

[Patcheen]

Thank you again for the reply

but when i enter formula it auto fills the column and greys it out.

Here is an example

 

[AhoyNC]

Need a little more explanation. Your formula refers to columns A, B, C, which are not in your example. Is column AB only showing 22/05/2015 because that is the only date that has 100 & 200 in columns N &O? What about an example if there is more than one answer. What do the yellow highlighted cells in columns N thru T represent?

 

[AhoyNC]

Since you said your INDEX formula works, here is an example using AGGREGATE. You will probably need to change some of the cell references to match your data.

Book1
	T	U	V
1		100	200
2		Dates
3		10/25/2023
4		10/31/2023
5		11/2/2023
Sheet2

Cell Formulas
Range	Formula
U3:U5	U3	=IFERROR(INDEX(EMD!$A$2:$A$12,AGGREGATE(15,6,(ROW(EMD!$A$2:$A$12)-ROW(EMD!$A$2)+1)/((EMD!$B$2:$B$12=$U$1)*(EMD!$C$2:$C$12=$V$1)),ROWS($U$3:U3))),"")

EMD sheet

Book1
	A	B	C
1	Date	Number1	Number2
2	10/25/2023	100	200
3	10/26/2023	200	400
4	10/27/2023	100	300
5	10/28/2023	300	300
6	10/29/2023	400	200
7	10/30/2023	150	160
8	10/31/2023	100	200
9	11/1/2023	260	365
10	11/2/2023	100	200
11	11/3/2023	560	235
12	11/4/2023	100	1000
EMD

 

[Patcheen]

all i want is to my original formula is to add aggregate (i think that is what it's missing) to it so i get the latest date the numbers last appeared together. as per example

on the 22/05/15 100 and 200 appeared together

on the 05/06/15 100 138 and 150 appeared together

on the 19/06/2015 100 125 175 and 150 appeared together

on the 03/07/2015 100 105 101 180 and 150 appeared together

on the 17/07/2015 100 172 183 168 111 185 and 179 appeared together

im trying to get those dates over on the right ihand side in column AB

the yellow nums are the ones that are in reference to U1:AA1

in AB2 should be 22/05/2015 -- in AB3 should be 05/06/2015 -- in AB4 should be 19/07/2015 -- In AB5 Should be 03/07/2015 in AB6 should be 17/07/2015.


|A b and C are not in the example above as this example is not from the original book i put this together to try make it easier so it can be seen as a whole.


Again i thank you for your time and help appreciate it.

 

[Fluff]

How about

=TAKE(SORT(FILTER(EMD!A2:A2000,(U1=EMD!B2:B2000)*(V1=EMD!C2:C2000)),,-1),1)