How to get rid of NA results in array formulas

[Arjen Bakker]

Hello together,

is there a way to avoid "NA" results in array formulas ?
I know I could refer to the array formula in another cell but that's not very elegant.

Thanks so much.
Arjen

 

[sheetspread]

You can return a "" (virtual blank) or conditionally format errors to background color.

 

[Eric W]

You could wrap your formula with IFERROR or IFNA depending on your version of Excel.

=INDEX(....)

becomes

=IFERROR(INDEX(....),"No match")

or

=IFERROR(INDEX(....),"")

 

[Arjen Bakker]

You could wrap your formula with IFERROR or IFNA depending on your version of Excel.

=INDEX(....)

becomes

=IFERROR(INDEX(....),"No match")

or

=IFERROR(INDEX(....),"")

Hi Eric,
Maybe I was not clear: [TABLE="width: 44"]
<colgroup><col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <tbody>[TR]
[TD="class: xl74, width: 58, bgcolor: transparent"] ={INDEX$A$2:$N$209;MATCH(1;($A$2:$A$209=O$1)*($B$2:$B$209=$N138);0);6)}

results in "NA"
cannot edit the formula without loosing
the brackets
unfortunately I can't say cheers at the moment.
I'll do that tomorrow
Thanks again ... Arjen[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

The brackets are just an indicator that this is an array formula. Select the cell with the formula, and press F2. You should see the formula in the formula bar without the {}. Edit the formula as I specified (use a semicolon instead of a comma, since that's how you have your regional settings), then press Control+Shift+Enter when done. The {} will reappear around the whole formula.

 

[jtakw]

Hi Eric,
Maybe I was not clear: [TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl74, width: 58, bgcolor: transparent"]={INDEX($A$2:$N$209;MATCH(1;($A$2:$A$209=O$1)*($B$2:$B$209=$N138);0);6)}

results in "NA"
cannot edit the formula without loosing
the brackets
unfortunately I can't say cheers at the moment.
I'll do that tomorrow
Thanks again ... Arjen
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

There's also a missing Bracket in your formula, or maybe it's just a typo in your posting. FYI

 

[Arjen Bakker]

The brackets are just an indicator that this is an array formula. Select the cell with the formula, and press F2. You should see the formula in the formula bar without the {}. Edit the formula as I specified (use a semicolon instead of a comma, since that's how you have your regional settings), then press Control+Shift+Enter when done. The {} will reappear around the whole formula.

great !

Thanks a lot
I' ll drink a beer on you tomorrow !