isnumber search from list and return match

[just_scott]

Hi, guys. Iv changed jobs and industries, from retail to compliance, since I last came here for help. Somehow Im still the only one that can use excel at my work and I know very little, although trying my best.

So im dealing with around 50,000 rows of data.
I have a list (USERS!B2:B11) of common text data that appears in my data
Im trying to search for the text in the data and then return the text if finds.

My data is not in cells, nor formatted in a way that could allow text to columns.

Im currently using the following:

=IF(ISNUMBER(SEARCH(USERS!$B$2,DATA!A1)),USERS!$B$2,
IF(ISNUMBER(SEARCH(USERS!$B$3,DATA!A1)),USERS!$B$3,
IF(ISNUMBER(SEARCH(USERS!$B$4,DATA!A1)),USERS!$B$4,
IF(ISNUMBER(SEARCH(USERS!$B$5,DATA!A1)),USERS!$B$5,
IF(ISNUMBER(SEARCH(USERS!$B$6,DATA!A1)),USERS!$B$6,
IF(ISNUMBER(SEARCH(USERS!$B$7,DATA!A1)),USERS!$B$7,
IF(ISNUMBER(SEARCH(USERS!$B$8,DATA!A1)),USERS!$B$8,
IF(ISNUMBER(SEARCH(USERS!$B$9,DATA!A1)),USERS!$B$9,
IF(ISNUMBER(SEARCH(USERS!$B$10,DATA!A1)),USERS!$B$10,
IF(ISNUMBER(SEARCH(USERS!$B$11,DATA!A1)),USERS!$B$11,"ERROR"))))))))))

My problem is that im going to need more “if” functions but ill soon run out of space in my formula bar. No to mention it is very slow.
Is there a way to isnumber(search(index(match,

Iv been playing with:

{=IF(ISNUMBER(SEARCH(USERS!$B$2:$B22,DATA!A10)),"YES")}

This will return a FALSE if the text does not exist in the cell or a YES if the text is there but still not sure how I would make it return the text if found.

Any help would be greatly appreciated.