Problem getting rid of N/A

[thewiseguy]

Hi everyone,

I can't figure out the configuration for the "IFNA" formula to rid this example of its "N/A" value. Can anyone help?

=(INDEX(FORMULA2!$I$58:$I$93,MATCH(AO5,IF(FORMULA2!$B$58:$B$93=AP5,FORMULA2!$A$58:$A$93),0)))

 

[Jonmo1]

the basic syntax to eliminate N/A is

=IF(ISNA(originalformula),"",originalformula)

so for your example

=IF(ISNA((INDEX(FORMULA2!$I$58:$I$93,MATCH(AO5,IF(FORMULA2!$B$58:$B$93=AP5,FORMULA2!$A$58:$A$93),0)))),"",(INDEX(FORMULA2!$I$58:$I$93,MATCH(AO5,IF(FORMULA2!$B$58:$B$93=AP5,FORMULA2!$A$58:$A$93),0))))

 

[thewiseguy]

thanks Jon. I think you helped me out on this last time. I must have tried it 20 times on the INDEX function to no avail. Hopefully I can get it now.

 

[Aladin Akyurek]

Hi everyone,

I can't figure out the configuration for the "IFNA" formula to rid this example of its "N/A" value. Can anyone help?

=(INDEX(FORMULA2!$I$58:$I$93,MATCH(AO5,IF(FORMULA2!$B$58:$B$93=AP5,FORMULA2!$A$58:$A$93),0)))

When successful, what does this formula return - a number or text?

 

[thewiseguy]

a number.

 

[Aladin Akyurek]

a number.

Control+shift+enter...

=LOOKUP(9.99999999999999E+307,
     CHOOSE({1,2},
       0,
       INDEX(FORMULA2!$I$58:$I$93,
          MATCH(AO5,IF(FORMULA2!$B$58:$B$93=AP5,
                                 FORMULA2!$A$58:$A$93),0))))