Handing N/A in Array Formulas

Question

I want to figure out how to handle N/A in formulas that use arrays. I'm using the Bloomberg Data History Add-In BDH to get the latest EX-DIV date. But in cases where there is an N/A in the array, the output is N/A instead of the latest EX-DIV date.

This is the formula I am using:

=IF(AN6="","",INDEX(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true"),MATCH(MIN(ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2)),ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2),0)))

• AM6 is the sedol
• AN6 is a check for data
• A2 is today's date

Answer 1

Have you tried encapsulating the entire thing with IFERROR()? It would become something like

=IFERROR(IF(AN6="","",INDEX(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true"),MATCH(MIN(ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2)),ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2),0))),$A$2)