Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last occurance of character in text string | Excel Worksheet Functions | |||
Find and Replace | Excel Worksheet Functions | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |