Vlookup Via Searching Text in Cell -Char limit?

Backstory:
I am attempting to find a solution that looks for specific model numbers in a cell with multiple model numbers in one cell. Then I wanted to do a sort of vlookup.

My thought was to do a
=vlookup("*"&"[text here]"&"*",[range],[column#/match],FALSE)


Issue:
It does seem to work but not consistently. I attempted to add a large number of characters into a reference cell and the formula no longer works. I am suspecting that there is a character limit to how far excel will search a cell.

I was wondering if there was an alternative around this or some other formula that I haven't thought of. The issue is how to recognize text in a table that has thousands of rows since it will be about 70% buried in text in a cell vs. being the only text in the cell. example CG501CG502CG503CG504...CG560 vs CG504.


Please refer to the document attached. Thank you everyone for your assistance.

Dear Sam,

After taking a look at your workbook, it appears that I found the issue. Unfortunately, it doesn't really solve the problem in all scenarios.

Please refer to my document. After running some tests, it appears that Excel limits looking at cells beyond 255 character count.

The process started when yours worked but mine did not. Using "=len([cell])", I found yours had fewer characters than mine. Sam's was 220 characters while mine had 263. Visually put side to side in unwrapped text mode, they looked the same. Quickly figured there were paragraph indentations, so I used:

"=SUBSTITUTE([Cell],"
","")

which lead to the same character count of 220. This furthered my hypothesis that this was a character limit issue. So, I ran another test. I attempted to put enter characters until the formula would not work. Logically, it would be between 220 and 263. I also shortened it by testing in multiples of 5. That is where I discovered the formula stopped working beyond 255. Adding one extra character [256] would break the formula (see tab SAMPLE V2).

After testing to see if excel only looks at the first 255 or just skips these cells, I have come to the conclusion, excel won't search any cell that is beyond 255 characters in length.

I was wondering if there was any other solution or if this is just a limitation of excel. Any alternative solution would be greatly appreciated.


Thanks.

I should have also mentioned that my organization limits developer mode functions, so the 255 character would be difficult to overcome.

I'm not sure what your expected result is... try:

=TRANSPOSE(INDEX(E:E,IF(ISNUMBER(SEARCH($J$10,Table145[FIELD])),ROW(Table145[FIELD]),""))) in K10.