When using a formula I get the Spill error

[viewotst]

Hello everybody,


I would like to use a function to find an exact word in a 1000 rows and 3 columns table. The main problem is that the word is contained in cells which are made of more than once word. For this reason, I believe I have to use a nested function (IF conditional function)
The idea is to use the function next to the table in each cell and if the cell contains the word “García” it says “True”. The word may be contain in cells in column C (third column). This column only contains text as it is a column with names and last names.


The problem is that is I use the following function (where C:C is the column containing the word “García” that I want to find) I get the “spill” error as I believe, 1000 rows is a lot for this function.


=IF(ISNUMBER(SEARCH("García";C:C));"True";"")


I cannot use Vlookup as “García” is contained among other words in the cells. For this reason, I think I have to use a nested function but not sure which one. What about something similar to this one? Do you know any other ways?


IF(ISTEXT(Vlookup(“García”;C:C;3;0));”True”;”False”)




What if the column contained numbers and text, could we still use ISTEXT?


Thank you

[Fluff13]

Hi & welcome to the board.
How about

Please Login or Register  to view this content.

[Limor_OP]

In order to avoid the spill, add SUM to your function.
The spill appears as your formula produces
=IF(SUM(--(ISNUMBER(SEARCH("García",C:C)))),"True","")

[viewotst]

Thank you.

It worked. I have a question, what is * for in Excel?

[viewotst]

Thank you too.

I think this one is a bit longer than the one above. What´s -- in excel for?

Thank you

[Fluff13]

I have a question, what is * for in Excel?

In this instance it's a wildcard meaning anything that contains "Garcia"

[viewotst]

I understand it now. So, you use the asterisk wild card to say to excel there are any characters before and after Garcia. Is the asterisk valid either for letters or numbers or other special characters?

What about the "--" that Belinda200 used above?

Thank you

[AliGW]

The double - forces the formula that follows it to return a numerical value.

[viewotst]

Hi there,

It worked using this formula but I did not need to add >0. Not sure why you added. Could you kindly explain it to me?

Thank you again

[Limor_OP]

Hi,
Please upload the file you are looking at.
see the yellow banner at the top of the page.

[Fluff13]

You said you wanted a True or False result, if you remove the >0 then it will just give you a count rather than True or False