=IF(J15<"10/10/05",VLOOKUP(E15,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J15>"11/10/05"<"19/10/05",VLOOKUP(E15,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J15>"19/10/05",VLOOKUP(E15,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE))))

which is producing the correct result when the search criteria is found, the problem is that I am getting #N/A where the result is not found. I have tried using if(isna) but to no avail. I got it to work with:-

=IF(ISERROR((IF(J175<"10/10/05",VLOOKUP(E175,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"11/10/05"<"19/10/05",VLOOKUP(E175,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"19/10/05",VLOOKUP(E175,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE)))))),"- NOT on ATLAS",(IF(J175<"10/10/05",VLOOKUP(E175,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"11/10/05"<"19/10/05",VLOOKUP(E175,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"19/10/05",VLOOKUP(E175,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE))))))

but this seems unmanageable.

All I want to do is select a value and it's date in Sheet 1, look for it's value within the date period in either sheets 2,3 or 4 and return a result.

There must be an easier way to do this.