Thank you both for responding. I couldn't get the =VLOOKUP(B2;Sheet2!$E$2:$S$5;15;FALSE) formula to work, there seemed to be an issue with the column reference. I tried using '19' for column 'S' and also after 15 didn't work but it still generated an #N/A error.

I was also unable to get the =Index formula to work, it kept returning a #Value Error and I couldn't understand why the formula was referencing column C as I was trying to match the values in column sheet1 B and column sheet2 E, then return a value in column sheet1 U, taken from column sheet2 S. I think however, I may not have been clear in my description

=INDEX(Sheet2!S$2:S$5,MAX(IF((Sheet1!B2=Sheet2!E$2:E$5)*(Sheet1!C2=Sheet2!C$2:C$5),ROW(C$2:C$5)-1,-1)))

I was however able to find a solution, using the below formula in sheet1 column U.

=INDEX(Sheet2!S:S, MATCH(Sheet1!B2, Sheet2!E:E, 0))