What formula can I use to match names and codes on the Numbers app?

I think SG is on the right line in using TEXTBETWEEN() to extract the Farm Code name. Just bear in mind it is highly dependent on the consistent data input.

Specifically, A6 ("Ice Cream Mintz - Henderson") and A15 ("Chemdog 91 x GSC #1") will both fail since they don't follow the pattern, and A8 ("Skittles - HSPC - 3p") doesn't have a matching farm code.

If you're 100% confident that you will name the products according to the model 'strain - Farm Code <space><something> then you're OK, but any deviation from that will fail.

You're actually inconsistent yourself in the CODE column of your farms table, where some farm codes have "- 3P' and others have just " 3P" (without the hyphen), so you can see it's easy to make a mistake.

Personally, I'd add an additional column for the Farm Code separate from the product. You can then even use a pop-up menu of farms to make sure a valid farm is selected. This also makes it easier to filter your data (e.g. if you ever wanted to look at just the products from a certain farm, for example):

Here I've setup the Product name in column A, and column B is a pop-up menu of the various farms. Column C then concatenates the product name with the farm code to give the full product code, via:

=A2&" - " &XLOOKUP(B2,Farms::$A,Farms::$B," - UNKNOWN",0,1)

which pulls column $B from the Farms table that matches the value in column $A

This leads to greater consistency (you're not dependent on the user typing the right farm code, and invalid codes are impossible).

The Cultivar type is pulled from another XLOOKUP():

=XLOOKUP(B2,Farms::$A,Farms::$D,"UNKNOWN",0,1)

but now it feeds off the pop-up menu selection rather than having to parse out the full product code.

If you're confident on the <dash><space><Farm Name> model then a slight modification to SGIII's earlier post should suffice:

=XLOOKUP(REGEX.EXTRACT(A2,"- ([A-Za-z]+)",1,1),Farms::$C,Farms::$D,"UNKNOWN",0,1)

This runs a regular expression against cell A2 that looks for a string of alphabetic characters after a "- " combination and grabs everything up to the end of the next word. This is compared to column C of the Farms table, with the corresponding value returned from column D

The problem gets more difficult when you don't have a reasonably consistent data format.

But if your goal is to extract the value you have in your third (NAMING) column in the second table from column A (Product Name), then the TEXTBETWEEN function should handle most of your cases, something like this:

=TEXTBETWEEN(A2," - "," ")

More on TEXTBETWEEN here:

TEXTBETWEEN - Apple Support

SG

> So in the case of the =TEXTBETWEEN(A2," - "," ") there is no way to just ignore all the other information and just pull the name I want the formula to pull from based on that being the consistent data point?

All (or, at least, many) things are possible.

As both SGIII and I pointed out, the key is consistency. You have to have some rule that the sheet can follow.

Given the data provided, it seems that the hyphen is the key here. It's the most obvious delimiter. If you don't want to use that, you have to provide some other way for the formula to determine what to include and what to ignore.

You can't, for example, rely on words since some product names have one word (GG4, Gumbo, Skittlez), while others have two (Permanent Marker, Purple OG) or even three (Ice Cream Mintz), so that's out.

It might be possible to have the formula look for specific keywords in the product name, but there's always a chance of a miss. For example, searching for 'hill' could mismatch on a 'Hillbilly Blue" from Medicine Farm or Gold Flora's Chinchilla Paws (I have no idea if these are even remotely possible names, just using them as an example :) )

So the trick is to find a way that's consistent and simple enough to implement and doesn't get in the way of your workflow. The more inconsistent (or possibility for inconsistency/mistakes), the more complex the rule has to be. Everything is a tradeoff.