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.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

hi,

you must use a name in the formula

=EQUIV(A1;rng;0)

--
isabelle

oups'

=MATCH(A1,plg,0)

--
isabelle

Le 2011-11-04 04:55, isabelle a écrit :
> hi,
>
> you must use a name in the formula
>
> =EQUIV(A1;rng;0)
>

"Paul Hyett" <(E-Mail Removed)> wrote:
> 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.

-----
[1] In fact, it is sufficient to write =IF(A1<>"",MATCH(A1,$B$1:$B$100,0)).
I prefer to use ISNUMBER(MATCH(...)) because that is what we would write in
a cell formula.

On Fri, 4 Nov 2011 at 08:23:09, joeu2004 <(E-Mail Removed)> wrote in
microsoft.public.excel :

>"Paul Hyett" <(E-Mail Removed)> wrote:
>> 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.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

"Paul Hyett" <(E-Mail Removed)> wrote:
> On Fri, 4 Nov 2011 at 08:23:09, joeu2004 <(E-Mail Removed)> wrote:
>>=IF(A1<>"",ISNUMBER(MATCH(A1,$B$1:$B$100,0)))
>
> Should this be ISTEXT?

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


"Paul Hyett" <(E-Mail Removed)> wrote:
> 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.

On Fri, 4 Nov 2011 at 11:54:38, joeu2004 <(E-Mail Removed)> wrote in
microsoft.public.excel :

>"Paul Hyett" <(E-Mail Removed)> wrote:
>> On Fri, 4 Nov 2011 at 08:23:09, joeu2004 <(E-Mail Removed)> wrote:
>>>=IF(A1<>"",ISNUMBER(MATCH(A1,$B$1:$B$100,0)))
>>
>> Should this be ISTEXT?
>
>No. MATCH returns a row number if a match is found; an error otherwise.
>
>
>"Paul Hyett" <(E-Mail Removed)> wrote:
>> 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, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

On Sat, 5 Nov 2011 at 07:08:50, Paul Hyett
<(E-Mail Removed)> wrote in microsoft.public.excel :
>>
>>=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.

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

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?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

"Paul Hyett" <(E-Mail Removed)> wrote:
>>>=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). :P

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


"Paul Hyett" <(E-Mail Removed)> wrote:
> 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)))

On Sat, 5 Nov 2011 at 09:27:06, joeu2004 <(E-Mail Removed)> wrote in
microsoft.public.excel :

>"Paul Hyett" <(E-Mail Removed)> wrote:
>>>>=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). :P
>
>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.
>
>"Paul Hyett" <(E-Mail Removed)> wrote:
>> 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)))

Thank you for all your help - I greatly appreciate it!
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)