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]

I've got the inputs to the formula the wrong way round, it should be the range first then the criteria.

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

 

[Ardin]

In this case

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

Just Enter

Yess it works for first one, however, for "C2", I want to do it from D2:O2, however, then it gives the #SPILL! error. Do you know how to do that?

 

[Ardin]

I've got the inputs to the formula the wrong way round, it should be the range first then the criteria.

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

Great, thank you so much, it works!!
Have a great day!

 

[Norie]

No problem.

Sorry about the mix up.