Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
During an index search my first occurence is always blank.
Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Is there a way to modify thise formula to skip the first occurence
The first instance in which axis? Is there more than 2 instances? Is the table sorted so that all instances are grouped together? -- Biff Microsoft Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Don,
Try =INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$ 1:$F$101,MATCH(B15,$F$1:$F$101,FALSE),0)))+MATCH(B 15,$F$1:$F$101,FALSE),MATCH("Total",Info!$A$1:$H$1 ,)) HTH, Bernie MS Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
The first instance I am looking for is in column F, and they are never
together, but the cells are not always consistent. The info may be in row 22 and 33 one time and in row 18 and 20 the next, but there is always 2 instances. "T. Valko" wrote: Is there a way to modify thise formula to skip the first occurence The first instance in which axis? Is there more than 2 instances? Is the table sorted so that all instances are grouped together? -- Biff Microsoft Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
I tried this with adding the info! in the proper places as the data is on a
seperate page but couldn't get it to work, any ideas? "Bernie Deitrick" wrote: Don, Try =INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$ 1:$F$101,MATCH(B15,$F$1:$F$101,FALSE),0)))+MATCH(B 15,$F$1:$F$101,FALSE),MATCH("Total",Info!$A$1:$H$1 ,)) HTH, Bernie MS Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Try this:
=INDEX(Info!A1:H101,LOOKUP(2,1/(Info!F1:F101=B15),ROW(Info!F1:F101)),MATCH("Total ",Info!A1:H1,0)) -- Biff Microsoft Excel MVP "Don Murray" wrote in message ... The first instance I am looking for is in column F, and they are never together, but the cells are not always consistent. The info may be in row 22 and 33 one time and in row 18 and 20 the next, but there is always 2 instances. "T. Valko" wrote: Is there a way to modify thise formula to skip the first occurence The first instance in which axis? Is there more than 2 instances? Is the table sorted so that all instances are grouped together? -- Biff Microsoft Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Doesn't seem to pick up anything, not even the first orrurence if I enter a
figure in it. "T. Valko" wrote: Try this: =INDEX(Info!A1:H101,LOOKUP(2,1/(Info!F1:F101=B15),ROW(Info!F1:F101)),MATCH("Total ",Info!A1:H1,0)) -- Biff Microsoft Excel MVP "Don Murray" wrote in message ... The first instance I am looking for is in column F, and they are never together, but the cells are not always consistent. The info may be in row 22 and 33 one time and in row 18 and 20 the next, but there is always 2 instances. "T. Valko" wrote: Is there a way to modify thise formula to skip the first occurence The first instance in which axis? Is there more than 2 instances? Is the table sorted so that all instances are grouped together? -- Biff Microsoft Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Don,
I left out a false, which wasn't needed for my sample data set. =INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$ 1:$F$101,MATCH(B15,Info!$F$1:$F$101,FALSE),0),FALS E))+MATCH(B15,Info!$F$1:$F$101,FALSE),MATCH("Total ",Info!$A$1:$H$1,)) HTH, Bernie MS Excel MVP "Don Murray" wrote in message ... I tried this with adding the info! in the proper places as the data is on a seperate page but couldn't get it to work, any ideas? "Bernie Deitrick" wrote: Don, Try =INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$ 1:$F$101,MATCH(B15,$F$1:$F$101,FALSE),0)))+MATCH(B 15,$F$1:$F$101,FALSE),MATCH("Total",Info!$A$1:$H$1 ,)) HTH, Bernie MS Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Hmmm...
Both of the formulas suggested by Bernie and myself do work. Here's small sample that demonstrates this (with both formulas): xLookupLast.xls http://www.freefilehosting.net/download/3eilb I put everything on the same sheet. -- Biff Microsoft Excel MVP "Don Murray" wrote in message ... Doesn't seem to pick up anything, not even the first orrurence if I enter a figure in it. "T. Valko" wrote: Try this: =INDEX(Info!A1:H101,LOOKUP(2,1/(Info!F1:F101=B15),ROW(Info!F1:F101)),MATCH("Total ",Info!A1:H1,0)) -- Biff Microsoft Excel MVP "Don Murray" wrote in message ... The first instance I am looking for is in column F, and they are never together, but the cells are not always consistent. The info may be in row 22 and 33 one time and in row 18 and 20 the next, but there is always 2 instances. "T. Valko" wrote: Is there a way to modify thise formula to skip the first occurence The first instance in which axis? Is there more than 2 instances? Is the table sorted so that all instances are grouped together? -- Biff Microsoft Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
The last one from Bernie worked, which is good, can't access downloads at
work but will check it out when I get home, thanks. "T. Valko" wrote: Hmmm... Both of the formulas suggested by Bernie and myself do work. Here's small sample that demonstrates this (with both formulas): xLookupLast.xls http://www.freefilehosting.net/download/3eilb I put everything on the same sheet. -- Biff Microsoft Excel MVP "Don Murray" wrote in message ... Doesn't seem to pick up anything, not even the first orrurence if I enter a figure in it. "T. Valko" wrote: Try this: =INDEX(Info!A1:H101,LOOKUP(2,1/(Info!F1:F101=B15),ROW(Info!F1:F101)),MATCH("Total ",Info!A1:H1,0)) -- Biff Microsoft Excel MVP "Don Murray" wrote in message ... The first instance I am looking for is in column F, and they are never together, but the cells are not always consistent. The info may be in row 22 and 33 one time and in row 18 and 20 the next, but there is always 2 instances. "T. Valko" wrote: Is there a way to modify thise formula to skip the first occurence The first instance in which axis? Is there more than 2 instances? Is the table sorted so that all instances are grouped together? -- Biff Microsoft Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I skip first the first occurence in an index search?
Thanks, that worked fine. I posted from home but don't see where it shows up.
"Bernie Deitrick" wrote: Don, I left out a false, which wasn't needed for my sample data set. =INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$ 1:$F$101,MATCH(B15,Info!$F$1:$F$101,FALSE),0),FALS E))+MATCH(B15,Info!$F$1:$F$101,FALSE),MATCH("Total ",Info!$A$1:$H$1,)) HTH, Bernie MS Excel MVP "Don Murray" wrote in message ... I tried this with adding the info! in the proper places as the data is on a seperate page but couldn't get it to work, any ideas? "Bernie Deitrick" wrote: Don, Try =INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$ 1:$F$101,MATCH(B15,$F$1:$F$101,FALSE),0)))+MATCH(B 15,$F$1:$F$101,FALSE),MATCH("Total",Info!$A$1:$H$1 ,)) HTH, Bernie MS Excel MVP "Don Murray" <Don wrote in message ... During an index search my first occurence is always blank. Is there a way to modify thise formula to skip the first occurence and check for the second as the second occurence always contain the figure. Currently there is no way to remove the first occurence. =INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,), MATCH("Total",Info!$A$1:$H$1,)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
next occurence | Excel Worksheet Functions | |||
search for Index+average in threads | Excel Worksheet Functions | |||
Occurence #'s | Excel Discussion (Misc queries) | |||
How to use an index number in a search range | Excel Worksheet Functions | |||
Using Search with either vlookup or match and index | Excel Worksheet Functions |