OmniDocHow to hide and not display when the Excel search result is 0
When searching using VLOOKUP, INDEX plus MATCH or XLOOKUP, 0 will be returned if the source data is empty. This article explains the reasons, gives how to write the formula to determine the null value, how to hide 0 in a custom format, and how to distinguish blank from real 0.
Key points of this article
- When the find function encounters a blank cell, it returns 0 instead of a null value, so it looks like "0 found".
- Use IF with INDEX to determine whether the target cell is empty. If it is empty, it will return empty text, otherwise it will return the original value.
- The custom format "0;-0;;@" only hides the displayed 0, the value of the cell remains unchanged, but the real 0 will also be hidden together.
- IFERROR handles the error value that occurs when it cannot be found. The 0 returned for blank cells is invalid. The two types of problems must be repaired separately.
- After writing the formula, test it once each with an unfindable value, a blank record, and a real 0.
When making tables, many people will use VLOOKUP, INDEX plus MATCH or XLOOKUP to get numbers from other tables. If the corresponding position is empty, the result will not be blank, but a prominent 0. If a row of zeros is sandwiched between a column of numbers, the report will look unclean, and the sum and average will also be affected.
This article is written for people who organize data every day and want to display "no data" as blank. After reading, you can distinguish two types of situations: one is that the source cell is indeed empty, and the other is that the corresponding row cannot be found at all. The former needs to be handled by using IF to judge the null value, while the latter is what IFERROR is used for. The formulas in this article can be applied to your own tables by simply replacing the range and column numbers.
The search result is 0, how to deal with it so that it is not displayed? One sentence conclusion: first determine whether the source cell is empty, and if it is empty, return empty text; only when it is confirmed that 0 has no business significance, use a custom format to hide it uniformly.
| your situation | Recommended practices | reason |
|---|---|---|
| There are blank cells in the source data and I want them to be displayed as empty. | Use IF plus INDEX to determine whether it is equal to empty text | Can distinguish between blank and real 0, and will not accidentally damage valid data |
| If the corresponding row cannot be found, #N/A will be returned. | Use IFERROR or IF plus ISNA to wrap a layer | This is a search failure, not a null value, and is handled differently. |
| You don’t want to see 0 in the entire column, and 0 does not participate in subsequent calculations | Set custom format "0;-0;;@" | Only the display is changed, the value of the cell is still 0 |
| 0 is real data, for example, the inventory is indeed 0 | Leave 0, marked with conditional formatting | After hiding, readers will mistakenly think that there is no record |
| The result must also be referenced or summed by other formulas | Before returning empty text, confirm that subsequent formulas can accept text | Empty text will be ignored by SUM, but will reduce the AVERAGE count. |
First determine what kind of problem it is
| symptoms | Common causes | How to confirm |
|---|---|---|
| The lookup column shows 0, and the corresponding position of the source table looks empty. | The source cell is empty and the lookup function is read as 0 | Point in the source cell, the edit bar is blank |
| The search column displays 0, and 0 can be seen in the corresponding position of the source table. | The source data itself is the number 0 | 0 is displayed in the edit bar |
| Display # NCMA | The lookup value does not exist, or the format of both sides is inconsistent. | Check to see if the lookup value and the lookup area are both text or numbers |
| 0 is displayed, but the location of the source table clearly has content. | Find zone or return column reference misplacement | Check whether the row range of the lookup column is consistent with that of the return column |
# item-by-item troubleshooting and repair
- First confirm where 0 comes from. Point in the cell where the formula is located, and then click the corresponding position of the source table, see the edit bar. The edit bar is blank, indicating that it is an empty cell; the edit bar displays 0, indicating that it is a real number.
two. Check the search method. The fourth parameter of VLOOKUP should write 0 or FALSE to indicate an exact match. Omit or write as 1, it may match the wrong line, and the result will be unexpected.
- Check the lookup values of the two tables. The same "001", one is a text, one is a number, the search will fail, and finally there is # N _ A, which is mistakenly changed to 0.
- To choose to practice the Dharma according to the reasons, see the following three sections.
case 1: the source cell is indeed empty
Suppose the lookup value is A2, column An of the source table Sheet2 is numbered, and column C is the data to be fetched. You can write as follows:
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"",IF(INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))="","",INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))))
The key to this formula is that INDEX returns a reference to the target cell. The comparison of the blank cell with "" is true, so the empty text is returned; the comparison of true 0 and "" is false, so 0 is retained. The outer ISNA section makes the lines that cannot be found also appear empty. If you want to see # Number A when you can't find it, just delete this layer. MATCH appears many times in the formula. When the table is large, you can calculate the line number in the auxiliary column before referencing it.
If you determine that 0 has no business meaning, you can write it in a shorter way: return empty text when the result of IF plus VLOOKUP equals 0. But it will also hide the real 0, so think carefully before using it.
case 2: the corresponding line cannot be found
In this case, the result is the wrong value, not 0. Enclose the original formula with IFERROR, for example, IFERROR (original formula, ""). It only works for error values and has no effect on the 0 returned by blank cells, so it cannot replace the writing of case one.
case 3: the whole column does not want to see 0
Select the area you want to process, open the cell format, and type "0position shareholder @" in the custom type. The third paragraph of this format is empty, indicating that the zero value is not displayed. Note that it only changes the display that the value of the cell is still 0, and calculations such as sum are not affected; but it hides all zeros, including the real data you need to keep.
Finally, test with three samples: a number that cannot be found, a number with an empty source cell, and a number with a source data of 0. Check the results one by one to see if they meet expectations, and then fill the entire column down.
# how to avoid the next occurrence
-unify the numbering format in the source table, all as text or all as numbers, to avoid failure due to different types of search. -the search area uses full column references or table structure, so you don't have to change the formula range one by one after adding new data. -distinguish "null value" from "0" in the source table: leave blank where there is no data, do not fill in the 0 space, otherwise the lookup function can not distinguish. -for important statements, always use the same set of judgment formulas, and write the meaning of each column on the table head to facilitate others to check. -for the contents of the table identified by machine translation or OCR, check the number and value before import to avoid failure due to recognition error.
# frequently asked questions (FAQ)
1. Why is IFERROR,0 set or displayed?
IFERROR only handles error values. When the source cell is empty, the lookup function returns 0, which is not an error, so IFERROR will not take over. You need to use IF to determine whether the target cell is empty.
2. Can the "if not found" parameter of XLOOKUP solve this problem directly?
It can solve the situation that can not be found, just set the parameter to empty text. However, if the source cell itself is empty, XLOOKUP still returns 0, and you need to use IF to determine again.
3. After replacing 0 with empty text, will the sum and peace mean change?
The sum result does not change because SUM ignores the text. But AVERAGE will count one less, and the count will be reduced. Make sure this is in line with your caliber before statistics.
4. After hiding 0, how to restore the display?
Select the area and change the cell format back to normal or numerical value to restore the display. Pay attention to check if there is any custom format residue.
# Summary
The result of the search is 0, in most cases because the source cell is empty. Using IF to judge whether it is empty or not can keep the blank space without accidentally injuring the real 0; only when it is confirmed that 0 is meaningless, it can be uniformly hidden in a custom format. After the formula is written, each of the three samples will be tested once, and the results will not be prone to error.
