| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
TEST for items in 2 columns to MATCH?
hi, I am trying to find if record exists for values met in 2 different
columns, for use in a conditional format. info have is: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144: $AQ$1228=$AQ11,0))ROW($A$1144) not sure how got this to work in Cond. Format since (for a TRUE), when testing in an external cell get a VALUE error; but, want to compare to another column for OR( values, e.g.: =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) oh yea: thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
TEST for items in 2 columns to MATCH?
Can't tell what you're trying to do with this.
This formula: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144 :$AQ$1228=$AQ11,0))ROW($A$1144) Is the same as: =MATCH(TRUE,$AQ$1144:$AQ$1228=$AQ11,0)1 This formula is syntactically correct but whether it's doing what you think it's doing is another story! =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) If the result of the INDEX call is a number (<0 which it has to be) *and* AB11 is either ab or ac then it should work. If the result of the INDEX call is #N/A (the result will be either a number or #N/A) then no format will be applied since it fails the AND conditions. -- Biff Microsoft Excel MVP "nastech" wrote in message ... hi, I am trying to find if record exists for values met in 2 different columns, for use in a conditional format. info have is: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144: $AQ$1228=$AQ11,0))ROW($A$1144) not sure how got this to work in Cond. Format since (for a TRUE), when testing in an external cell get a VALUE error; but, want to compare to another column for OR( values, e.g.: =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) oh yea: thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
TEST for items in 2 columns to MATCH?
This formula is syntactically correct
Actually, it's not but I'm assuming it's just a typo: .....OR(AB11="ab',AB11="ac")) There's only a single quote after ab. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Can't tell what you're trying to do with this. This formula: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$114 4:$AQ$1228=$AQ11,0))ROW($A$1144) Is the same as: =MATCH(TRUE,$AQ$1144:$AQ$1228=$AQ11,0)1 This formula is syntactically correct but whether it's doing what you think it's doing is another story! =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) If the result of the INDEX call is a number (<0 which it has to be) *and* AB11 is either ab or ac then it should work. If the result of the INDEX call is #N/A (the result will be either a number or #N/A) then no format will be applied since it fails the AND conditions. -- Biff Microsoft Excel MVP "nastech" wrote in message ... hi, I am trying to find if record exists for values met in 2 different columns, for use in a conditional format. info have is: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144: $AQ$1228=$AQ11,0))ROW($A$1144) not sure how got this to work in Cond. Format since (for a TRUE), when testing in an external cell get a VALUE error; but, want to compare to another column for OR( values, e.g.: =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) oh yea: thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
TEST for items in 2 columns to MATCH?
hi !! thanks for responding, (did not know how to ask)
had re-phrased/posted just after this, above: how to OR() a SUMPRODUCT still a question, may have had part of the answer / misplaced info.. after some effort, notes a TEST FOR MULTIPLE COLUMNS: =SUMPRODUCT(--(E80:E1099<"x"),--(LEFT(BA80:BA1099,1)="x")) trying to find way to OR() SUMPRODUCT for: p0 or p1 =SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p0")) else: =OR(SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p0")),SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p1"))) could not remember how to resolve 2 items in the same row for being true, to where SUMPRODUCT seems to do that for you. Is there a way to shorten the last formula above? thanks. "T. Valko" wrote: Can't tell what you're trying to do with this. This formula: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144 :$AQ$1228=$AQ11,0))ROW($A$1144) Is the same as: =MATCH(TRUE,$AQ$1144:$AQ$1228=$AQ11,0)1 This formula is syntactically correct but whether it's doing what you think it's doing is another story! =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) If the result of the INDEX call is a number (<0 which it has to be) *and* AB11 is either ab or ac then it should work. If the result of the INDEX call is #N/A (the result will be either a number or #N/A) then no format will be applied since it fails the AND conditions. -- Biff Microsoft Excel MVP "nastech" wrote in message ... hi, I am trying to find if record exists for values met in 2 different columns, for use in a conditional format. info have is: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144: $AQ$1228=$AQ11,0))ROW($A$1144) not sure how got this to work in Cond. Format since (for a TRUE), when testing in an external cell get a VALUE error; but, want to compare to another column for OR( values, e.g.: =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) oh yea: thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
TEST for items in 2 columns to MATCH?
See my reply at your other post on how to use an "or" condition in
SUMPRODUCT. -- Biff Microsoft Excel MVP "nastech" wrote in message ... hi !! thanks for responding, (did not know how to ask) had re-phrased/posted just after this, above: how to OR() a SUMPRODUCT still a question, may have had part of the answer / misplaced info.. after some effort, notes a TEST FOR MULTIPLE COLUMNS: =SUMPRODUCT(--(E80:E1099<"x"),--(LEFT(BA80:BA1099,1)="x")) trying to find way to OR() SUMPRODUCT for: p0 or p1 =SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p0")) else: =OR(SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p0")),SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p1"))) could not remember how to resolve 2 items in the same row for being true, to where SUMPRODUCT seems to do that for you. Is there a way to shorten the last formula above? thanks. "T. Valko" wrote: Can't tell what you're trying to do with this. This formula: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144 :$AQ$1228=$AQ11,0))ROW($A$1144) Is the same as: =MATCH(TRUE,$AQ$1144:$AQ$1228=$AQ11,0)1 This formula is syntactically correct but whether it's doing what you think it's doing is another story! =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) If the result of the INDEX call is a number (<0 which it has to be) *and* AB11 is either ab or ac then it should work. If the result of the INDEX call is #N/A (the result will be either a number or #N/A) then no format will be applied since it fails the AND conditions. -- Biff Microsoft Excel MVP "nastech" wrote in message ... hi, I am trying to find if record exists for values met in 2 different columns, for use in a conditional format. info have is: =INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144: $AQ$1228=$AQ11,0))ROW($A$1144) not sure how got this to work in Cond. Format since (for a TRUE), when testing in an external cell get a VALUE error; but, want to compare to another column for OR( values, e.g.: =AND( INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)), OR(AB11="ab',AB11="ac")) oh yea: thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup match 2 items | New Users to Excel | |||
| Sort and Match like Items in 2 Columns | Excel Worksheet Functions | |||
| Match function multiple items | Excel Worksheet Functions | |||
| Test IF Two Numeric Values Match (from two separate Dynamic Ranges) | Excel Worksheet Functions | |||
Linear Mode
