MATCH function

[F. Lawrence Kulchar]

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

 

[Gary''s Student]

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"

 

[FLKulchar]

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