# Find words (not exact value) in a cell and populate a cell in a differnt column

1. ## Find words (not exact value) in a cell and populate a cell in a differnt column

I have these 2 columns
 house in washington House purchases house in detroit House purchases purchased car blue car purchased in delaware Car purchases iowa house in House purchases

Every time that Excel finds in column A the words "house in", I want to populate column B with the words "House in city" (which refers to the category). Similarly, if Excel finds in column A the words "car purchased "or "purchased car", I want to populate column B with the category "Car purchases". I tried using IF statement, but it's not working. The thing is that I am not looking for an exact value. If the words "house in" are in the cell, it doesn't matter if there is a 3rd word describing the city or the state, it should be classified in the ''House purchases'' category. I need to finish this by tomorrow. Any quick insights you can provide would be very much appreciated. Thank you again.

2. ## Re: Find words (not exact value) in a cell and populate a cell in a differnt column

Hi,

=LOOKUP(1,0/SEARCH({"car purchased","house in","purchased car"},A1),{"Car","House","Car"}&" purchases")

P.S. You don't say what the return should be if none of those phrases are found within the string, so I didn't add anything to that effect.

Regards

3. ## Re: Find words (not exact value) in a cell and populate a cell in a differnt column

XOR LX Thank you so much. Yes, you're right. How do I add the return when none of the phrases are found? In addition, can you please share a resource so I can understand how you came to this solution? With continued thanks.

4. ## Re: Find words (not exact value) in a cell and populate a cell in a differnt column

What do you want the formula to return in such cases? The original cell value?

I'll post an explanation after updating the formula as you require.

Cheers

5. ## Re: Find words (not exact value) in a cell and populate a cell in a differnt column

I would like the formula to return the category "Other". But I wouldn't also mind learning how to return the original cell value. Thanks.

6. ## Re: Find words (not exact value) in a cell and populate a cell in a differnt column

Ok, try:

=LOOKUP(1,0/SEARCH({"","car purchased","house in","purchased car"},A1),{"Other","Car","House","Car"}&" purchases")

Cheers

There are currently 1 users browsing this thread. (0 members and 1 guests)