Excel IF Formula VALUE error

Question

I am trying to enter the following formula into Excel:

=IF(D4=$M$4:$M$12, 1, 0)

Basically, "D4" refers to text, and I am trying to say that: IF this cell matches any of these words ($M$4:$M$12) then put the value as 1. 

The formula appears gives 0's for the first few rows (but does not actually work if the values are changed), but then gives me a VALUE error for everything else can I cannot figure it out.

Any ideas why this may not be working?

Thanks

Answer 1

@Jamie

Works for me OR I really don't understand the problem - always possible (my separor is ; not , in formulas):

Answer 2

Hi there,

Yes it doesn't appear to have worked, I am getting 1s and 0s that do not match up to the specified range. It does not appear as though it has reversed though, the pattern doesn't quite make sense. 

I apologise if my explanation wasn't quite clear! I am trying to get a binary YES/NO for whether each individual is active at each time (I am studying animal behaviour). So anything that matches the "Active" category would give a "1" and anything that does not would give a "0".  

Thanks again in advance!

Answer 3

@Jamie,

It's not quite clear so the following might not work:

=IF(ISNA(MATCH(D4,$M$4:$M$13,0)),0,1)

I extended the range to include row 13 as it seems you missed it in your formula

Answer 4

Here is a picture of the spreadsheet as an idea of what I am trying to do.