Match and Index problems

[mikemcbain]

To Peter_SSs & Co
I am still struggling to come to grips with Match and Index.
Now I am trying to to do the following....
Any help greatly appreciated.
Old Mike
I want to put formulae in column "L' of the DivsDue worksheet that will check both the Code in column "A" and the 42BuyDate in column "J" and find the corresponding Close Price in column "C" of the TOP20 worksheet.

Investment Tracker.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
311	HSN	Hansen Technologies Ltd	31	05-Oct-21	$0.30	50.00%	Final	20-Oct-21	2.01%	24-Aug-21
312	EHE	Estia Health Ltd	33	07-Oct-21	$0.02	100.00%	Final	29-Oct-21	1.54%	26-Aug-21
313	JLG	Johns LYNG Group Ltd	33	07-Oct-21	$0.39	100.00%	Final	22-Oct-21	0.76%	26-Aug-21
314	AWC	Alumina Ltd	33	07-Oct-21	$0.00	20.00%	Final	22-Oct-21	1.50%	26-Aug-21
315	REA	REA Group Ltd	34	08-Oct-21	$0.04	100.00%	Final	05-Nov-21	1.19%	27-Aug-21
316	AGG	Anglogold Ashanti Ltd	34	08-Oct-21	$0.12	100.00%	Final	29-Oct-21	1.74%	27-Aug-21
317	TLS	Telstra Corporation Ltd	37	11-Oct-21	$0.02	100.00%	Final	22-Oct-21	1.96%	30-Aug-21
318	AGL	AGL Energy Ltd	37	11-Oct-21	$0.03	100.00%	Interim	22-Oct-21	1.83%	30-Aug-21
319	DOW	Downer Edi Ltd	37	11-Oct-21	$0.02	0.00%	Final	25-Oct-21	1.83%	30-Aug-21
320	RKN	Reckon Ltd	37	11-Oct-21	$0.05	100.00%	Final	22-Oct-21	2.91%	30-Aug-21
321	AZJ	Aurizon Holdings Ltd	37	11-Oct-21	$0.08	100.00%	Final	22-Oct-21	4.00%	30-Aug-21
322	TWD	Tamawood Ltd	38	12-Oct-21	$0.12	100.00%	Final	27-Oct-21	0.57%	31-Aug-21
DivsDue

Investment Tracker.xlsm
	A	B	C	D	E	F	G	H	I	J
228	TCL	30-Aug-21	$14.06	-$0.02	-0.14%	$14.11	$14.14	$13.95	2,531,822	$35,564,789
229	TCL	31-Aug-21	$14.21	$0.15	1.07%	$14.08	$14.26	$14.02	6,119,418	$86,827,855
230	TLS	02-Aug-21	$3.77	-$0.01	-0.26%	$3.79	$3.81	$3.77	16,902,980	$64,078,996
231	TLS	03-Aug-21	$3.76	-$0.01	-0.27%	$3.76	$3.78	$3.74	19,459,802	$73,074,987
232	TLS	04-Aug-21	$3.77	$0.01	0.27%	$3.75	$3.78	$3.75	13,307,448	$50,122,260
233	TLS	05-Aug-21	$3.78	$0.01	0.27%	$3.77	$3.79	$3.77	16,244,477	$61,379,564
234	TLS	06-Aug-21	$3.80	$0.02	0.53%	$3.78	$3.80	$3.77	17,088,234	$64,688,158
235	TLS	09-Aug-21	$3.82	$0.02	0.53%	$3.80	$3.83	$3.79	18,028,157	$68,761,753
236	TLS	10-Aug-21	$3.85	$0.03	0.79%	$3.85	$3.88	$3.82	16,976,962	$65,352,048
237	TLS	11-Aug-21	$3.83	-$0.02	-0.52%	$3.87	$3.88	$3.81	24,517,837	$93,904,329
238	TLS	12-Aug-21	$3.97	$0.14	3.66%	$3.86	$4.00	$3.84	50,585,072	$198,922,141
239	TLS	13-Aug-21	$3.96	-$0.01	-0.25%	$4.00	$4.02	$3.94	29,018,036	$115,308,021
240	TLS	16-Aug-21	$3.94	-$0.02	-0.51%	$3.97	$4.00	$3.94	24,714,402	$97,761,584
241	TLS	17-Aug-21	$3.93	-$0.01	-0.25%	$3.94	$3.96	$3.91	20,421,635	$80,269,392
242	TLS	18-Aug-21	$3.97	$0.04	1.02%	$3.97	$4.00	$3.96	21,164,773	$84,184,623
243	TLS	19-Aug-21	$3.97	$0.00	0.00%	$3.92	$3.98	$3.92	20,156,695	$79,713,593
244	TLS	20-Aug-21	$4.02	$0.05	1.26%	$3.98	$4.02	$3.97	29,498,130	$116,981,127
245	TLS	23-Aug-21	$3.99	-$0.03	-0.75%	$4.02	$4.03	$3.96	16,846,352	$67,194,021
246	TLS	24-Aug-21	$3.93	-$0.06	-1.50%	$3.99	$3.99	$3.93	26,181,957	$103,349,456
247	TLS	25-Aug-21	$3.83	-$0.10	-2.54%	$3.84	$3.85	$3.80	34,949,539	$131,038,525
248	TLS	26-Aug-21	$3.86	$0.03	0.78%	$3.84	$3.88	$3.84	19,905,119	$76,795,770
249	TLS	27-Aug-21	$3.86	$0.00	0.00%	$3.85	$3.87	$3.81	17,326,661	$66,640,609
250	TLS	30-Aug-21	$3.83	-$0.03	-0.78%	$3.85	$3.86	$3.80	16,006,683	$61,315,479
251	TLS	31-Aug-21	$3.84	$0.01	0.26%	$3.84	$3.85	$3.82	17,939,912	$68,851,815
252	WBC	02-Aug-21	$25.03	$0.51	2.08%	$24.65	$25.10	$24.63	5,354,327	$133,589,192
253	WBC	03-Aug-21	$24.80	-$0.23	-0.92%	$24.95	$25.00	$24.70	4,759,885	$118,119,779
254	WBC	04-Aug-21	$24.84	$0.04	0.16%	$24.90	$24.94	$24.72	4,835,657	$120,063,854
TOP20

 

