Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |