Reference a Vlookup cell

[jassybun]

I am trying to reference an array formula result in another column, but I get a N/A error

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

whereas F3 is:

=IF(ISERROR(INDEX(LTI!$A$2:$R$988,SMALL(IF(LTI!$B$2:$B$988={"05- SERVICES US","38-TRADE SERVICES"}, ROW(LTI!$D$2:$D$988)-MIN(ROW(LTI!$D$2:$D$988))+1,""),ROW(LTI!H2)),COLUMN(LTI!H2))),"", INDEX(LTI!$A$2:$R$988,SMALL(IF(LTI!$B$2:$B$988={"05-SERVICES US","38-TRADE SERVICES"}, ROW(LTI!$D$2:$D$988)-MIN(ROW(LTI!$D$2:$D$988))+1,""),ROW(LTI!H2)),COLUMN(LTI!H2))) and the result is a 7 digit number.

 

[AliGW]

Attach the workbook - it's not possible to troubleshoot just from the formula. You also need to explain (a) what you are expecting the formula to do and (b) what you expect the result to be.

 

[NARAYANK991]

Hi ,

Since the only functions used in your formula are VLOOKUP , the only reason for a #N/A error value will be if the 7-digit number in F3 is not found in column A in the tab named Status.

However , your formula can be slightly simplified to :

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

though this change is not going to make the error value go away.

Narayan