MATCH and/or INDEX formula requested

[mikemcbain]

G'day Magicians

I have an Excel 365 spreadsheet with a number of worksheets.

One worksheet is called DivsDue and in cell L250 I would like a MATCH and/or INDEX formula that will pick up the Date from column J and the Code from from column A and look at worksheet named Banks Etc and find the Price from column C that corresponds to the Code and Date from the first worksheet.In this case $12.05. The Data in worksheet Banks Etc is manipulateable, It can be sorted or columns rearranged if necessary.
The attached examples are shortened versions of my rather large file for simplicity sake.
Any guidance will be greatly appreciated.

Old Mike

Investment Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
229	COH	Cochlear Ltd	27	23-Sep-21	$1.40	0.00%	Final	18-Oct-21	0.62%	12-Aug-21
230	NWH	NRW Holdings Ltd	27	23-Sep-21	$0.05	100.00%	Final	13-Oct-21	2.72%	12-Aug-21
231	MYE	Mastermyne Group Ltd	27	23-Sep-21	$0.02	100.00%	Final	14-Oct-21	2.39%	12-Aug-21
232	VLS	Vita Life Sciences Ltd	27	23-Sep-21	$0.03	100.00%	Interim	08-Oct-21	2.28%	12-Aug-21
233	CAF	Centrepoint Alliance Ltd	27	23-Sep-21	$0.01	100.00%	Final	08-Oct-21	3.70%	12-Aug-21
234	LAU	Lindsay Australia Ltd	27	23-Sep-21	$0.01	0.00%	Final	08-Oct-21	1.35%	12-Aug-21
235	LYL	Lycopodium Ltd	27	23-Sep-21	$0.15	100.00%	Final	08-Oct-21	3.14%	12-Aug-21
236	APE	Eagers Automotive Ltd	25	23-Sep-21	$0.28	100.00%	Interim	15-Oct-21	1.75%	12-Aug-21
237	CAJ	Capitol Health Ltd	25	23-Sep-21	$0.01	100.00%	Final	22-Oct-21	1.32%	12-Aug-21
238	CUP	Countplus Ltd	25	23-Sep-21	$0.02	100.00%	Final	13-Oct-21	1.60%	12-Aug-21
239	GNE	Genesis Energy Ltd	25	23-Sep-21	$0.08	0.00%	Final	08-Oct-21	2.54%	12-Aug-21
240	BFL	BSP Financial Group Ltd	28	24-Sep-21	$0.13	0.00%	Interim	18-Oct-21	2.37%	13-Aug-21
241	IMD	IMDEX Ltd	31	27-Sep-21	$0.02	100.00%	Final	12-Oct-21	0.78%	16-Aug-21
242	PIC	Perpetual Equity Investment Company Ltd	32	28-Sep-21	$0.03	100.00%	Final	20-Oct-21	2.15%	17-Aug-21
243	EOL	Energy One Ltd	33	29-Sep-21	$0.06	0.00%	Final	18-Oct-21	0.93%	18-Aug-21
244	MEZ	Meridian Energy Ltd	33	29-Sep-21	$0.10	0.00%	Final	15-Oct-21	2.15%	18-Aug-21
245	BOL	Boom Logistics Ltd	31	29-Sep-21	$0.01	0.00%	Final	05-Nov-21	5.41%	18-Aug-21
246	CWP	Cedar Woods Properties Ltd	31	29-Sep-21	$0.14	100.00%	Final	29-Oct-21	2.02%	18-Aug-21
247	COS	Cosol Ltd	34	30-Sep-21	$0.01	100.00%	Final	29-Oct-21	1.45%	19-Aug-21
248	PBP	Probiotec Ltd	34	30-Sep-21	$0.03	100.00%	Final	08-Oct-21	1.32%	19-Aug-21
249	XRF	XRF Scientific Ltd	34	30-Sep-21	$0.02	100.00%	Final	15-Oct-21	3.81%	19-Aug-21
250	NCK	Nick Scali Ltd	32	01-Oct-21	$0.25	100.00%	Final	25-Oct-21	1.94%	20-Aug-21
251	BSA	BSA Ltd	38	04-Oct-21	$0.01	100.00%	Final	03-Nov-21	1.56%	23-Aug-21
252	CAM	Clime Capital Ltd	39	05-Oct-21	$0.02	100.00%	Interim	28-Oct-21	1.58%	24-Aug-21
253	NCC	Naos Emerging Opportunities Company Ltd	39	05-Oct-21	$0.04	100.00%	Final	25-Oct-21	3.30%	24-Aug-21
DivsDue

