Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Indexing
I'm using the below Indexing w/ matching function to find entries that meet a
criteria. But now I find that i have to take it one step further... I have to exclude entries that have an adjacent cell in the same row where the first four letters are PASS. How would I make the below indexing function skip all results that start with the work "PASS" and move on until it finds a result that does not have PASS as the first four characters? =IF(D3="","",IF(ISNA(INDEX(Sheet3!A:A,MATCH(D3,She et3!B:B,0))),"None Found",INDEX(Sheet3!A:A,MATCH(D3,Sheet3!B:B,0)))) I'm trying this... =IF(LEFT(INDEX(Sheet3!D:D,MATCH(D3,Sheet3!B:B,0)), 4)="PASS",????,INDEX(Sheet3!A:A,MATCH(D3,Sheet3!B: B,0))) --- Where the four question marks are, I do not know what to put there to make it keep looking for the next appropriate value. Any help would be much appreciated. Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Indexing
I believe this will work for you:
=IF(D3="","",IF(ISERROR(INDEX(Sheet3!A:A,SUMPRODUC T((ROW($A1:$A$65535))*($B1:$B$65535=D3)*(LEFT($D1: $D$65535,4)<"PASS")))),"None Found",INDEX(Sheet3!A:A,SUMPRODUCT((ROW($A1:$A$655 35))*($B1:$B$65535=D3)*(LEFT($D1:$D$65535,4)<"PAS S"))))) Couple things to note: The SUMPRODUCT function can't callout an entire column (unless using XL2007). That why I only call out to row 65535, and not 65536 (XL would automatically change it to a column reference, thus screwing things up). Also, because those arrays are fairly large, it makes this formula more calculation intensive, slowing things down, unfortunately. To help with this, you could reduce the size of the arrays to whatever is actaully needed (perhaps 1:1000?). The nice thing is that it won't affect the rest of your formula. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Rob" wrote: I'm using the below Indexing w/ matching function to find entries that meet a criteria. But now I find that i have to take it one step further... I have to exclude entries that have an adjacent cell in the same row where the first four letters are PASS. How would I make the below indexing function skip all results that start with the work "PASS" and move on until it finds a result that does not have PASS as the first four characters? =IF(D3="","",IF(ISNA(INDEX(Sheet3!A:A,MATCH(D3,She et3!B:B,0))),"None Found",INDEX(Sheet3!A:A,MATCH(D3,Sheet3!B:B,0)))) I'm trying this... =IF(LEFT(INDEX(Sheet3!D:D,MATCH(D3,Sheet3!B:B,0)), 4)="PASS",????,INDEX(Sheet3!A:A,MATCH(D3,Sheet3!B: B,0))) --- Where the four question marks are, I do not know what to put there to make it keep looking for the next appropriate value. Any help would be much appreciated. Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Indexing
In article ,
Rob wrote: I'm using the below Indexing w/ matching function to find entries that meet a criteria. But now I find that i have to take it one step further... I have to exclude entries that have an adjacent cell in the same row where the first four letters are PASS. How would I make the below indexing function skip all results that start with the work "PASS" and move on until it finds a result that does not have PASS as the first four characters? =IF(D3="","",IF(ISNA(INDEX(Sheet3!A:A,MATCH(D3,She et3!B:B,0))),"None Found",INDEX(Sheet3!A:A,MATCH(D3,Sheet3!B:B,0)))) I'm trying this... =IF(LEFT(INDEX(Sheet3!D:D,MATCH(D3,Sheet3!B:B,0)), 4)="PASS",????,INDEX(Sheet3! A:A,MATCH(D3,Sheet3!B:B,0))) --- Where the four question marks are, I do not know what to put there to make it keep looking for the next appropriate value. Any help would be much appreciated. Rob Try the following array formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =IF(D3<"",IF(ISNUMBER(MATCH(1,IF(Sheet3!B2:B100=D 3,IF(LEFT(Sheet3!D2:D10 0,4)<"Pass",1)),0)),INDEX(Sheet3!A2:A100,MATCH(1, IF(Sheet3!B2:B100=D3,IF (LEFT(Sheet3!D2:D100,4)<"Pass",1)),0)),""),"") Note that unless you're using Excel 2007, the array formula will not accept whole column references. Also, if you're using Excel 2007, IFERROR can be used instead of ISNUMBER/MATCH to trap errors. -- Domenic http://www.xl-central.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indexing | Excel Discussion (Misc queries) | |||
Tab indexing | Excel Discussion (Misc queries) | |||
Indexing | Excel Discussion (Misc queries) | |||
Indexing of Name | Excel Worksheet Functions | |||
Indexing a row | Excel Worksheet Functions |