MATCH function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

F. Lawrence Kulchar - 30 May 2008 17:57 GMT

I have, in cell C5, the following:

=MATCH(B5&"*",A1:A1286,0)

The problem is, say in cell B5, is the word: 'SYDNEY'.

I have, perhaps, 4 different 'Sydneys' (scattered throughout A1:A1286)...my
result gives ONLY, the first cell reference scrolling down!

How can I "bring up" the other "Sydney"'s??

Thanks,

FLKulchar

Reply to this Message

Gary''s Student - 30 May 2008 18:46 GMT

Say, to be simple:

=MATCH("sidney",A1:A100,0) will find the first sidney

Clearly if the first sidney is in cell A13, then to find the "next" sidney
we would like to use:

=MATCH("sidney",A14:A100,0)+13 but automate the process.

So if D1 contains:

=MATCH("sidney",A1:A100,0)
then in D2 enter:
=MATCH("sidney",INDIRECT("A" & D1+1 & ":A100"),0)+D1
and copy down. This will give you the row numbers of all the "sidneys"

Signature

Gary''s Student - gsnu200789

> I have, in cell C5, the following:
>
[quoted text clipped - 10 lines]
>
> FLKulchar

Reply to this Message

FLKulchar - 31 May 2008 04:24 GMT

I am still having a problem with your match "automation"...although your
first match fct. works fine. Let me repeat:

In cell b5, I have "Sydney"

In cell c5, i have =MATCH($B$5&"*",A1:A1291,0)
(which finds me my first sydney)

In cell d5, I have =HYPERLINK("#A"&C5,"GO TO")

If I copy down from C5 (as you suggest) as follows:

In cell C6 = MATCH($B$5&"*",INDIRECT("A"&C5 +1&":A1291",0)+C5

IT DOES NOT WORK ...

There is something wrong with the "INDIRECT" part of what I am doing.

Please continue to help...I need it.

Thanks,

FLKulchar

t" <GarysStudent@discussions.microsoft.com> wrote in message
news:0C69A510-4E09-4168-90BA-93CAEB838860@microsoft.com...

> Say, to be simple:
>
[quoted text clipped - 27 lines]
>>
>> FLKulchar

Reply to this Message