Lookup , match, not sure !?
 

Hi,
I am trying to find a formula that will look up a text value in Column A
then compare it to the corresponding cell in columnB and copy the numbers in
column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5 for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil then
do nothing.

Hope i explained it ok.
Thanks.

=INDEX(C1:C10,MATCH(1,(A1:A10="Tony")*(B1:B10="Phi l")*ISNUMBER(C1:C10),0))
array entered (ctrl+shift+enter)

will give you the first number where col A and Col B are tony and phil and
col C is a number.

"Phil" wrote in message
. uk...
Hi,
I am trying to find a formula that will look up a text value in Column A
then compare it to the corresponding cell in columnB and copy the numbers
in column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5 for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil
then do nothing.

Hope i explained it ok.
Thanks.

=sumproduct(--(A:A="Tony"),--(B:B="Phil"),C:C)

will return a zero value if Tony & Phil aren't side by side in columns A &
B, but if they are side by side, will return the value in column C



"Phil" wrote:

Hi,
I am trying to find a formula that will look up a text value in Column A
then compare it to the corresponding cell in columnB and copy the numbers in
column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5 for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil then
do nothing.

Hope i explained it ok.
Thanks.

Excellent, thanks buddy!
"N Harkawat" <nharkawat@hotmail_dot_com wrote in message
...
=INDEX(C1:C10,MATCH(1,(A1:A10="Tony")*(B1:B10="Phi l")*ISNUMBER(C1:C10),0))
array entered (ctrl+shift+enter)

will give you the first number where col A and Col B are tony and phil
and col C is a number.

"Phil" wrote in message
. uk...
Hi,
I am trying to find a formula that will look up a text value in Column A
then compare it to the corresponding cell in columnB and copy the numbers
in column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5 for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil
then do nothing.

Hope i explained it ok.
Thanks.

SUMPRODUCT *doesn't* work with entire column references:

A:A, B:B, C:C
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Duke Carey" wrote in message
...
=sumproduct(--(A:A="Tony"),--(B:B="Phil"),C:C)

will return a zero value if Tony & Phil aren't side by side in columns A &
B, but if they are side by side, will return the value in column C



"Phil" wrote:

Hi,
I am trying to find a formula that will look up a text value in Column A
then compare it to the corresponding cell in columnB and copy the
numbers in
column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5
for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil
then
do nothing.

Hope i explained it ok.
Thanks.

What you gave me worked great but is there a way to do it where "Tony" and
"Phil" can be in either column. As it is now Tony in A matches with Phil in
B but can Tony be in A or B and still match with Phil in B or A ?
"N Harkawat" <nharkawat@hotmail_dot_com wrote in message
...
=INDEX(C1:C10,MATCH(1,(A1:A10="Tony")*(B1:B10="Phi l")*ISNUMBER(C1:C10),0))
array entered (ctrl+shift+enter)

will give you the first number where col A and Col B are tony and phil
and col C is a number.

"Phil" wrote in message
. uk...
Hi,
I am trying to find a formula that will look up a text value in Column A
then compare it to the corresponding cell in columnB and copy the numbers
in column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5 for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil
then do nothing.

Hope i explained it ok.
Thanks.

Try this:

=SUMPRODUCT((A1:A10={"Tony","Phil"})*(B1:B10={"Phi l","Tony"})*C1:C10)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Phil" wrote in message
. uk...
What you gave me worked great but is there a way to do it where "Tony" and
"Phil" can be in either column. As it is now Tony in A matches with Phil
in
B but can Tony be in A or B and still match with Phil in B or A ?
"N Harkawat" <nharkawat@hotmail_dot_com wrote in message
...

=INDEX(C1:C10,MATCH(1,(A1:A10="Tony")*(B1:B10="Phi l")*ISNUMBER(C1:C10),0))
array entered (ctrl+shift+enter)

will give you the first number where col A and Col B are tony and phil
and col C is a number.

"Phil" wrote in message
. uk...
Hi,
I am trying to find a formula that will look up a text value in Column
A
then compare it to the corresponding cell in columnB and copy the
numbers
in column c.
For instance...
Look down Column A for "Tony". If Tony is in ,say, A5 then look at B5
for
"Phil" and if B5 = Phil then copy number in C5. If B5 not equal to Phil
then do nothing.

Hope i explained it ok.
Thanks.