Match and Index problems

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
152
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
311HSNHansen Technologies Ltd3105-Oct-21$0.3050.00%Final20-Oct-212.01%24-Aug-21
312EHEEstia Health Ltd3307-Oct-21$0.02100.00%Final29-Oct-211.54%26-Aug-21
313JLGJohns LYNG Group Ltd3307-Oct-21$0.39100.00%Final22-Oct-210.76%26-Aug-21
314AWCAlumina Ltd3307-Oct-21$0.0020.00%Final22-Oct-211.50%26-Aug-21
315REAREA Group Ltd3408-Oct-21$0.04100.00%Final05-Nov-211.19%27-Aug-21
316AGGAnglogold Ashanti Ltd3408-Oct-21$0.12100.00%Final29-Oct-211.74%27-Aug-21
317TLSTelstra Corporation Ltd3711-Oct-21$0.02100.00%Final22-Oct-211.96%30-Aug-21
318AGLAGL Energy Ltd3711-Oct-21$0.03100.00%Interim22-Oct-211.83%30-Aug-21
319DOWDowner Edi Ltd3711-Oct-21$0.020.00%Final25-Oct-211.83%30-Aug-21
320RKNReckon Ltd3711-Oct-21$0.05100.00%Final22-Oct-212.91%30-Aug-21
321AZJAurizon Holdings Ltd3711-Oct-21$0.08100.00%Final22-Oct-214.00%30-Aug-21
322TWDTamawood Ltd3812-Oct-21$0.12100.00%Final27-Oct-210.57%31-Aug-21
DivsDue


Investment Tracker.xlsm
ABCDEFGHIJ
228TCL30-Aug-21$14.06-$0.02-0.14%$14.11$14.14$13.952,531,822$35,564,789
229TCL31-Aug-21$14.21$0.151.07%$14.08$14.26$14.026,119,418$86,827,855
230TLS02-Aug-21$3.77-$0.01-0.26%$3.79$3.81$3.7716,902,980$64,078,996
231TLS03-Aug-21$3.76-$0.01-0.27%$3.76$3.78$3.7419,459,802$73,074,987
232TLS04-Aug-21$3.77$0.010.27%$3.75$3.78$3.7513,307,448$50,122,260
233TLS05-Aug-21$3.78$0.010.27%$3.77$3.79$3.7716,244,477$61,379,564
234TLS06-Aug-21$3.80$0.020.53%$3.78$3.80$3.7717,088,234$64,688,158
235TLS09-Aug-21$3.82$0.020.53%$3.80$3.83$3.7918,028,157$68,761,753
236TLS10-Aug-21$3.85$0.030.79%$3.85$3.88$3.8216,976,962$65,352,048
237TLS11-Aug-21$3.83-$0.02-0.52%$3.87$3.88$3.8124,517,837$93,904,329
238TLS12-Aug-21$3.97$0.143.66%$3.86$4.00$3.8450,585,072$198,922,141
239TLS13-Aug-21$3.96-$0.01-0.25%$4.00$4.02$3.9429,018,036$115,308,021
240TLS16-Aug-21$3.94-$0.02-0.51%$3.97$4.00$3.9424,714,402$97,761,584
241TLS17-Aug-21$3.93-$0.01-0.25%$3.94$3.96$3.9120,421,635$80,269,392
242TLS18-Aug-21$3.97$0.041.02%$3.97$4.00$3.9621,164,773$84,184,623
243TLS19-Aug-21$3.97$0.000.00%$3.92$3.98$3.9220,156,695$79,713,593
244TLS20-Aug-21$4.02$0.051.26%$3.98$4.02$3.9729,498,130$116,981,127
245TLS23-Aug-21$3.99-$0.03-0.75%$4.02$4.03$3.9616,846,352$67,194,021
246TLS24-Aug-21$3.93-$0.06-1.50%$3.99$3.99$3.9326,181,957$103,349,456
247TLS25-Aug-21$3.83-$0.10-2.54%$3.84$3.85$3.8034,949,539$131,038,525
248TLS26-Aug-21$3.86$0.030.78%$3.84$3.88$3.8419,905,119$76,795,770
249TLS27-Aug-21$3.86$0.000.00%$3.85$3.87$3.8117,326,661$66,640,609
250TLS30-Aug-21$3.83-$0.03-0.78%$3.85$3.86$3.8016,006,683$61,315,479
251TLS31-Aug-21$3.84$0.010.26%$3.84$3.85$3.8217,939,912$68,851,815
252WBC02-Aug-21$25.03$0.512.08%$24.65$25.10$24.635,354,327$133,589,192
253WBC03-Aug-21$24.80-$0.23-0.92%$24.95$25.00$24.704,759,885$118,119,779
254WBC04-Aug-21$24.84$0.040.16%$24.90$24.94$24.724,835,657$120,063,854
TOP20
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try formula at Column L. Because at your uploaded data only exist TLS, you only see it result:
Book1
ABCDEFGHIJKLMN
1HSNHansen Technologies Ltd10/5/2021$ 0.3050%Final10/20/20212.01%8/24/2021 
2EHEEstia Health Ltd10/7/2021$ 0.02100%Final10/29/20211.54%8/26/2021 
3JLGJohns LYNG Group Ltd10/7/2021$ 0.39100%Final10/22/20210.76%8/26/2021 
4AWCAlumina Ltd10/7/2021$ 0.0020%Final10/22/20211.50%8/26/2021 
5REAREA Group Ltd10/8/2021$ 0.04100%Final11/5/20211.19%8/27/2021 
6AGGAnglogold Ashanti Ltd10/8/2021$ 0.12100%Final10/29/20211.74%8/27/2021 
7TLSTelstra Corporation Ltd10/11/2021$ 0.02100%Final10/22/20211.96%8/30/20213.83
8AGLAGL Energy Ltd10/11/2021$ 0.03100%Interim10/22/20211.83%8/30/2021 
9DOWDowner Edi Ltd10/11/2021$ 0.020%Final10/25/20211.83%8/30/2021 
10RKNReckon Ltd10/11/2021$ 0.05100%Final10/22/20212.91%8/30/2021 
11AZJAurizon Holdings Ltd10/11/2021$ 0.08100%Final10/22/20214.00%8/30/2021 
12TWDTamawood Ltd10/12/2021$ 0.12100%Final10/27/20210.57%8/31/2021 
13
14
15
Sheet1
Cell Formulas
RangeFormula
L1:L12L1=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),"")
 
