Conditional formatting syntax question

[Paul Hyett]

I would like to flag if an item of text in one column matches one in a
given range in another, but I can't seem to get the syntax right to do
this. Preferably the formula could be easily copied down and adjusted
for a range fixed at one end or the other.

I am using Excel 2003.

Thanks for any help you can offer.

 

[isabelle]

hi,

you must use a name in the formula

=EQUIV(A1;rng;0)

 

[isabelle]

oups'

=MATCH(A1,plg,0)

 

[joeu2004]

I would like to flag if an item of text in one column
matches one in a given range in another, but I can't
seem to get the syntax right to do this. Preferably
the formula could be easily copied down and adjusted for a range fixed at
one end or the other. I am using Excel 2003.

It might be helpful if you showed us what you tried. If the text to be
flagged is in A1:A100 and the ranged to be matched is B1:B100, then select
A1:A100, click on Format, then Conditional Formatting, select Formula Is,
and enter the following formula [1]:

=IF(A1<>"",ISNUMBER(MATCH(A1,$B$1:$B$100,0)))

Then click on Format, select the font, border and pattern options that you
want and click OK.

By using a relative reference for A1, it will change appropriately to A2, A3
etc for each of A1:A100.

You can also copy the conditional format by copying A1, then using
paste-special-formats to apply to another cell or cells in column A.

 

[Paul Hyett]

I would like to flag if an item of text in one column
matches one in a given range in another, but I can't
seem to get the syntax right to do this. Preferably
the formula could be easily copied down and adjusted for a range
fixed at one end or the other. I am using Excel 2003.

It might be helpful if you showed us what you tried. If the text to be
flagged is in A1:A100 and the ranged to be matched is B1:B100, then
select A1:A100, click on Format, then Conditional Formatting, select
Formula Is, and enter the following formula [1]:

=IF(A1<>"",ISNUMBER(MATCH(A1,$B$1:$B$100,0)))

Should this be ISTEXT?

Then click on Format, select the font, border and pattern options that
you want and click OK.

Unfortunately this won't work, as the cell I want to place the
conditional format in, is not in either of the ranges I'm trying to
test.

Lets see if I can explain better...

A B C
1 Orange Orange
2 Apple Apple
3 Lemon Lemon
4 Banana Banana
5 Grape Grape

(In the above example, I'd like to put the conditional format in C1)

I like to check if the text/value in B1 occurs anywhere in the range
A1:A5 (the position doesn't matter, only the occurrence), and if it
does, have the fact indicated in C1.

 

[joeu2004]

Should this be ISTEXT?

No. MATCH returns a row number if a match is found; an error otherwise.

Unfortunately this won't work, as the cell I want to place
the conditional format in, is not in either of the ranges
I'm trying to test. [....]
(In the above example, I'd like to put the conditional format in C1)

I like to check if the text/value in B1 occurs anywhere in the range
A1:A5 (the position doesn't matter, only the occurrence), and if it
does, have the fact indicated in C1.

The paradigm that I provided previously will work. Select C1:C5 and enter
the following Conditional Format formula (see the details in my previously
response).

=IF(B1<>"",ISNUMBER(MATCH(B1,$A$1:$A$5,0)))

All that has changed is reversing the use of column A and column B
references.

 

[Paul Hyett]

Should this be ISTEXT?

No. MATCH returns a row number if a match is found; an error otherwise.

Unfortunately this won't work, as the cell I want to place
the conditional format in, is not in either of the ranges
I'm trying to test. [....]
(In the above example, I'd like to put the conditional format in C1)

I like to check if the text/value in B1 occurs anywhere in the range
A1:A5 (the position doesn't matter, only the occurrence), and if it
does, have the fact indicated in C1.

The paradigm that I provided previously will work. Select C1:C5 and
enter the following Conditional Format formula (see the details in my
previously response).

=IF(B1<>"",ISNUMBER(MATCH(B1,$A$1:$A$5,0)))

All that has changed is reversing the use of column A and column B
references.

Thanks - I'll have a play with this to see if it does what I want, and
let you know how it goes.

 

[Paul Hyett]

Thanks - I'll have a play with this to see if it does what I want, and
let you know how it goes.

Thanks - it does what it is supposed to, though it does produce the
occasional unexpected result (isn't that always the way).

However, when I tried to extend the conditional format formula to
include a 2nd cell to cross-check with, it spat it out :

=IF((and(O3<>"",ISNUMBER(MATCH(O3,L4:L13,0),(p3<>"",ISNUMBER(MATCH(p3,M4:
M13,0))))))

Yes, I know I've probably screwed up the brackets - but is there any
reason why this won't work if they are in the right places?

 

[joeu2004]

[....]
Thanks - it does what it is supposed to, though it
does produce the occasional unexpected result
(isn't that always the way).

No, it isn't. If you gave us more information, we might be able to help you
perfect the formula (or the one below).

At a minimum, copy-and-paste from the Conditional Formatting formula field
into your posting so that we see the syntax exactly as you entered it. Do
not retype the formula, since that might introduce syntax differences.

Better: upload an example Excel file to a file-sharing website, be sure to
mark the uploaded file "shared", and post the "shared" URL (link;
http://...) here. The following is a list of some free file-sharing
websites.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

However, when I tried to extend the conditional format
formula to include a 2nd cell to cross-check with, it
spat it out :

=IF((and(O3<>"",ISNUMBER(MATCH(O3,L4:L13,0),
(p3<>"",ISNUMBER(MATCH(p3,M4:M13,0))))))

First, L4:L13 and M4:M13 must be absolute references, i.e. $L$4:$L$13 and
$M$4:$M$13, in order for you to copy the conditional format into other cells
in columns O and P or in a parallel column. That might explain why the
original formula produced "occassional unexpected results" as you noted
above.

Second, the new formula can be written:

=IF(AND(O3<>"",P3<>""),
IF(ISNUMBER(MATCH(O3,$L$4:$L$13,0)),
ISNUMBER(MATCH(P3,$M$4:$M$13,0))))

Although the above is the most efficient, for edification purposes, the
following is how you might write it using AND in the manner that you tried:

=AND(O3<>"",ISNUMBER(MATCH(O3,$L$4:$L$13,0)),
P3<>"",ISNUMBER(MATCH(P3,$M$4:$M$13,0)))

 

[Paul Hyett]

=IF(B1<>"",ISNUMBER(MATCH(B1,$A$1:$A$5,0)))

[....]
Thanks - it does what it is supposed to, though it
does produce the occasional unexpected result
(isn't that always the way).

No, it isn't. If you gave us more information, we might be able to
help you perfect the formula (or the one below).

At a minimum, copy-and-paste from the Conditional Formatting formula
field into your posting so that we see the syntax exactly as you
entered it.

That's what I actually did, below.

First, L4:L13 and M4:M13 must be absolute references, i.e. $L$4:$L$13
and $M$4:$M$13, in order for you to copy the conditional format into
other cells in columns O and P or in a parallel column. That might
explain why the original formula produced "occassional unexpected
results" as you noted above.

Second, the new formula can be written:

=IF(AND(O3<>"",P3<>""),
IF(ISNUMBER(MATCH(O3,$L$4:$L$13,0)),
ISNUMBER(MATCH(P3,$M$4:$M$13,0))))

Although the above is the most efficient, for edification purposes, the
following is how you might write it using AND in the manner that you
tried:

=AND(O3<>"",ISNUMBER(MATCH(O3,$L$4:$L$13,0)),
P3<>"",ISNUMBER(MATCH(P3,$M$4:$M$13,0)))

Thank you for all your help - I greatly appreciate it!