In normalized structure, each type/weight pair would be a record. How does that not meet your requirement?
Your OP says you need to look up 'correct currency'. What do you mean by 'currency' if there is only 1 currency involved?
I cannot open your db with Access 2010, it is using some feature that requires a later version of Access.
So what exactly is saved into Category field - text values like "Motor Vehicle", "Motorcycle", etc that match field names in Category table?
If yes, then:
DLookup("[" & [Category] & "]", "Category", [Weight] & " BETWEEN [Weight Class 1] AND [Weight Class 2]")
That expression can be in query or textbox on form or report.
Advise table name should be Categories to reduce confusion with field name Category. It is common practice to use plural for table names.