Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I need
to analyze. I want to write an IF statement that will check if the value of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I will be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


....C_Guy

=IF(A8=A3:F7,1,0)

Try this instead...

=IF(OR(TYPE(MATCH(A8,A3:A7,0))=1,TYPE(MATCH(A8,B3: B7,0))=1,TYPE(MATCH(A8,C3:C7,0))=1,TYPE(MATCH(A8,D 3:D7,0))=1,TYPE(MATCH(A8,E3:E7,0))=1,TYPE(MATCH(A8 ,F3:F7,0))=1),1,0)

Rather ugly, but from my experience match does not work across a 2-D
range. Maybe there is a better way to search the range for the value
(either that or change the range into a single column or row... Hope
that puts you in the right direction at least...


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=390739

Read up on MATCH() and LOOKUP(), they may be what you are looking for.


Hope this helps.


C_Guy Wrote:
Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I
need
to analyze. I want to write an IF statement that will check if the
value of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I
will be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere
in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


....C_Guy


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390739

=COUNTIF(A3:F7,A8)

--
Greetings from New Zealand
Bill K
"C_Guy" wrote in message
...
Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I
need
to analyze. I want to write an IF statement that will check if the value
of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I will
be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


...C_Guy

Hi Bill;

I considered 'Countif' but in the case that the value appears twice the
function will return a '2'. I want the function to only return only
TRUE/FALSE or 0/1 regardless of how many times the value appears.

....C_Guy

"Bill Kuunders" wrote:


=COUNTIF(A3:F7,A8)

--
Greetings from New Zealand
Bill K
"C_Guy" wrote in message
...
Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I
need
to analyze. I want to write an IF statement that will check if the value
of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I will
be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


...C_Guy

not a problem

=IF(COUNTIF(A3:F7,A8)0,"true","false")

( :)
Greetings from New Zealand
Bill K
"C_Guy" wrote in message
...
Hi Bill;

I considered 'Countif' but in the case that the value appears twice the
function will return a '2'. I want the function to only return only
TRUE/FALSE or 0/1 regardless of how many times the value appears.

...C_Guy

"Bill Kuunders" wrote:


=COUNTIF(A3:F7,A8)

--
Greetings from New Zealand
Bill K
"C_Guy" wrote in message
...
Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I
need
to analyze. I want to write an IF statement that will check if the
value
of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I
will
be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


...C_Guy

One way:

=--(COUNTIF(A3:F7,A8)0)

In article ,
"C_Guy" wrote:

I considered 'Countif' but in the case that the value appears twice the
function will return a '2'. I want the function to only return only
TRUE/FALSE or 0/1 regardless of how many times the value appears.

"Bill Kuunders" wrote:


not a problem

=IF(COUNTIF(A3:F7,A8)0,"true","false")

( :)
Greetings from New Zealand
Bill K
"C_Guy" wrote in message
...
Hi Bill;

I considered 'Countif' but in the case that the value appears twice the
function will return a '2'. I want the function to only return only
TRUE/FALSE or 0/1 regardless of how many times the value appears.

...C_Guy

"Bill Kuunders" wrote:


=COUNTIF(A3:F7,A8)

--
Greetings from New Zealand
Bill K
"C_Guy" wrote in message
...
Hello everyone;

I need some help with the Excel 'if' function. I have a lot of data I
need
to analyze. I want to write an IF statement that will check if the
value
of
a particular cell is found within a range of other cells. I need to
reference the cell in the function (not the actual value) because I
will
be
copying the formula all over my worksheet.

For example, I want to see if the value in cell A8 is found anywhere in
cells A3:F7. The best I could come up with is:

=IF(A8=A3:F7,1,0)

The result was #VALUE!
The value in A8 does in fact appear within the range A3:F7.

What am I doing wrong?

Thanks!


...C_Guy