CODEWITHSUNDEEP
excelvlookup
Bigjo
Bigjo

Reputation: 633

How to use vlookup to find a string in a cell in excel

In excel I have a column A:A with a comment text (a lot of text). I want to make a column (col B) that put the category if a certain word is containing in the comment.

I have in my sheet2 a list of words (java,excel,...). If java is containing in the comment text, I want to put in column B "java". If the comment text contain "excel" i want to have "excel as category.

I thought to do that with vlookup, but I don't know how.

Upvotes: 0

Views: 2741

Answers (2)

PaloDravecky
PaloDravecky

Reputation: 281

The SEARCH function can take a range as input. So one solution would be this, with a helper column. Assuming you have 50 words in Sheet2, and that words in the comments are separated by space, and that you are OK to only return the first found word:

C1:

=SEARCH(Sheet2!$A$1:$A$50,A1)

B1:

=MID(A1,C1,FIND(" ",A1,C1)-C1)

The formula in C gives you the location of the first found word within the comment. You then use it to extract that word from your comment by finding the first space after the word's location. There should exist a more elegant solution though!

Edit: When applied to comment "123 excellent", my formula will give "excellent" although your word list only contains "excel". See @barry's answer for a better solution that ingeniously uses a property of the LOOKUP function (If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value).

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

Assuming that Sheet2!$A$1:$A$50 contains the search list (without blanks) as per Palo's solution then try this formula in B1 copied down

=LOOKUP(2^15,SEARCH(Sheet2!$A$1:$A$50,A1),Sheet2!$A$1:$A$50)

That returns the last match from Sheet2!$A$1:$A$50 so if you have any words in the list that are substrings of other words in the list, like Excel and Excellent then the longer word should be later in the list - so ideally Sheet2!$A$1:$A$50 would be sorted by length

Upvotes: 1

Related Questions