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

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

Find next occurance
 

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" 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

Find next occurance
 

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" wrote in message
...
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" 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

Find next occurance
 

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" wrote in message
...
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" 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

Find next occurance
 

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

Regards,

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

"Jambruins" wrote in message
...
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" wrote in message
...
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" 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