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