Return multiple match value in two different workbook

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
18K.RameshProduction Coretrade Renewal reinforced concrete  
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,130,175
Messages
5,640,608
Members
417,156
Latest member
Ciupanezul21

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
Top