and worksheet Banks Etc is ....

Investment Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J
31	CBA	13-Aug-21	$104.03	-1.85	-1.75%	$106.00	$106.26	$103.63	3,748,467	$390,357,371
32	NCK	16-Aug-21	$12.30	-0.29	-2.30%	$12.63	$12.74	$12.26	365,361	$4,519,439
33	BEN	16-Aug-21	$10.00	-1.1	-9.91%	$10.85	$10.85	$10.00	13,399,496	$135,995,518
34	CBA	16-Aug-21	$102.54	-1.49	-1.43%	$103.55	$103.63	$102.42	3,913,742	$401,099,612
35	NCK	17-Aug-21	$12.09	-0.21	-1.71%	$12.64	$12.64	$12.01	311,609	$3,810,304
36	BEN	17-Aug-21	$9.86	-0.14	-1.40%	$10.19	$10.19	$9.77	7,114,380	$70,495,016
37	CBA	17-Aug-21	$99.00	-3.54	-3.45%	$100.68	$100.90	$99.00	3,990,475	$386,079,989
38	NCK	18-Aug-21	$12.03	-0.06	-0.50%	$12.01	$12.16	$11.88	281,781	$3,388,225
39	BEN	18-Aug-21	$9.95	0.09	0.91%	$9.87	$10.08	$9.83	2,670,729	$26,653,956
40	CBA	18-Aug-21	$99.77	0.77	0.78%	$99.49	$100.57	$99.28	3,068,552	$306,701,954
41	NCK	19-Aug-21	$12.14	0.11	0.91%	$12.10	$12.45	$11.98	370,233	$4,492,306
42	BEN	19-Aug-21	$9.99	0.04	0.40%	$9.97	$10.07	$9.92	2,368,162	$23,667,131
43	CBA	19-Aug-21	$99.22	-0.55	-0.55%	$99.12	$100.07	$99.10	2,319,533	$230,627,024
44	NCK	20-Aug-21	$12.05	-0.09	-0.74%	$12.14	$12.27	$11.96	376,362	$4,557,032
45	BEN	20-Aug-21	$9.80	-0.19	-1.90%	$10.01	$10.07	$9.80	3,399,157	$33,614,810
46	CBA	20-Aug-21	$99.27	0.05	0.05%	$100.00	$100.70	$99.25	2,620,966	$261,160,273
47	NCK	23-Aug-21	$11.97	-0.08	-0.66%	$12.35	$12.35	$11.74	391,364	$4,651,476
48	BEN	23-Aug-21	$9.81	0.01	0.10%	$9.87	$9.90	$9.81	1,786,839	$17,582,017
49	CBA	23-Aug-21	$100.17	0.9	0.91%	$99.89	$100.17	$99.62	1,326,874	$132,705,387
50	NCK	24-Aug-21	$11.99	0.02	0.17%	$12.15	$12.15	$11.88	284,546	$3,406,900
51	BEN	24-Aug-21	$9.90	0.09	0.92%	$9.84	$9.97	$9.81	1,476,349	$14,635,049
52	CBA	24-Aug-21	$99.91	-0.26	-0.26%	$100.51	$100.60	$99.75	1,766,246	$176,699,792
53	NCK	25-Aug-21	$12.29	0.3	2.50%	$12.09	$12.34	$12.04	298,424	$3,633,046
Banks Etc

 

[ravi2628]

hi mike,

