Logic Test With Text

%3CLINGO-SUB%20id%3D%22lingo-sub-1794398%22%20slang%3D%22en-US%22%3ELogic%20Test%20With%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1794398%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20create%20a%20formula%20which%20will%20produce%20a%20%22true%22%20or%20%22false%22%20or%20in%20my%20case%20%22native%22%20or%20%22exotic%22%20plant%20species%2C%20the%20problem%20is%20that%20excel%20will%20not%20recognise%20my%20text%20at%20the%20source%20when%20I%20select%20multiple%20cells%20eg.%20%22AA3%3AAA260%22%20which%20is%20a%20series%20of%20200%20plant%20names%2C%20if%20I%20have%20only%20one%20cell%20for%20example%20%22AA3%22%20the%20formula%20will%20work%2C%20however%20the%20whole%20string%20will%20not%20read%20the%20text%20in%20each%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20create%20a%20logic%20test%20for%20say%20%22H55%22%20with%20a%20range%20of%20cells%20%22AA3%3AAA260%22%20which%20will%20contain%20a%20plant%20name%2C%20to%20read%20if%20it%20is%20native%20or%20exotic%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20formula%3C%2FP%3E%3CP%3E%3DIF(H55%3DAA3%3AAA260%2C%22Native%22%2C%22Exotic%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20work%20around.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227613iA6869FFFB5D1F15F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Excel%20work%20around.png%22%20alt%3D%22Excel%20work%20around.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1794398%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1794641%22%20slang%3D%22de-DE%22%3ESubject%3A%20Logic%20Test%20With%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1794641%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F837266%22%20target%3D%22_blank%22%3E%40AngoDono%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20formula%2C%20as%20far%20as%20I%20could%20understand%20the%20translation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(EXACT(H55%2CAA3%3AAA260))%2C%22Native%22%2C%22Exotic%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1795388%22%20slang%3D%22en-US%22%3ERe%3A%20Logic%20Test%20With%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1795388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F837266%22%20target%3D%22_blank%22%3E%40AngoDono%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20work%20%3DIf(ISNUMEBER(search(%3CSPAN%3EH55%2C%3C%2FSPAN%3E%3CSPAN%3EAA3%3AAA260))%2C%22Native%22%2C%22Exotic%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E-%20Geir%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

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

 

Highlighted

@AngoDono 

 

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

 

- Geir

Highlighted

@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

Highlighted

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. 

Highlighted

@AngoDono 

As variant

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