Request for help - If word exists in one column, update another column

[Kyote]

Howdy all

I was hoping someone would be able to help me. With help from this board, I was able to create a formula that displayed one of 3 conditions based on comparing columns. I am looking to do something similar finding text in a single column and updating a different column based on what it finds.

I tried adapting the formula, but it looks far too complex for me and I am thinking it's more appropriate in a macro. So what I am looking to do is to look at Column A's cell contents for many possible words and update the corresponding Column D cells.

So it looks for "apple", "orange", or "pear" and if found it updates with "yummy apples", "lots of oranges", "we gots pears", or blank.

__A_________________D_____
apple__________yummy apples
orange_________lots of oranges
apples here_____yummy apples
I hate pears_____we gots pears
watermelon_____
kiwi____________
candy apples____yummy apples


- If A1 contains "apple", it sets the contents of D1 to "yummy apples"
- If A2 contains "orange", it sets the contents of D2 to "lots of oranges"
- If A5 contains none of the words, it sets D5 blank.
- If A7 contains "candy apple", it sets the contents of D7 to "yummy apples"
- Etc


I had started with a simple formula (below), but I couldn't think of a good way to nest one into another into another without a big confusing mess. I thought rewriting as a macro, looping per line, would be easier. But beyond the basic conditional If Then, I don't know how to find or update in this way.

=IF(ISERROR(FIND("apple",A1, 1)), "", "yummy apples")

If anyone has a moment and a suggestion, I'd appreciate it!

-Kyote

 

[T. Valko]

Howdy all

I was hoping someone would be able to help me. With help from this board, I was able to create a formula that displayed one of 3 conditions based on comparing columns. I am looking to do something similar finding text in a single column and updating a different column based on what it finds.

I tried adapting the formula, but it looks far too complex for me and I am thinking it's more appropriate in a macro. So what I am looking to do is to look at Column A's cell contents for many possible words and update the corresponding Column D cells.

So it looks for "apple", "orange", or "pear" and if found it updates with "yummy apples", "lots of oranges", "we gots pears", or blank.

__A_________________D_____
apple__________yummy apples
orange_________lots of oranges
apples here_____yummy apples
I hate pears_____we gots pears
watermelon_____
kiwi____________
candy apples____yummy apples


- If A1 contains "apple", it sets the contents of D1 to "yummy apples"
- If A2 contains "orange", it sets the contents of D2 to "lots of oranges"
- If A5 contains none of the words, it sets D5 blank.
- If A7 contains "candy apple", it sets the contents of D7 to "yummy apples"
- Etc


I had started with a simple formula (below), but I couldn't think of a good way to nest one into another into another without a big confusing mess. I thought rewriting as a macro, looping per line, would be easier. But beyond the basic conditional If Then, I don't know how to find or update in this way.

=IF(ISERROR(FIND("apple",A1, 1)), "", "yummy apples")

If anyone has a moment and a suggestion, I'd appreciate it!

-Kyote

Try this...

Create this 2 column table:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 107px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Apple</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">yummy apples</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Orange</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">lots of oranges</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Pear</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">we gots pears</TD></TR></TBODY></TABLE>


A1 = some string like I hate pears

Enter this formula in B1:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(F1:F3,A1),G1:G3)))

Note that if an entry contains more than one keyword the formula will match the keyword that is furthest down in the table list of keywords.

 

[T. Valko]

Try this...

Create this 2 column table:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 107px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Apple</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">yummy apples</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Orange</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">lots of oranges</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Pear</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">we gots pears</TD></TR></TBODY></TABLE>


A1 = some string like I hate pears

Enter this formula in B1:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(F1:F3,A1),G1:G3)))

Note that if an entry contains more than one keyword the formula will match the keyword that is furthest down in the table list of keywords.

If you're using Excel 2007 or later then we can reduce the formula to:

=IFERROR(LOOKUP(1E100,SEARCH(F1:F3,A1),G1:G3),"")