OmniDocHow to find data if there are merged cells in the VLOOKUP table
When the source table has merged cells, VLOOKUP often returns blank, #N/A or wrong rows. This article explains the reason and gives a complete method of first completing the data, and then using auxiliary columns and exact match search.
Key points of this article
- Only the upper left corner of the merged cell stores the value, and the rest of the cells are empty. When VLOOKUP finds these spaces, it will return blank or #N/A.
- Unmerge first and fill downwards so that each line has complete keywords. This is a direct fix and can also avoid repeated mistakes in the future.
- When you do not want to change the original table, use auxiliary columns to complete the values in the merged area, and then let VLOOKUP point to the auxiliary columns.
- Approximate matching can easily return wrong rows on unsorted or null-valued data. It is recommended to always use exact matching when searching.
When many people use VLOOKUP for the first time, the table is neat: one record per row, and the keywords are in the left column. However, there are often merged cells in real worksheets. For example, several rows are merged under the same department, and the same order number corresponds to multiple details. In this case VLOOKUP will either return blank or #N/A, or sometimes even return a result that looks normal but actually has one wrong line.
This article is for people who have already written the VLOOKUP formula, but the results don't match up. After reading, you will be able to determine what kind of problem you are encountering, know why merging cells interferes with the search, and stabilize the search by completing data or auxiliary columns.
When VLOOKUP encounters merged cells, how should it be handled without making an error? One sentence conclusion
First cancel the merged cells of the source table, fill the blank cells with the value of the previous row, so that each row carries complete keywords, and then use exact match to search; if you do not want to change the original table, create an auxiliary column next to it to complete the same.
| your situation | Recommended practices | reason |
|---|---|---|
| The source table is maintained by you and can be modified at will. | Cancel the merge, fill in the blanks downwards, and keep the original table as "one record per row" | The data structure is clean, and subsequent filtering, pivoting, and formulas will no longer cause problems. |
| The source table is sent by others and the structure cannot be changed. | Add an auxiliary column next to it, use formulas to complete the values in the merged area row by row, and search for the auxiliary column. | The original data is not moved, and the search results are still correct. |
| Only one or two items need to be checked, the amount of data is small | Manually find the first cell of the merged area and directly reference the cell | Fast, but needs to be rechecked after the data is updated |
| Find if the column is numeric or numbered, the result is always #N/A | First check whether the number is in text format and remove excess spaces, then apply the above method | When the formats are inconsistent, the data will not match even if it "looks the same" |
| The result column is part of the merge area | Put the returned data into columns with values in each row, or complete the same | The non-first cell in the merge area has no value, and the return result must be empty. |
First determine what kind of problem it is
The same "cannot be found" may have completely different reasons. First check the table below to find your symptoms.
| Symptom | Common reasons | How to confirm |
|---|---|---|
| Return # NCMA, but the name is clearly in the table. | The grid in which the search value is located is the non-first grid of the merge area, which is actually empty. | Click on the source cell to see if the edit bar is empty; if so, it is below the merge area |
| Return blank or 0 | The grid corresponding to the return column is part of the merge area, and the value is only in the first grid. | Find the source cell corresponding to the return column and see if it is empty |
| The result seems to be a wrong line, and the data do not match. | The fourth parameter is omitted or TRUE, and the data is not sorted by keywords. | Check the parameter at the end of the formula. If it is not FALSE or 0, it is an approximate match |
| The returned field is not what you want. | The column serial number is wrong according to the naked eye, and the position of the column is easy to be missed after the header is merged. | Use the COLUMN function or the number of direct series in the blank space to confirm against the table header. |
| The key words look the same, but they can't be found. | There is a space at the beginning and end, a number, a text and a number. | Use the LEN function to compare the lengths on both sides to see if there are more characters |
# item-by-item troubleshooting and repair
Check according to the following order, generally deal with the structural problem first, and then deal with the formula problem. This avoids changing the formula but does not solve the root cause.
- confirm that the lookup column is part of the merge area. Select the source cell to be found. If the edit bar is empty and there is a value above the same area, it is below the merge area, and VLOOKUP cannot read the value from it.
- when the original table can be changed, cancel the merger and then complete it. * check the entire datazone and cancel the merge. Then select all the blank spaces, enter the equal sign in the first blank box and point to the previous box, and press Ctrl+Enter to fill in all the blanks at once. In this way, each line has a complete keyword, and the formula does not need to be changed.
- when the original table cannot be changed, an auxiliary column is built. * in the blank column on the right side of the data area, write the formula
=IF(A2="",D1,A2)in the second row (assuming that column An is the key column of the merge, column D is the auxiliary column, and the first behavior header), fill it down. Column D is the keyword after completion. Point the search value of VLOOKUP to the area corresponding to column D. - change the first column of the search area to the completed keyword column. VLOOKUP can only start with the left column of the zone, so the secondary column must be on the left side of the return column. If the secondary column is on the right, you can use the combination of INDEX and MATCH instead, with the same effect.
- always use exact matching. the formula ends with
FALSEor0, such as=VLOOKUP(F2,$D$2:$H$200,3,FALSE). An approximate match returns "less than the last row of the lookup value" when the data is not sorted, which is a common source of wrong rows. - use MATCH to locate the return column, not the number of hands. write
MATCH("金额",$D$1:$H$1,0)in the formula and use it as the column serial number of VLOOKUP. When the header increases or decreases the column, the result automatically changes. - finally check whether the returned column has a null value. filter the returned column once to see if there is a blank space. If the return column itself is also part of the merge area, it will also be completed, otherwise the result will still be empty.
If the data comes from a table in PDF, you can export it to Excel using the PDF editor of the OmniDoc Desktop client (download.omnidoc.top), and then follow the steps above. Exported tables may also retain merged cells, so they still need to be checked after export.
# how to avoid the next occurrence
- the data source maintains "one record per row". if you need to display it in a merged format, make another typesetting table. Do not use the same table as both a database and a report. - keyword columns are not merged. grouping information can be written out in a separate column, such as writing the department name on each line, which is easier than not being able to find it after the merge, even if it seems to be duplicated. - take a look at the source table before searching. use the filter function or simply scroll to confirm that each row of the keyword column has a value. - formulas are written in a unified manner and match exactly. use FALSE as a fixed writing method to reduce line errors caused by approximate matching. - the search field is explained before the form is handed over to others. if the other person wants to use VLOOKUP to look up your table, tell him which column the keyword is in to prevent him from selecting the merge area to look it up.
# frequently asked questions (FAQ)
1. Merge cells retain only the first value. Why are the other cells empty after unmerging?
After unmerging, Excel will only retain the value of the upper-left corner, and the rest of the grid will go blank. This is a characteristic of merged cells, so completion must be completed by filling, rather than expecting the value to appear automatically after unmerging.
2. Wrap # Ngamma An in IFERROR, is that the right result?
Wrong. IFERROR only hides the error into white space or specified text, and does not make the result correct. If the search source itself lacks keywords, you should first complete the data, and then consider whether to use IFERROR to beautify the display.
3. It is the header that is merged. Will VLOOKUP still make mistakes?
Header merging generally does not affect the lookup, but it may make you miscount the serial number. The search area should start with the real data row, the header is only used to compare the field name, and it is recommended that you use MATCH to locate the column number.
4. There is not much data, is it faster to copy and paste manually?
When there is little data, it can be processed manually, but then the source table is updated, and the manual results will not change. With auxiliary columns or completed data tables, the formula can remain valid and is more suitable for tables that need to be used repeatedly.
5. Machine translation or OCR out of the form, direct search is reliable?
The forms identified by OCR often have typos, spaces, and merge mislocations. Please check the keywords and numbers item by item before searching, and check the important data manually.
# Summary
VLOOKUP cannot find the data, mostly because the lookup value falls in the space of the merged cell, or the return column is not aligned. First judge the symptoms, and then complete the keywords, fixed and accurate matching, the problem can usually be solved at once. Keeping one record at a time in the source table is the fundamental way to avoid repeated errors in the future.