[maabadi]

Try formula at Column L. Because at your uploaded data only exist TLS, you only see it result:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	HSN	Hansen Technologies Ltd		10/5/2021	$ 0.30	50%	Final	10/20/2021	2.01%	8/24/2021
2	EHE	Estia Health Ltd		10/7/2021	$ 0.02	100%	Final	10/29/2021	1.54%	8/26/2021
3	JLG	Johns LYNG Group Ltd		10/7/2021	$ 0.39	100%	Final	10/22/2021	0.76%	8/26/2021
4	AWC	Alumina Ltd		10/7/2021	$ 0.00	20%	Final	10/22/2021	1.50%	8/26/2021
5	REA	REA Group Ltd		10/8/2021	$ 0.04	100%	Final	11/5/2021	1.19%	8/27/2021
6	AGG	Anglogold Ashanti Ltd		10/8/2021	$ 0.12	100%	Final	10/29/2021	1.74%	8/27/2021
7	TLS	Telstra Corporation Ltd		10/11/2021	$ 0.02	100%	Final	10/22/2021	1.96%	8/30/2021		3.83
8	AGL	AGL Energy Ltd		10/11/2021	$ 0.03	100%	Interim	10/22/2021	1.83%	8/30/2021
9	DOW	Downer Edi Ltd		10/11/2021	$ 0.02	0%	Final	10/25/2021	1.83%	8/30/2021
10	RKN	Reckon Ltd		10/11/2021	$ 0.05	100%	Final	10/22/2021	2.91%	8/30/2021
11	AZJ	Aurizon Holdings Ltd		10/11/2021	$ 0.08	100%	Final	10/22/2021	4.00%	8/30/2021
12	TWD	Tamawood Ltd		10/12/2021	$ 0.12	100%	Final	10/27/2021	0.57%	8/31/2021
13
14
15
Sheet1

Cell Formulas
Range	Formula
L1:L12	L1	=IFERROR(INDEX('Top20'!$C$1:$C$27,MATCH(1,INDEX(('Top20'!$A$1:$A$27=A1)*('Top20'!$B$1:$B$27=J1),0,1),0),1),"")

 

[AhoyNC]

You could also use the FILTER function in Excel 365.

Book2
	A	B	C	D	E	F	G	H	I	J	K
