Forum Discussion

AngoDono's avatar
AngoDono
Copper Contributor
Oct 19, 2020

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

 

 

 

 

excel

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 19, 2020

    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

    • AngoDono's avatar
      AngoDono
      Copper Contributor
      Oct 19, 2020

      Hi SergeiBaklan 

       

      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. 

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Oct 19, 2020

    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)

     

Resources