I’m not sure why you still have types mixed with numbers with a pipe character in-between. Anything regarding that pipe character should be gone.
Doesn’t each medication in the data sheet have a type? It’s hard to tell from your screenshots, but I would assume that each medication should have a type column in the data sheet to tell you what the type is for each medication. That type column is what you should be using to create a single relation to the type table.
You seem to have a Lookup column and I’m not really sure why you have that or why it has ‘type | sort#’ with the pipe character. Where is that even coming from. That pipe character is a big red flag to me. You type table looks good, but the data sheet needs to be simplified. All you should need is a single relation to the type table and a lookup column to get the associated sort number from that relation. That’s it.
Don’t overthink it. It shouldn’t be a complicated setup. Before you add a new column, make sure you understand why you are adding it and how it works.