Help to modify formula so that it only searches for whole string

Hi.

I have a workbook which contains a column of number values delimited with the pipeline character (column A). I want to return category names to column B based on a lookup of another sheet (Categories). My formula is looking for every character due to the SEARCH function, but I only want it to search for whole strings. I've tried lots of other formulas that I've seen online but none of them seem to work for what I want.

In summary:
'Product Import' sheet has pipeline delimited values in column A
'Categories' sheet has an array A2:B73 with column B containing the value I want returned to 'Product Import' Column B

Any help appreciated.

TIA :-)

Maybe
=TEXTJOIN("|",,IF(ISNUMBER(SEARCH("|"&Categories!$A$2:$A$73&"|","|"&A2&"|")),Categories!$B$2:$B$73,""))

CSE entered

Originally Posted by Glenn Kennedy

I am a bit confused. rather than showing us a non-working formula, please tell us (manually calculated) a few examples of what you EXPECT to see.

Sorry, it always seems clearer to the person who writes it!

So on the revised speadsheet, sheet 'Expected Outcome' shows in column B what I want as the outcome of the formula.

Originally Posted by Fluff13

Maybe
=TEXTJOIN("|",,IF(ISNUMBER(SEARCH("|"&Categories!$A$2:$A$73&"|","|"&A2&"|")),Categories!$B$2:$B$73,""))

CSE entered

Thanks Fluff13, that seems to work perfectly :-)

You're welcome & thanks for the feedback