Help with VLOOKUP formula using partial matches & reference table

Hello everyone,


I need a formula to return the appropriate value from a table based on a matching keyword. Please see screenshot:

Screenshot 2024-10-22 155844.png

Column "K" has a line description from which I need to pull a keyword to get a partial match. In this example, the keyword I'm looking for in cell K2 is "CMS Misc 3RD Party". On a separate sheet, I have a table (Table 13). This table has four different categories (in the "CATEGORY" column), and several keywords (in the "KEYWORD" column) which then correspond to a specific category.

I am not sure what formula I should be using, but I came up with a VLOOKUP that I thought would work, but it's only partially working, oddly enough.

The formula is: =VLOOKUP("*"&K2&"*",Table13,2,FALSE)

I am using wildcards in the lookup value because the data in column "K" is random/incomplete, so I need to find partial matches by using keywords.

For this example, the formula needs to give me an answer of "Damage", since that is the category that corresponds to the keyword "CMS Misc 3RD Party". How do I accomplish this? And is the VLOOKUP the correct formula to use?


Thank you,

Hi Mr. Shorty,


Thank you for the response. I wonder, is there a way I could do a =SEARCH formula that would tell me the correct number of characters, which I could then plug into the VLOOKUP? Like, in the formula, the Find_Text would be the list of keywords in column "A" of Table 13, and the Within_Text would be the item in cell "K2". If that were possible, I wonder if I could then take that number, and plug it into the VLOOKUP, maybe:

=VLOOKUP("result of the SEARCH formula"&K2&"*",Table13,2,FALSE)

Not sure of any of this makes sense, sorry (I am a beginner).


Thank you,

Ok, I've attached a second screenshot with more examples. Unfortunately, the "Line description" data is carried over from a different report that contains a great deal of manually entered information, by a large group of people, and so there is no consistent pattern.

Attachment 881579

Hello,


Ok, I have tried to upload an Excel file, please see attached.

Example for Excel Issue.xlsx

Hello JeteMc,


This worked really well, thank you very much; only 165 errors in a data set 87,000 entries large.

I believe expanding reference table 13 to include more keywords will fix many of them, but I did find one problem; I noticed that the formula will give an #N/A message when it can't reference a keyword in a line description (as it should), but sometimes it will assign a random category. For example: the keywords "transport" and "voided" are not in reference table 13, so in theory, the formula should give me an #N/A message for items with those words in the line description, correct? Currently it's categorizing them all as "Dispute" and "Per diem", respectively. I could, of course, add those words to the table, but that wouldn't fix the underlying issue. Any thoughts on what might be causing this break in the logic?

Once again: very grateful for your help; you have saved me a lot of time and taught me so much.


Kind regards,