Logic Test With Text

Copper Contributor

I am trying create a formula which will produce a "true" or "false" or in my case "native" or "exotic" plant species, the problem is that excel will not recognise my text at the source when I select multiple cells eg. "AA3:AA260" which is a series of 200 plant names, if I have only one cell for example "AA3" the formula will work, however the whole string will not read the text in each cell.

 

How can I create a logic test for say "H55" with a range of cells "AA3:AA260" which will contain a plant name, to read if it is native or exotic? 

 

Current formula

=IF(H55=AA3:AA260,"Native","Exotic")

 

Excel work around.png

 

 

5 Replies

@AngoDono 

 

Here is a formula, as far as I could understand the translation.

 

=IF(OR(EXACT(H55,AA3:AA260)),"Native","Exotic")

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

@AngoDono 

 

This should work =If(ISNUMEBER(search(H55,AA3:AA260)),"Native","Exotic")

 

- Geir

@AngoDono 

The logic is not clear, would you like to return Native/Exotic if

- all cells in the range contain value in H55, or;

- any cells, at least one, in the range contain value in H55, or;

- to show the flag against each cell in the range if it is Native/Exotic

Hi @Sergei Baklan 

 

I am trying to get "any cells, at least one, in the range contain value in H55", so when I type in a species name if it is listed in the range it is native if it is not in that range it is exotic. 

@AngoDono 

As variant

=IF(COUNTIF(AA3:AA260,"*"&H55&"*"),"Native","Exotic")