Search of values doesn't work and gives errors

[Ardin]

Hi,

I'm currently working on a kind of CRM system in excel, and I just wanted to have a function to see if the name in "U2" equals one of the names in "V2:$FB$" then give an error or false or something. This works with several kinds of formula, however, when I copy it to the next cell, so then it becomes if the name in "V2" equals one of the names in "W2:$FB$", then it gives errors like "#spill!" and other ones as well with different formulas. When I searched it up, I saw because it was blocking the values, however, I am putting the formula in U1/V1 etc., so how can it be blocked?

Does someone maybe have a solution or a formula which I can use?

Thanks in advance!

Ardin

 

[Norie]

Ardin

Can you post the formula you are using?

 

[Ardin]

Hi Norie,
Thanks for your quick reply. I used several different formulas, but this is the last one I tried and got the "#SPILL!" error.
=IF(ISNUMBER(SEARCH(U2,V2:$FB$)),FALSE,"")

But maybe I need to use another type of formula, do you have any idea about that?

 

[mohadin]

Hi
You are missing the end range FB
it should be some thing like

=IF(ISNUMBER(SEARCH(U2,V2:$FB$60)),FALSE,"")

in addition tis an array formulay You have to CTRL+SHIFT+ENTER

 

[Norie]

SEARCH is used to find a substring in a string, so this formula:

=[URL='https://exceljet.net/excel-functions/excel-if-function']IF[/URL]([URL='https://exceljet.net/excel-functions/excel-isnumber-function']ISNUMBER[/URL]([URL='https://exceljet.net/excel-functions/excel-search-function']SEARCH[/URL](U2,V2:$FB$)),FALSE,"")

is going to look for the value in U2 in every cell in the specified range and return multiple results, in fact it will return as many results as there are cells in the second range in the formula. i.e. if the second range consists of 10 cells 10 results will be returned

If you want to find if the value in U2 is in any cell in range use COUNTIF.

=IF(COUNTIF(V2, V2:FB10)>0, "Found", "Not found"

 

[Ardin]

SEARCH is used to find a substring in a string, so this formula:

=[URL='https://exceljet.net/excel-functions/excel-if-function']IF[/URL]([URL='https://exceljet.net/excel-functions/excel-isnumber-function']ISNUMBER[/URL]([URL='https://exceljet.net/excel-functions/excel-search-function']SEARCH[/URL](U2,V2:$FB$)),FALSE,"")

is going to look for the value in U2 in every cell in the specified range and return multiple results, in fact it will return as many results as there are cells in the second range in the formula. i.e. if the second range consists of 10 cells 10 results will be returned

If you want to find if the value in U2 is in any cell in range use COUNTIF.

=IF(COUNTIF(V2, V2:FB10)>0, "Found", "Not found"

Thanks again for the quick reply.
I tried your code =IF(COUNTIF(V2,W2:$FB$2)>0, "Found", "Not found"), however I got the same #SPILL! error. I think I fixed the error by adding a "@" to =IF(COUNTIF(V2,@W2:$FC$2)>0, "Found", "Not found").
However, to test the code, I put (on purpose) one of the names in that range, but it didn't work, it stayed on "Not found", do you know how to fix that?

 

[Ardin]

Hi
You are missing the end range FB
it should be some thing like

=IF(ISNUMBER(SEARCH(U2,V2:$FB$60)),FALSE,"")

in addition tis an array formulay You have to CTRL+SHIFT+ENTER

Thanks, this worked as well, could it be similar to "@"? However, the code didn't work, any ideas?

 

[mohadin]

Well,
Would you show some examples with expected results

 

[Ardin]

Ofcourse!
In the picture below you can see the formula that I used, and the result of it. However, in "O2" I got the same name as in "B2", which is value which I am searching for. But it still gives "Not Found". So, why is it that it not says "found"?

 

[mohadin]

In this case

=IF(ISNUMBER(SEARCH(B2,C2:O2)),"Found","Not found")

Just Enter