Forum Discussion
Compare two sheets
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 separat...
=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.
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.
=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.