Multiple IF contains Lookup

[DT112]

Hi!

I am trying to do the following. I have one column which contains different search criteria and another column where I want to search each word against, if it finds a partial match, return the word? How can I do this without a massive nested if statement? Essentially I need it to do a multi search and return one match.

E.G.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Fruit
[/TD]
[TD]Search
[/TD]
[TD]Value (DESIRED RETURN VALUE)
[/TD]
[/TR]
[TR]
[TD]Apples Tree
[/TD]
[TD]mango
[/TD]
[TD]apple
[/TD]
[/TR]
[TR]
[TD]Mangos Juice
[/TD]
[TD]orange
[/TD]
[TD]mango
[/TD]
[/TR]
[TR]
[TD]Watermelons Seed
[/TD]
[TD]melon
[/TD]
[TD]melon
[/TD]
[/TR]
[TR]
[TD]Oranges Tree
[/TD]
[TD]apple
[/TD]
[TD]orange
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Are you saying that if mango is found under fruit, return apple, etc? If so, let A2:A5 house the Fruit strings.

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&SearchItems," "&$A2),ValueItems)

 

[DT112]

Are you saying that if mango is found under fruit, return apple, etc? If so, let A2:A5 house the Fruit strings.

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&SearchItems," "&$A2),ValueItems)

Hi,

No what I want is for each element in the fruit list, I want it to check through the entire of the search list (they will be in random order) if it finds a match in the fruit list (i.e apple matches, apple tree) return the value apple.

Essentially I want to have a column which tags the fruits by a pre-defined list of options (i.e. the search list)

 

[Aladin Akyurek]

Hi,

No what I want is for each element in the fruit list, I want it to check through the entire of the search list (they will be in random order) if it finds a match in the fruit list (i.e apple matches, apple tree) return the value apple.

Essentially I want to have a column which tags the fruits by a pre-defined list of options (i.e. the search list)

In that case:

=LOOKUP(9.99999999999999E+307,SEARCH(SearchItems,$A2),SearchItems)​