Finding matches from different sheets and gathering them in one sheet

Hi there,
I have the following case. Example sample file attached.

I have 3 sheets - "Full list" , "Training1" and "Training2".

In the 1st sheet Full list I need to fill data for different employees, based on their training attendance.
Training1 and Training 2 sheets contain report exported data for attendees, who have attended the trainings already, however each export for each training is different = there's no consistency here, except the "E-mail" column which is always the same.

Now, is there a formula that I can use in the Full list sheet, Column L (L2:L5) that is checking for matches based on the emails (Column B2:B6) in the other sheet Training1 and if such match is found, to return a value for the respective Group (Column L , Sheet Training1) ?

Also for the other column M2:M5 on Sheet Full list I need a similar thing - once again to check for emails in sheet "Training2" and when a match is found to return result in Column M, Sheet Full list.

As a remark - on Column L and Column M in sheet Full list are showing the desired/expected result, just to illustrate what I want to achieve as end result.

no sample attached
Also which version of excel will this be used in ? as you have 365 and 2016 - a vast difference in functions available between those versions

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

HOW TO ATTACH YOUR SAMPLE WORKBOOK:, Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Just added the attachment. Excel 365 is the version

yes, you could use vstack and filter()
but
just a xlookup / index/match / IF will work , if i have understood

=INDEX(Training1!$L$2:$L$100,MATCH('Full list'!E2,Training1!$B$3:$B$100,0))
But some of the emails do not match up
and then
=IFERROR(INDEX(Training2!$B$2:$B$100,MATCH('Full list'!E2,Training2!$A$3:$A$100,0)),"")
will give the date - BUT
you could use an IF
=IF(ISERROR(INDEX(Training2!$B$2:$B$100,MATCH('Full list'!E2,Training2!$A$3:$A$100,0))),"","completed")

see column O and P

Dynamic array, since you have O365:

Training 1:
=BYROW(E2:E5,LAMBDA(x,IFERROR(FILTER(Training1!L2:L20,Training1!B2:B20=x),"")))

Training 2:
=BYROW(E2:E5,LAMBDA(x,IF(ISNUMBER(MATCH(x,Training2!A2:A20,0)),"Completed","")))

No formula dragging needed.

It doesn't do that for me... I see blanks instead... Please post a file showing zeros.