Example - Copy.xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	COH	Cochlear Ltd	27	23-Sep-21	$ 1.40	0%	Final	18-Oct-21	1%	12-Aug-21	-
2	NWH	NRW Holdings Ltd	27	23-Sep-21	$ 0.05	100%	Final	13-Oct-21	3%	12-Aug-21	-
3	MYE	Mastermyne Group Ltd	27	23-Sep-21	$ 0.02	100%	Final	14-Oct-21	2%	12-Aug-21	-
4	VLS	Vita Life Sciences Ltd	27	23-Sep-21	$ 0.03	100%	Interim	08-Oct-21	2%	12-Aug-21	-
5	CAF	Centrepoint Alliance Ltd	27	23-Sep-21	$ 0.01	100%	Final	08-Oct-21	4%	12-Aug-21	-
6	LAU	Lindsay Australia Ltd	27	23-Sep-21	$ 0.01	0%	Final	08-Oct-21	1%	12-Aug-21	-
7	LYL	Lycopodium Ltd	27	23-Sep-21	$ 0.15	100%	Final	08-Oct-21	3%	12-Aug-21	-
8	APE	Eagers Automotive Ltd	25	23-Sep-21	$ 0.28	100%	Interim	15-Oct-21	2%	12-Aug-21	-
9	CAJ	Capitol Health Ltd	25	23-Sep-21	$ 0.01	100%	Final	22-Oct-21	1%	12-Aug-21	-
10	CUP	Countplus Ltd	25	23-Sep-21	$ 0.02	100%	Final	13-Oct-21	2%	12-Aug-21	-
11	GNE	Genesis Energy Ltd	25	23-Sep-21	$ 0.08	0%	Final	08-Oct-21	3%	12-Aug-21	-
12	BFL	BSP Financial Group Ltd	28	24-Sep-21	$ 0.13	0%	Interim	18-Oct-21	2%	13-Aug-21	-
13	IMD	IMDEX Ltd	31	27-Sep-21	$ 0.02	100%	Final	12-Oct-21	1%	16-Aug-21	-
14	PIC	Perpetual Equity Investment Company Ltd	32	28-Sep-21	$ 0.03	100%	Final	20-Oct-21	2%	17-Aug-21	-
15	EOL	Energy One Ltd	33	29-Sep-21	$ 0.06	0%	Final	18-Oct-21	1%	18-Aug-21	-
16	MEZ	Meridian Energy Ltd	33	29-Sep-21	$ 0.10	0%	Final	15-Oct-21	2%	18-Aug-21	-
17	BOL	Boom Logistics Ltd	31	29-Sep-21	$ 0.01	0%	Final	05-Nov-21	5%	18-Aug-21	-
18	CWP	Cedar Woods Properties Ltd	31	29-Sep-21	$ 0.14	100%	Final	29-Oct-21	2%	18-Aug-21	-
19	COS	Cosol Ltd	34	30-Sep-21	$ 0.01	100%	Final	29-Oct-21	1%	19-Aug-21	-
20	PBP	Probiotec Ltd	34	30-Sep-21	$ 0.03	100%	Final	08-Oct-21	1%	19-Aug-21	-
21	XRF	XRF Scientific Ltd	34	30-Sep-21	$ 0.02	100%	Final	15-Oct-21	4%	19-Aug-21	-
22	NCK	Nick Scali Ltd	32	01-Oct-21	$ 0.25	100%	Final	25-Oct-21	2%	20-Aug-21	$ 12.05
23	BSA	BSA Ltd	38	04-Oct-21	$ 0.01	100%	Final	03-Nov-21	2%	23-Aug-21	-
24	CAM	Clime Capital Ltd	39	05-Oct-21	$ 0.02	100%	Interim	28-Oct-21	2%	24-Aug-21	-
25	NCC	Naos Emerging Opportunities Company Ltd	39	05-Oct-21	$ 0.04	100%	Final	25-Oct-21	3%	24-Aug-21	-
DivsDue

Cell Formulas
Range	Formula
J1:J25	J1	=D1-42
K1:K25	K1	=IFERROR(INDEX('Banks Etc'!C:C,MATCH(1,('Banks Etc'!A:A=DivsDue!A1)*('Banks Etc'!B:B=DivsDue!J1),0),0),"-")
C22	C22	=D22-TODAY()

Please check the formula.

 

[Peter_SSs]

You may need to adjust the ranges to suit your Bank Etc data, but try this.

=FILTER('Banks Etc'!C$2:C$1000,('Banks Etc'!A$2:A$1000=A250)*('Banks Etc'!B$2:B$1000=J250),NA())

 

[mikemcbain]

Many thanks for your prompt response Peter.
However when I copy that formula to the other cells in Column L the "250" references don't change to represent the Rows. I will try and see if I can adjust it?

Mike.

 

[mikemcbain]

Many thanks for your prompt response Peter.
However when I copy that formula to the other cells in Column L the "250" references don't change to represent the Rows. I will try and see if I can adjust it?

Mike.

OK Peter, found my problem - your solution is working perfectly for me now.
Your excellent skills and this Forum are most valuable to old incompetents like me.

Mike.

 

[Peter_SSs]

You're welcome. Glad you got it working.