Vlookup nightmare!

You can't do a VLOOKUP if the value you're trying to return is to the LEFT of your lookup value. An INDEX/MATCH formula might work for this:

=IF(I3="","",IF(I3="CANCELLED","",INDEX('Job Sheet'!$A$3:$B$922,MATCH(A3,'Job Sheet'!$B$3:$B922,0),1)))

This formula must be entered as an array formula by using CTRL-SHIFT-ENTER. I haven't tested this, so let me know if you have any issues with it.

Hope this helps.

Thanks for the rapid responses.

rob.barnes01 your code works perfectly. Strange how the position makes a difference here if everything is technically accurate.

Head scratching over, thank you!