Return multiple match value in two different workbook

ambz123

Board Regular
Hi All,
can i ask is there a way or any formula where I can return multiple match values from two different workbook?
The formula i used only works for same sheet but when i tried to return values from different workbook it cannot work. The data should be recording in my training record as shown in the image attached.

Master List:

Master List.xlsx
ABCHIJK
7Summary of Training Course - Year 2021
8
9Apps for Division:
10Candidate Name Dept Title Remark7Sangilikaruppan Selvam
16S. Karuppiah Production Coretrade Renewal reinforced concrete 7 deaths from workplace accidents in Feb 2021
19Hossain Ellius Production Coretrade Renewal reinforced concrete
28Sangilikaruppan KaruppiahProduction Procedure Reinforced Concrete Works Via Zoom
29Karuppiah RameshProduction Procedure Reinforced Concrete Works Via Zoom
30Hossain EliusProduction Procedure Reinforced Concrete Works Via Zoom
31Kolappa Pillai Mani KandanProduction Rebar Hazards
32Sangilikaruppan KaruppiahProduction Rebar Hazards
33Karuppiah RameshProduction Rebar Hazards
34Sangilikaruppan SelvamProduction Rebar Hazards
35Karuppiah SivalingamProduction Rebar Hazards
2021 Summary (All Dept.)
Cell Formulas
RangeFormula
I10I10=COUNTIF(\$A\$11:\$A\$73,\$J\$10)
J16,J28:J29,J18:J19J16=IF(ROWS(\$I\$11:I16)<=\$I\$10,INDEX(TableDiv[[Title ]],AGGREGATE(15,3,(TableDiv[[Candidate Name ]]=\$J\$10)/(TableDiv[[Candidate Name ]]=\$J\$10)*(ROW(TableDiv[[Candidate Name ]])-ROW(TableDiv[[#Headers],[Candidate Name ]])),ROWS(\$I\$11:I16))),"")

The master list should record in this training record based on candidate name.

Training Record:
Employee Training Record - 2021 (Production).xls
ABCDEFGHIJKLMNO
1IP-TRG-01-01 Rev 08
2EXCEL PRECAST PTE LTD
3EMPLOYEE TRAINING RECORD
42021
5Employee Name:Sangilikaruppan Selvam (W204)Date Joined:6/7/2004
6
7Department:ProductionDesignationWorker
8
9
10S/No.TRAINING /DATE / TRAINER /FeesREMARKS
11COURSE NAMEPERIODORGANISATION(IF ANY)
121
13
14
W204

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Replies
3
Views
228
Replies
3
Views
89
Replies
12
Views
336
Replies
11
Views
138
Replies
17
Views
544

1,130,037
Messages
5,639,670
Members
417,104
Latest member
Nelsini

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.

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

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