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.

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.

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.

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.

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.