ISNA not compatible with CONCATENATE?

[excel.vba]

I have formula which returns a #NA which is fine since sometimes the value i am looking up may not be found.

=IF(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE)="","not started",IF(AND(NOT(ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE))),ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$L,8,FALSE))),"started","completed"))

For presentation sake i want it to return "NA" instead of "#NA" when this happens. I tried using ISNA and IF to give me what i want. When i add the ISNA to the above formula i get the value "TRUE". However, when i include the IF function it gives me the error "The formula u typed in contains an error" message and highlights the second CONCATENATE in the formula. I am not sure whether it is a simple brackets error or something else.

What i have been trying(left the if statement is false blank for easier reading):
=IF(ISNA(IF(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE)="","not started",IF(AND(NOT(ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE))),ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$L,8,FALSE))),"started","completed"))),"NA",)

Thank You!

 

[T. Valko]

I have formula which returns a #NA which is fine since sometimes the value i am looking up may not be found.

=IF(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE)="","not started",IF(AND(NOT(ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE))),ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$L,8,FALSE))),"started","completed"))

For presentation sake i want it to return "NA" instead of "#NA" when this happens. I tried using ISNA and IF to give me what i want. When i add the ISNA to the above formula i get the value "TRUE". However, when i include the IF function it gives me the error "The formula u typed in contains an error" message and highlights the second CONCATENATE in the formula. I am not sure whether it is a simple brackets error or something else.

What i have been trying(left the if statement is false blank for easier reading):
=IF(ISNA(IF(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE)="","not started",IF(AND(NOT(ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE))),ISBLANK(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$L,8,FALSE))),"started","completed"))),"NA",)

Thank You!

What version of Excel are you using?

 

[excel.vba]

Excel 2003.

Thanks for the quick reply

 

[Zack Barresse]

2007 & up has the IFERROR() function, otherwise you'll need to double nest you formula, first in an IF(ISNA(formula), NA resultant, formula)

 

[AlphaFrog]

Try something like this...

=IF(ISNA(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE)), "NA",
  IF(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$H,4,FALSE)="","not started",
  IF(VLOOKUP(CONCATENATE($B6,G$1),'3a. Tier1 Msg'!$E:$L,8,FALSE)="","started","completed")))

 

[excel.vba]

Thank you so much. It works perfectly fine, but i was wondering why is it not possible to put the entire if statement into the ISNA instead of only the first vlookup which causes the NA.

 

[T. Valko]

Excel 2003.

Thanks for the quick reply

Try this...

=IF(ISNA(MATCH($B6&G$1,'3a. Tier1 Msg'!$E:$E,0)),"N/A",
IF(VLOOKUP($B6&G$1,'3a. Tier1 Msg'!$E:$H,4,0)="","not started",
IF(AND(VLOOKUP($B6&G$1,'3a. Tier1 Msg'!$E:$H,4,0)<>"",
VLOOKUP($B6&G$1,'3a. Tier1 Msg'!$E:$L,8,0)=""),"started","completed")))