lookup value in table from text string containing those values and redundant information

[Ian53]

Hi all, I have a 2000 row sheet of raw data with a column of descriptions, all of which will contain one matching text item that is defined in a constant table.
I need to look into that table to find which text item is in the raw data string and return that text (or preferably an associated code).

I've tried various combinations of COUNTIF, OR, MATCH, SEARCH but can't get it right.

Any assistance greatly appreciated.

[windknife]

Clean all expected results.

Please Login or Register  to view this content.

[Rick Rothstein]

I am not sure what version Excel2211 is (the newer version supporting dynamic arrays are 365, 2021, 2024), but if your Excel is one of those, then this formula will also work...

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

Welcome to the forum.

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.