Find next occurance

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

I get a #NAME? error when I enter that in cell C2. I did use cse when I
entered it also so that is not the problem. Any other ideas? Thanks.

"RagDyer" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

The only way I can see a #NAME? error being returned is if one of the
functions were misspelled.
"Misspelled" could *also* mean an added <space>!

The *only* spaces in the formula are in your sheet names, between the "NHL"
and "Schedule".
So, check to see if you might be a victim of 'line wrap', where a <space> or
two might have been added somewhere in a function name.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jambruins" <[email protected]> wrote in message
news:[email protected]...
>I get a #NAME? error when I enter that in cell C2. I did use cse when I
> entered it also so that is not the problem. Any other ideas? Thanks.
>
> "RagDyer" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>

works perfectly, thanks a lot

"RagDyer" wrote:

> The only way I can see a #NAME? error being returned is if one of the
> functions were misspelled.
> "Misspelled" could *also* mean an added <space>!
>
> The *only* spaces in the formula are in your sheet names, between the "NHL"
> and "Schedule".
> So, check to see if you might be a victim of 'line wrap', where a <space> or
> two might have been added somewhere in a function name.
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Jambruins" <[email protected]> wrote in message
> news:[email protected]...
> >I get a #NAME? error when I enter that in cell C2. I did use cse when I
> > entered it also so that is not the problem. Any other ideas? Thanks.
> >
> > "RagDyer" wrote:
> >
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
>
>

You're welcome, and appreciate the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Jambruins" <[email protected]> wrote in message
news:[email protected]...
works perfectly, thanks a lot

"RagDyer" wrote:

> The only way I can see a #NAME? error being returned is if one of the
> functions were misspelled.
> "Misspelled" could *also* mean an added <space>!
>
> The *only* spaces in the formula are in your sheet names, between the
"NHL"
> and "Schedule".
> So, check to see if you might be a victim of 'line wrap', where a <space>
or
> two might have been added somewhere in a function name.
>
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
> "Jambruins" <[email protected]> wrote in message
> news:[email protected]...
> >I get a #NAME? error when I enter that in cell C2. I did use cse when I
> > entered it also so that is not the problem. Any other ideas? Thanks.
> >
> > "RagDyer" wrote:
> >
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
>
>