Help with lookup / v lookup big failure

[mda99das]

I have enclosed a sample workbook.

What I want is a look up function that looks up the reference number and returns all the data in the other columns as described.
I keep getting error messages.


I have tried vlookup and lookup but it doesn't help.
Sometimes I get the error cannot search on the left.
It sounds simple in theory but its driving me insane.
Thank you.