How To Check If Column has Certain Word

[HaveProblemsEveryday]

Hello. In excel, I have a cell that is supposed to check if the word in a box matches one of the words in the A column. However, when I try running it, it says #SPILL! The function is:

=IF(FIND(F6,A:A)>-1, "Hello", B1)

F6 is the input, A is the column where the words are being stored, B1 is a null, so that if it doesn't match, it shows a 0, which will be changed later.

However, when I run it, it displays as #SPILL!

Does anybody know what the problem might be?

[#2]

@HaveProblemsEveryday 

In your formula, Excel tries to find the text string from F6 in every cell in column A (i.e. 1048576 cells) and output the result (a number if found and #VALUE! if not found) for each of these in one single (dynamic) array. If you have that formula in any other place than on the first row, the #SPILL! error appears as there isn't enough space to display the entire results-array containing 1048576 cells. To demonstrate, I've limited the "find area" to A1:A10 in the example below (picture). As you can see, your formula will return "Hello" somewhere in the output array if the word "Car" is found. All other cells will contain #VALUE! . So, is doesn't produce a single cell output with the word "Hello" of a null.

To get a singe cell result, try it this way:

=IF(SUM(IFERROR(FIND(F6,A:A),0))>0,"Hello",B1)

This formula will find all occurrences of the word to find, replace errors with zeros, sum the entire array and if the result is greater than zero (meaning that some match was found), return "Hello" else B1.

But you should be aware that FIND looks for a text string within other text strings and it is case sensitive. So, if the word in F6 contains "Car" and column A contains the word "Card" you will get a positive ("Hello") result. If column A, on the other hand, contains the word "car", you will get your null result. If that's really what you want you're set to go. If you want to find text strings that are not case sensitive use SEARCH. If you want to find an exact match of a word use MATCH. Then it could look like this:

=IF(ISNUMBER(MATCH(F6,A:A,0)),"Hello",B1)

[#3 · Accepted solution]

@HaveProblemsEveryday 

Another variant is to use

=IF(SUM(COUNTIF(F6,A:A)),"Hello",B1)

but from performance point of view much better to use dynamic range as

=IF(SUM(COUNTIF(F6,A1:INDEX(A:A,COUNTA(A:A)))),"Hello",B1)

[#4]

@Sergei Baklan Thank you, this ended up working

[#5]

@HaveProblemsEveryday , you are welcome