Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in column B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

Hi,

Try this, It rerurns the 1 or 0 plus if c2 cant be found in column A 'Lookup
Not found'

=IF(ISNA(VLOOKUP(C2,A1:B6,2,FALSE)),"Lookup Not
found",(VLOOKUP(C2,A1:B6,2,FALSE)=1)*1)

Mike

"Langway" wrote:

Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in column B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

=IF(SUMPRODUCT(($A$2:$A$100=C2)*($B$2:$B$100=1))0 ,1,0)

Copy down as desired. Note that if you adjust the range sizes in SUMPRODUCT,
they must be equal in size, and you can only use entire column callouts (A:A)
if using 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Langway" wrote:

Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in column B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

This works on your sample data where there are no duplicates in column A and
the values in column B are either 1 or 0:

=SUMIF(A$2:A$7,C2,B$2:B$7)

If your sample data is "make believe" then try this generic formula:

=--(SUMPRODUCT(--(A$2:A$7=C2),--(B$2:B$7=1))0)

--
Biff
Microsoft Excel MVP


"Langway" wrote in message
...
Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in column
B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

THANKS!!!!! WORKS LIKE A CHARM!!!!

"T. Valko" wrote:

This works on your sample data where there are no duplicates in column A and
the values in column B are either 1 or 0:

=SUMIF(A$2:A$7,C2,B$2:B$7)

If your sample data is "make believe" then try this generic formula:

=--(SUMPRODUCT(--(A$2:A$7=C2),--(B$2:B$7=1))0)

--
Biff
Microsoft Excel MVP


"Langway" wrote in message
...
Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in column
B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Langway" wrote in message
...
THANKS!!!!! WORKS LIKE A CHARM!!!!

"T. Valko" wrote:

This works on your sample data where there are no duplicates in column A
and
the values in column B are either 1 or 0:

=SUMIF(A$2:A$7,C2,B$2:B$7)

If your sample data is "make believe" then try this generic formula:

=--(SUMPRODUCT(--(A$2:A$7=C2),--(B$2:B$7=1))0)

--
Biff
Microsoft Excel MVP


"Langway" wrote in message
...
Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in
column
B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

On Wed, 1 Apr 2009 11:30:04 -0700, Langway
wrote:

Ok, I think I have a tricky one.

A B C
101 1 100
102 0 101
104 0 102
105 1 103
109 1 104
110 0 105

I'm trying to put a formula in column D that will tell me...
If C2 is found anywhere in Column A AND on the same the same row in column B
there is a 1, then 1, if not 0.

Thanks in advance for your help!!!

Excel 2007+:

=IFERROR(INDEX($B$2:$B$7,MATCH(C2,$A$2:$A$7,0)),0)

Prior versions:

=IF(ISNA(MATCH(C2,$A$2:$A$7,0)),0,INDEX($B$2:$B$7, MATCH(C2,$A$2:$A$7,0)))

--ron