If Cell Value Doesn't Exist

[Legacy 436357]

Hi,

I want to check the value that is in cell C and if it isn't found in C14:C62 then return "1".

Does anyone know the formula?

Thanks

 

[Joe4]

I want to check the value that is in cell C

Assuming that you mean cell C1. You didn't say what you want to return if the value is found, so this will return the empty string in that case:

=IF(COUNTIF(C14:C62,C1)>0,1,"")

 

[Legacy 436357]

I am sorry for not specifying the correct cell. It should be C2 and which I applied your formula thank you. However it always returns the "1" even if the cell value is in the range.

=IF(COUNTIF(C14:C62,C2)>0,1,"")

Do you happen to know why?

 

[Joe4]

Whoops, it should be:

=IF(COUNTIF(C14:C62,C2)[COLOR=#ff0000]=[/COLOR]0,1,"")

Sorry about that.

 

[Legacy 436357]

Basically the range is of customer codes. The cell C2 has a drop down list of customer codes. I select a code and if that code isn't in the range I want to apply the number "1" so I can give that customer a discount for being new.

 

[Joe4]

I just had the logic reversed. See my last reply for the update.

 

[Legacy 436357]

Thanks but the edited formula yields a blank no matter if the code exists or not.

 

[Joe4]

I am guessing that you either have a data issue, or have an error in how you applied it.

This can be shown to work properly pretty easily on a new blank sheet.

First, let's test one where there is a match, so nothing should be returned.
1. Place the formula in cell D1 on your sheet:

=IF(COUNTIF(C14:C62,C2)=0,1,"")

2. Place an "a" in cell C14
3. Place an "a" in cell C2

The formula should return nothing.

Now, let's test where there is no match. We can use the same sheet.
Simply change the value in cell C2 from "a" to "b".
You will now see "1" show up in cell D1.

So the formula is doing what you asked in both cases.

Note that something as simple as an extra space will cause things not to match, i.e. "a " will not match "a".
They must be EXACTLY the same.

 

[Legacy 436357]

Thanks there must have been a data issue. I deleted most of the table range and reapplied the formula and it works.

One of those unexplained mysteries I guess.