Upvote 0
You could also use the FILTER function in Excel 365.
Book2
ABCDEFGHIJK
7TLSTelstra Corporation Ltd11-Oct-20210.021Final22-Oct-20211.96%30-Aug-20213.83
8AGLAGL Energy Ltd11-Oct-20210.031Interim22-Oct-20211.83%30-Aug-2021 
9DOWDowner Edi Ltd11-Oct-20210.02250Final25-Oct-20211.83%30-Aug-2021 
10RKNReckon Ltd11-Oct-20210.051Final22-Oct-20212.91%30-Aug-2021 
11AZJAurizon Holdings Ltd11-Oct-20210.081Final22-Oct-20214.00%30-Aug-2021 
12TWDTamawood Ltd12-Oct-20210.121Final27-Oct-20210.57%31-Aug-2021 
DivsDue
Cell Formulas
RangeFormula
K7:K12K7=FILTER(Sheet2!$C$1:$C$27,(Sheet2!$A$1:$A$27=A7)*(Sheet2!$B$1:$B$27=J7)," ")


Book2
ABCDEFGHIJ
1TCL30-Aug-202114.06-0.02-0.001414.1114.1413.95253182235564789
2TCL31-Aug-202114.210.150.010714.0814.2614.02611941886827855
3TLS2-Aug-20213.77-0.01-0.00263.793.813.771690298064078996
4TLS3-Aug-20213.76-0.01-0.00273.763.7753.741945980273074987
5TLS4-Aug-20213.770.010.00273.753.783.751330744850122260
6TLS5-Aug-20213.780.010.00273.773.793.771624447761379564
7TLS6-Aug-20213.80.020.00533.783.83.771708823464688158
8TLS9-Aug-20213.820.020.00533.83.833.791802815768761753
9TLS10-Aug-20213.850.030.00793.853.883.821697696265352048
10TLS11-Aug-20213.83-0.02-0.00523.873.8753.812451783793904329
11TLS12-Aug-20213.970.140.03663.8643.8450585072198922141
12TLS13-Aug-20213.96-0.01-0.002544.023.9429018036115308021
13TLS16-Aug-20213.94-0.02-0.00513.9743.942471440297761584
14TLS17-Aug-20213.93-0.01-0.00253.943.9553.912042163580269392
15TLS18-Aug-20213.970.040.01023.9743.962116477384184623
16TLS19-Aug-20213.97003.923.983.922015669579713593
17TLS20-Aug-20214.020.050.01263.984.023.9729498130116981127
18TLS23-Aug-20213.99-0.03-0.00754.024.0253.961684635267194021
19TLS24-Aug-20213.93-0.06-0.0153.993.993.9326181957103349456
20TLS25-Aug-20213.83-0.1-0.02543.843.853.834949539131038525
21TLS26-Aug-20213.860.030.00783.843.883.8351990511976795770
22TLS27-Aug-20213.86003.853.873.811732666166640609
23TLS30-Aug-20213.83-0.03-0.00783.853.863.81600668361315479
24TLS31-Aug-20213.840.010.00263.843.853.821793991268851815
25WBC2-Aug-202125.030.510.020824.6525.124.635354327133589192
26WBC3-Aug-202124.8-0.23-0.009224.9524.99524.74759885118119779
27WBC4-Aug-202124.840.040.001624.924.9424.724835657120063854
Sheet2
 
Upvote 0
Solution

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.
 
Upvote 0
You're Welcome & Thanks for Feedback.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top