LOOKUP Data across a range of cells to meet a specific criteria.

Hi, looking for some help after staring at a Spreadsheet for the morning. I've decided to go back to basics and have had support from yourselves in the past so thought I would call upon it again.

I've attached an example with steps of what I'm trying to acheive with this Spreadsheet.

It's a combination of VLOOKUP's across different Worksheet's and in some places up to four IF Statements to determine the correct answer.

Also on the attached Spreadsheet is a completed table of what I am trying to achieve.

Many thanks,
Adam.