If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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,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
|
|||
|
|||
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,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
|
|||
|
|||
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,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!D210 0,4)"Pass",1)),0)),INDEX(Sheet3!A2:A100,MATCH(1, IF(Sheet3!B2:B100=D3,IF (LEFT(Sheet3!D2100,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 |
Thread Tools | |
Display Modes | |
|
|