7	TLS	Telstra Corporation Ltd		11-Oct-2021	0.02	1	Final	22-Oct-2021	1.96%	30-Aug-2021	3.83
8	AGL	AGL Energy Ltd		11-Oct-2021	0.03	1	Interim	22-Oct-2021	1.83%	30-Aug-2021
9	DOW	Downer Edi Ltd		11-Oct-2021	0.0225	0	Final	25-Oct-2021	1.83%	30-Aug-2021
10	RKN	Reckon Ltd		11-Oct-2021	0.05	1	Final	22-Oct-2021	2.91%	30-Aug-2021
11	AZJ	Aurizon Holdings Ltd		11-Oct-2021	0.08	1	Final	22-Oct-2021	4.00%	30-Aug-2021
12	TWD	Tamawood Ltd		12-Oct-2021	0.12	1	Final	27-Oct-2021	0.57%	31-Aug-2021
DivsDue

Cell Formulas
Range	Formula
K7:K12	K7	=FILTER(Sheet2!$C$1:$C$27,(Sheet2!$A$1:$A$27=A7)*(Sheet2!$B$1:$B$27=J7)," ")

Book2
	A	B	C	D	E	F	G	H	I	J
1	TCL	30-Aug-2021	14.06	-0.02	-0.0014	14.11	14.14	13.95	2531822	35564789
2	TCL	31-Aug-2021	14.21	0.15	0.0107	14.08	14.26	14.02	6119418	86827855
3	TLS	2-Aug-2021	3.77	-0.01	-0.0026	3.79	3.81	3.77	16902980	64078996
4	TLS	3-Aug-2021	3.76	-0.01	-0.0027	3.76	3.775	3.74	19459802	73074987
5	TLS	4-Aug-2021	3.77	0.01	0.0027	3.75	3.78	3.75	13307448	50122260
6	TLS	5-Aug-2021	3.78	0.01	0.0027	3.77	3.79	3.77	16244477	61379564
7	TLS	6-Aug-2021	3.8	0.02	0.0053	3.78	3.8	3.77	17088234	64688158
8	TLS	9-Aug-2021	3.82	0.02	0.0053	3.8	3.83	3.79	18028157	68761753
9	TLS	10-Aug-2021	3.85	0.03	0.0079	3.85	3.88	3.82	16976962	65352048
10	TLS	11-Aug-2021	3.83	-0.02	-0.0052	3.87	3.875	3.81	24517837	93904329
11	TLS	12-Aug-2021	3.97	0.14	0.0366	3.86	4	3.84	50585072	198922141
12	TLS	13-Aug-2021	3.96	-0.01	-0.0025	4	4.02	3.94	29018036	115308021
13	TLS	16-Aug-2021	3.94	-0.02	-0.0051	3.97	4	3.94	24714402	97761584
14	TLS	17-Aug-2021	3.93	-0.01	-0.0025	3.94	3.955	3.91	20421635	80269392
15	TLS	18-Aug-2021	3.97	0.04	0.0102	3.97	4	3.96	21164773	84184623
16	TLS	19-Aug-2021	3.97	0	0	3.92	3.98	3.92	20156695	79713593
17	TLS	20-Aug-2021	4.02	0.05	0.0126	3.98	4.02	3.97	29498130	116981127
18	TLS	23-Aug-2021	3.99	-0.03	-0.0075	4.02	4.025	3.96	16846352	67194021
19	TLS	24-Aug-2021	3.93	-0.06	-0.015	3.99	3.99	3.93	26181957	103349456
20	TLS	25-Aug-2021	3.83	-0.1	-0.0254	3.84	3.85	3.8	34949539	131038525
21	TLS	26-Aug-2021	3.86	0.03	0.0078	3.84	3.88	3.835	19905119	76795770
22	TLS	27-Aug-2021	3.86	0	0	3.85	3.87	3.81	17326661	66640609
23	TLS	30-Aug-2021	3.83	-0.03	-0.0078	3.85	3.86	3.8	16006683	61315479
24	TLS	31-Aug-2021	3.84	0.01	0.0026	3.84	3.85	3.82	17939912	68851815
25	WBC	2-Aug-2021	25.03	0.51	0.0208	24.65	25.1	24.63	5354327	133589192
26	WBC	3-Aug-2021	24.8	-0.23	-0.0092	24.95	24.995	24.7	4759885	118119779
27	WBC	4-Aug-2021	24.84	0.04	0.0016	24.9	24.94	24.72	4835657	120063854
Sheet2

 

[mikemcbain]

AhoyNC & maabadi​

Both solutions worked perfectly and your marvellous skills are greatly envied down here in COVID free Tasmania.
I am going to use Ahoy's because the NC reminded me of a wonderful 6 week Rotary GSE Tour I led to North Carolina way back in 1979.

Thank you both.

Old Mike.

 

[maabadi]

You're Welcome & Thanks for Feedback.