Find next occurance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ).
 
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.
 
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.
 
works perfectly, thanks a lot

RagDyer said:
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.
Click to expand...
 
Back
Top