Word Matching & Ranked list

[psuLemon]

Everyone,

I am having some trouble even figuring out where to start on this project. Essentially, I am trying to do a search of a set of words and based on their rank within the category, I want to return the highest value. For example

Apple>Banana>Coconut



Column "A" would have one of those words in it. In B2 I want it to be able to search all of column "A" and return the highest value. So if column "A" had Banana and Coconut, I want it to return banana. Anyone able to help on this? I have been trying everything from a sumproduct to a isnumberserach without luck.

Thanks,


Lemon

 

[psuLemon]

=IF(ISNUMBER(SEARCH("Apple",A1&A2&A3&A4)),"Apple",IF(ISNUMBER(SEARCH("Banana",A1&A2&A3&A4)),"Banana","Coconut")))

So I got this to work. Is there an easier way to seach a range of rows? Because if I have 50 rows of information, I won't have enough room to write the formula

 

[jasonb75]

Not tested thoroughly, try something on the basis of

Excel Workbook
	A	B	C	D
2	apple		orange	banana
3	banana		banana
4	coconut
5	orange
6	pear
Sheet