0

The formula below returns the correct value and removes the #NA but not the null values.

How do I remove “0” null?

=IFERROR(INDEX('[request.xlsm]Notes'!$A:$K,MATCH($C2,'[request.xlsm]Notes'!$B:$B,0),11),"")
Nimantha
  • 6,405
  • 6
  • 28
  • 69
Rachel Taylor
  • 57
  • 3
  • Does this answer your question? [IFERROR, INDEX, MATCH returning zeros instead of blanks](https://stackoverflow.com/questions/31255281/iferror-index-match-returning-zeros-instead-of-blanks) – redstang423 Jun 05 '20 at 13:47

1 Answers1

0

IFERROR function captures the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

A zero is not the same as #NULL!, so IFERROR will not filter that out, and the MATCH function should not be returning #NULL! anyway. If you're seeing zeros, it is likely because you're matching to blanks in the data in your worksheet. If the blanks are expected in the data, and the match response must also be zero, you can nest an IF statement to first evaluate to see if the expression will evaluate to zero.

=IF(INDEX('[request.xlsm]Notes'!$A:$K,MATCH($C2,'[request.xlsm]Notes'!$B:$B,0),11)=0,"",IFERROR(INDEX('[request.xlsm]Notes'!$A:$K,MATCH($C2,'[request.xlsm]Notes'!$B:$B,0),11),""))

redstang423
  • 123
  • 1
  • 1
  • 10