Compare two sheets

[RLS_SLR]

Hi - for my new job I am sent an 'absent' spreadsheet once a week with a list of students who are sick from school.  It comprises first name, last name, year, tutor group, etc.  

I have a separate 'main' sheet which shows every student in the school.  The columns include 'full name', and among other things 'FSM' which would show a Y or N to indicate if they receive free school meals.  

I need to send meal vouchers to students who are absent but receive free school meals.  I don't know how to compare the two sheets to pull up the answer.  There are too many just to scroll through. 

I can TEXTJOIN so that the 'absent' sheet has the student full names, i.e. the same format as names are presented in my 'main' sheet, and I know how to filter my own list to show only students who receive school meals but not then how to pull from that list those that are on the separate 'absent' sheet.  

The prior person apparently did this with a Pivot table but I don't understand how to do that.  

Thank you

[#2]

@RLS_SLR 

=IF(AND(B2="Y",NOT(ISNA(VLOOKUP(A2,absent!$E$2:$E$20,1,FALSE)))),"Absent and free voucher","no free voucher")

Perhaps this can be done with above formula if the data is arranged as in the attached file. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

@OliverScheurich thank you so much for your assistance and time.  I have been messing about with VLOOKUP - following little online guides for hours to achieve what you have shown on your example but keep failing. I don't know where exactly I am going wrong.  I'm going to continue try and follow your formula through to see what I am messing up.  Again - thank you so much.  

[#4]

@RLS_SLR 

=IF(NOT(ISNA(INDEX(absent!$E$2:$E$20,MATCH(1,(main!B2="Y")*(main!A2=absent!$E$2:$E$20),0)))),"Absent and free voucher","no free voucher")

You can try INDEX and MATCH as well. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.