Conditional Indexing

[Rob]

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,Sheet3!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

 

[Luke M]

I believe this will work for you:

=IF(D3="","",IF(ISERROR(INDEX(Sheet3!A:A,SUMPRODUCT((ROW($A1:$A$65535))*($B1:$B$65535=D3)*(LEFT($D1:$D$65535,4)<>"PASS")))),"None
Found",INDEX(Sheet3!A:A,SUMPRODUCT((ROW($A1:$A$65535))*($B1:$B$65535=D3)*(LEFT($D1:$D$65535,4)<>"PASS")))))

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.

 

[Domenic]

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,Sheet3!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=D3,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.