Sort a table by key words in a column.

[hotrod]

Table, A1/H1 - A100/H100.
Column H rows have a lot of text and I want to sort the table by a key word ( blue, red and green) within the text of each row.
Is it possible?

Or what about a formula for column I that searches column H for the key words ( blue, red and green) and puts in the key word for each row.
Then I could sort the table by I column.

 

[jbeaucaire]

You could put this in I1 and then copy down:

=LOOKUP(H1, {"blue","green","red"}, {1,3,2})

Notice the arrays are alphabetic? Anyway, now you can sort by column I and all the 1s will be on top, etc...

 

[Gerald Higgins]

The second approach is certainly doable.

For example

=IF(ISERROR(FIND("blue",A1,1)),IF(ISERROR(FIND("red",A1,1)),
IF(ISERROR(FIND("green",A1,1)),"NONE","GREEN"),"RED"),"BLUE")

This may not deal with multiple entries exactly as you wish, for example where A1 contains "RED BLUE GREEN". If this is a possibility, please give more details on how you want to handle it.

 

[hotrod]

=LOOKUP(H1, {"blue","green","red"}, {1,3,2})

=IF(ISERROR(FIND("blue",A1,1)),IF(ISERROR(FIND("red",A1,1)), IF(ISERROR(FIND("green",A1,1)),"NONE","GREEN"),"RED"),"BLUE")

Both formulas only work when blue, green or red is in a cell.

Does not work if blue, green or red is in a sentenance and this is what I need.

 

[jbeaucaire]

...Does not work if blue, green or red is in a sentenance and this is what I need.

That's an important distinction you left out of your original question. Best be more complete next time, a lot time gets wasted with omissions like that.

Setup your text strings in a column, the values to return in the next column...like this example...it's an ARRAY-ENTERED formula so you have to press CTRL-SHIFT-ENTER to confirm it, not just ENTER.

Excel Workbook
	H	I	J	K	L	M	N
1	The dog is green		3			blue	1
2	That red bird.		2			red	2
3	The brown lizard		0			green	3
4	The sky is blue		1
Sheet2

 

[facethegod]

Try..

=LOOKUP(9.99999999999999E+307,FIND({"$$$","blue","red","green"},"$$$"&A1),{"None","blue","red","green"})

Hope this helps

 

[Gerald Higgins]

=IF(ISERROR(FIND("blue",A1,1)),IF(ISERROR(FIND("red",A1,1)), IF(ISERROR(FIND("green",A1,1)),"NONE","GREEN"),"RED"),"BLUE")

Both formulas only work when blue, green or red is in a cell.

Does not work if blue, green or red is in a sentenance and this is what I need.

Not correct.

My formula works if blue, green or red is in a sentence.
For example, if A1 contains
"The sky is blue today"
my formula returns BLUE

 

[hotrod]

Gerald, tried formula again and it does work. I must have had A1 when it should have been H1. Thanks for making me try again.

Facethegod, this formula also works, thank you.

jbeaucaire, sorry for waisting your time, thanks for your efforts.