if(isnumber(search - how to not include matching phrases

make manualy a table for all options so you can use VLookup to determine the categorie.

Hi.

What you're asking is quite straightforward, though it would be helpful first if you could attach a workbook with an example plus your expected results.

Regards

Based on your example, I don't see the problem since 'grapes' and 'apples' don't appear in the other items.

Originally Posted by jceg316

I can't put the exact data I'm working with as it's sensitive information

Of course, but can you not upload a small, mocked-up sheet which illustrates the same concept, just as in your fruit example here?

Regards

Thanks.

The fact that some of your categories are in the plural, and some singular, e.g. "Apples", but "Beer", complicates matters.

Is this not something which can be standardised?

Edit: this would ideally also need to be the case with the items in the Products list.

Regards

Ok. Can you at least guarantee that the only form of plural nouns being used will be that which involve the addition of an "s" to the singular?

Regards

This is not 100% rigorous, though try this in B2 of the Products sheet:

=LOOKUP(1,0/MMULT(N(ISNUMBER(SEARCH(" "&LEFT(Categories!$A$1:$A$7,LEN(Categories!$A$1:$A$7)-{0,1})&" "," "&Products!A2&" "))),{1;1}),Categories!$A$1:$A$7)

Copy down as required.

The reason I say that it's not foolproof is that it performs a search for each of the strings in the Categories tab twice: once for the word as it stands, and once for that word minus it's final letter.

This is an attempt to resolve the issue I mentioned. However, there is a very small chance that it could lead to incorrect results. For example, if both "Pear" and "Pea" were contained within the Categories list, then an entry in the Products list of "Green Pea" could well return "Pear".

Apologies - but I do not know of a simple way to resolve this potential issue.

Regards

And I assume that you amended the ranges to meet your actual set-up accordingly?

And that you've also verified that there is actually one such Category which contains at least one of the words for the Product being queried?

Perhaps you can post the version of the formula as adapted by you for one cell? And then say why you think that #N/A is not the correct result, e.g. "Cell A2 contains "red apple" and cell A6 in my Categories list contains "Apples", so I would expect a result of "Apples" here"?

Regards