New Contributor

# Logic Test With Text

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")

5 Replies

# Betreff: Logic Test With Text

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)

# Re: Logic Test With Text

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

- Geir

# Re: Logic Test With Text

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

# Re: Logic Test With Text

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.

# Re: Logic Test With Text

As variant

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