Changing a vlookup to a index/match that uses array

[jassybun]

Original formula:

=IF(IF((VLOOKUP(E2,Status!A:B,2,FALSE))=0,"ACTIVE",VLOOKUP(E2,Status!A:B,2,FALSE))="N", "LOA", "ACTIVE")

This doesn't work, gives me an n/a. I believe because it is referencing a cell that has an array in it:

=IF(ISERROR(INDEX(LTI!$A$2:$R$1000,SMALL(IF(LTI!$B$2:$B$1000={"05","38"}, ROW(LTI!$D$2:$D$1000)-MIN(ROW(LTI!$D$2:$D$1000))+1,""),ROW(LTI!H2)),COLUMN(LTI!H2))),"", INDEX(LTI!$A$2:$R$1000,SMALL(IF(LTI!$B$2:$B$1000={"05-","38"}, ROW(LTI!$D$2:$D$1000)-MIN(ROW(LTI!$D$2:$D$1000))+1,""),ROW(LTI!H2)),COLUMN(LTI!H2)))

I tried to use the indirect with index/ match function with no luck, I get an n/a too:

=INDEX(INDIRECT("Status" & "!" & "$C$1:$C$1000"),MATCH(E2,INDIRECT("Status" & "!" & "$B$1:$B$1000"),0))

 

[pecoflyer]

Try =IF(VLOOKUP(E2,Status!A:B,2,FALSE)=0,"ACTIVE",IF(VLOOKUP(E2,Status!A:B,2,FALSE)="N", "LOA", "ACTIVE"))