Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH function
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" -- Gary''s Student - gsnu200789 "F. Lawrence Kulchar" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH function
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" wrote in message ... 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" -- Gary''s Student - gsnu200789 "F. Lawrence Kulchar" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |