Find next occurance
Try this *array* formula in C2:
=IF(COUNTIF('NHL SCHEDULE'!$H$1:$H$100,$A$1)=ROWS($1:1),INDEX('NHL
SCHEDULE'!$I$1:$I$100,SMALL(IF('NHL
SCHEDULE'!$H$1:$H$100=$A$1,ROW($1:$100)),ROWS($1:1 ))),"")
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
*AFTER* the CSE entry, copy the formula down Column C as far as needed to
return *all* the occurrences of the team entered in A1.
Also, since this is an array formula, you *cannot* use total column
reference ( H:I ).
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jambruins" wrote in message
...
In cell C2 I have a vlookup formula (=VLOOKUP(A1,'NHL SCHEDULE'!H:I,2,0))
that looks up the word ducks in column H in a tab called NHL Schedule .
The
words ducks occurs a number of times in column H in the NHL Schedule tab.
I
would like a formula for cell C3 that looks up the next occurance of the
word
ducks. Thanks
|