How to find an item in a table to populate a select list

Ok this shouldn’t be so difficult but today I am just not figuring it out.

I have a table for “orders”. The first field is Order Number which is not useful for lookup. That table also contains Items which may contain multiple items that are a text field.

All I want to do is from my Vitamins Table be able to have a Select List column that populates with orders that contain those items.


1234 Vitamin C, Vitamin B bunch of other columns

Vitamins Orders bunch of other columns
Vitamin C
Vitamin B

So HERE I want this Orders field to allow me to CHOOSE from the orders in the Order table that have Vitamin C in them. So I can see when I last ordered something.

But everything I’ve tried fails.

If I just use a lookup it wants to give me back the order number which is useless to choose because I have no idea without going back to the orders nad finding it myself which order # contains which items.

I tried using Contains and In and various other things using a filter, etc. I cannot figure out how to make this work. I know there has to be a solution but today I’m just not figuring it out.

Could you share a sample doc and the output you’re hoping to see?