IF statement - checking if text appears in a range, then applying 1, 2 or 3

[Tradewind2001]

Hi guys,

as my title somewhat poorly describes, I would like to test a cell (k14) to see if that word appears anywhere within a range of cells on another tab.

If it appears in the first third of the tab, then I would like "1" to be the result, if the word appears in the next third a "2" and the final third a "3".

If the cell does not appear at all in the range, I would like a "1" to be displayed.

Am I on the right track here?

=IF(COUNTIF(Zones!#REF!,K14),1,IF(COUNTIF(Zones!A112:A329,K14),2,IF(COUNTIF(Zones!A330:A939,K14),3)))

Many thanks in advance!!

[Jacc]

Pretty close. Try this:

Please Login or Register  to view this content.

[Tradewind2001]

Perfect - thanks so much!!!

[Ace_XL]

The >0 is redundant,

Hence, a tad shorter...

=IF(COUNTIF(Zones!$A$1:$A$111,K14),1,IF(COUNTIF(Zones!$A$112:$A$329,K14),2,IF(COUNTIF(Zones!$A$330:$A$939,K14),3,"Not found")))