OmniDocWhat to do if #N/A appears in VLOOKUP: troubleshooting multiple results and incorrect results
Regarding the three types of problems such as #N/A appearing in VLOOKUP in Excel, incorrect return results, and multiple matching values found, the method of first determining the cause, then troubleshooting and repairing one by one is given, and how to retrieve all matching results.
Key points of this article
- When #N/A appears, it is most likely that the search value and the source data "look the same but are actually different". First, use functions such as LEN and TRIM to check for hidden spaces and number formats.
- VLOOKUP only returns the first matching item. When multiple matching values are found, use an auxiliary column to add a serial number, or use functions such as FILTER and TEXTJOIN instead.
- Result misalignment usually results from areas not being locked, column numbers written incorrectly, or fuzzy matching being used on unsorted data.
- The lookup value must be in the left column of the lookup area, otherwise VLOOKUP cannot look left, and INDEX plus MATCH or XLOOKUP should be used instead.
When using VLOOKUP to look up numbers in Excel, a common trouble is that a #N/A pops up in the cell, or another person's data is returned despite the name clearly being in the table. There is another situation that is even more confusing: the same name appears several times in the table, VLOOKUP only gives one result, and where are the other ones? These three types of problems look similar, but the causes and practices are different.
This article is written for readers who already know how to write basic VLOOKUP formulas, but don’t know where to look when they encounter errors or incorrect results. The introductory writing method of VLOOKUP will not be repeated here, only troubleshooting will be discussed. After reading, you can first determine what kind of problem it is, then check it in order, and if there are indeed multiple matches, take out all the results.
The following examples can all be reproduced in their own tables, such as a 30-row customer table, or a 12-page list of contract attachments. I suggest you try it out in your own files while reading.
If the formula reports an error or the result is incorrect, where should I look first? One sentence conclusion: Locate the symptoms first, and then start with the causes that are easy to check.
When you encounter a problem, don't rush to change all the parameters of the formula, first classify it according to the symptoms. #N/A usually means that no match was found, a misaligned result means that it was found but in the wrong column or row, and "several" is a design limitation of VLOOKUP itself. The form below can help you quickly find your seat.
| your situation | Recommended practices | reason |
|---|---|---|
| This name is clearly on the table, but it shows #N/A | First use LEN and TRIM to check for spaces between the search value and the source data, and then check the number and text formats | Many values that "look the same" actually contain spaces, invisible characters, or are formatted inconsistently |
| The returned data obviously comes from other rows or columns | Check the third parameter column sequence number and add an absolute reference to the search area | If the column number is wrong or the area is offset during copying, the result will be misaligned. |
| If the same name appears multiple times in the table, only one will be returned. | Use auxiliary columns to add serial numbers, or use FILTER or TEXTJOIN to obtain all results. | VLOOKUP is designed to only return the first match found from top to bottom |
| The search value is on the right side of the table and cannot be used with VLOOKUP | Use INDEX plus MATCH instead, or use XLOOKUP | VLOOKUP can only take values from the left column of the search range to the right |
| The results are sometimes right and sometimes wrong, and the data is not sorted. | Write FALSE in the fourth parameter, or sort the data first | The default is approximate matching when the fourth parameter is omitted, which will return unreliable results on unsorted data. |
First determine what kind of problem it is
The table below lists common symptoms, their corresponding causes, and how to identify them. If you are not sure, you can verify it line by line.
| symptoms | Common causes | How to confirm |
|---|---|---|
| Cell displays #N/A | The lookup value does not exist in the source data, or the format is inconsistent | Use LEN in a blank cell to calculate the length of the search value and source data respectively. Different lengths mean there are extra characters. |
| Displays #N/A, but the difference is not visible to the naked eye | Leading and trailing spaces, full-width and half-width differences, invisible characters | Use CLEAN and TRIM to process and then compare, or use the EXACT function to compare one by one. |
| I can see the numbers, but I can’t find them. | One side is numbers, the other side is text numbers | Use ISNUMBER to check both sides. Textual numbers will return FALSE. |
| The value returned is misplaced or appears to be random | Incorrect column sequence, region not locked, approximate match | Check the third and fourth parameters and see if there is a $ sign in the formula bar. |
| There are obviously multiple records, but only one is returned. | VLOOKUP only takes the first match | Use COUNTIF to count the number of times the search value appears. If it is greater than 1, it means there are multiple results. |
| The error is reported as #REF! | The column number exceeds the number of columns in the search area | Count the total number of columns in the search area and make sure the column number does not exceed this number. |
Check and repair item by item
When troubleshooting, it is recommended to proceed in order from easy to difficult. Most problems can be solved in the first two steps, and the following content is used to deal with more complex situations.
- Confirm that the fourth parameter is written as FALSE. Exact search must write FALSE or 0. When this parameter is omitted, Excel defaults to approximate matching, and the result may be wrong, or even return a similar value if it is not found. This step is easily overlooked, so check it first.
- Use LEN to compare lengths. Enter =LEN (find value) in a blank cell, and then do the same calculation for the corresponding cell in the source data column. Different lengths indicate extra spaces or invisible characters on one side.
- Clean up redundant characters. For data columns, you can use TRIM to remove leading and trailing spaces and extra middle spaces, and CLEAN to remove unprintable characters. After processing, it is recommended to paste the results as numerical values and then search to avoid confusion caused by mutual references between formulas.
- Unify digital format. Things that "look like numbers" such as job numbers and serial numbers are often stored in text form, but the search values are numbers, so the two cannot match. You can use VALUE or "--" to convert the auxiliary column, or use the column sorting function to convert the entire column into numbers.
- Check the column number and area. The third parameter indicates which column value in the search area is returned, starting from the left column of the area. If the column number exceeds the range width, #REF! is returned.
- Lock the search area. When the formula is copied downwards, if the search area does not add an absolute reference, the area will move accordingly, resulting in no values found in some rows. Write the area in the form "$A$2:$D$100", and then copy the formula.
- Handle multiple matches. First use =COUNTIF (search column, search value) to count the number of times. If the result is greater than 1, VLOOKUP will only return the first one. In this case, please see the next section.
When multiple matching values are found
VLOOKUP cannot directly return multiple results, so you need to think differently. There are three commonly used methods, and they are suitable for different scenarios.
Method 1: Add serial numbers to auxiliary columns. Insert a column next to the source data and enter the formula =A2&COUNTIF($A$2:A2,A2), so that the records with the same name become differentiating keys such as "Zhang San 1" and "Zhang San 2". When searching, enter "Zhang San 1" and "Zhang San 2" in the lookup table to retrieve each record separately. This method has good compatibility and can be used with older versions of Excel.
Method 2: FILTER removes all matching rows. If your version of Excel supports dynamic arrays, you can use =FILTER (result area, condition area = search value) to retrieve all rows that meet the conditions at once. The results will automatically overflow downwards, suitable for listing all orders from the same customer.
Method 3: Merge TEXTJOIN into one cell. If you only need to display multiple results together, you can use =TEXTJOIN(",", TRUE, IF(condition area=search value, result area, "")) and use commas to connect all matching items. This formula is available in newer versions, some older versions require input as an array.
When the result is misplaced or fetched from another row
There are usually only two reasons for misaligned results: the area is not locked, or the column sequence does not match expectations. If the table often adds or deletes rows, it is recommended to use full column references instead, such as A:D, or convert the data into a table so that the area automatically expands with the data. In addition, if the search value is on the right side of the result column, VLOOKUP cannot search to the left. In this case, it is more reliable to use the combination of INDEX plus MATCH.
How to avoid it happening again next time
Prevention is easier than repair afterward. The following few practices can help you reduce most problems.
- Unify the format before input. Fields such as number, date, and phone number should be fixed as text or numbers during entry, and do not mix them. When copying data from an external system or web page, paste it into Notepad first and then copy it back to remove some of the hidden formatting.
- Give the search area a name. Define the source data area as a name, such as "Customer Table", and directly reference the name in the formula, which not only avoids area offset, but also facilitates future maintenance.
- Create an identity column. If the same name may be repeated, try to add the number or date during the data entry stage so that each row has a distinguishable search key.
- Statistics before searching. Before doing the formal summary, use COUNTIF to check several key values to confirm that there are no unexpected duplicates, and then decide which search method to use.
- Keep a copy of the original data. Save another copy before cleaning up the format or changing the source data. When problems arise, you can check them instead of guessing back and forth in the modified table.
If your data has undergone text recognition or machine translation, such as importing from scans or foreign language tables, be sure to check key names and numbers, which are prone to deviations during the conversion process.
❓ Frequently Asked Questions (FAQ)
1. VLOOKUP returns #N/A, but I am sure this value exists in the source data. Why?
In most cases, there are invisible differences on both sides, such as leading and trailing spaces, full-width and half-width characters, or textual numbers on one side and numbers on the other. Use LEN to compare the length, and then use ISNUMBER to check the type, and you can usually find the reason.
2. There are several records with the same name. How can I retrieve them all at once?
If the version supports dynamic arrays, use the FILTER function directly. If the version is older, you can use an auxiliary column plus COUNTIF to generate a distinguishing key of "name plus serial number", and then search one by one.
3. Why is the result sometimes correct but wrong again when the data is changed?
A common reason is that by omitting the fourth parameter, Excel calculates by approximate matching, which returns unreliable results when the data is not sorted. By explicitly writing the fourth parameter as FALSE, most of these problems will disappear.
4. Can VLOOKUP search left?
cannot. VLOOKUP can only take values from the left column of the lookup range to the right. When you need to search to the left, you can use the combination of INDEX plus MATCH, or use XLOOKUP.
📝 Summary
#N/A appears in VLOOKUP, usually because there are hidden differences between the search value and the source data; the results are misaligned, most likely because there is a problem with the column number or range reference; if multiple values are found, an auxiliary column or a new function is needed to retrieve all results. Determine the symptoms first, and then troubleshoot them in order from easy to difficult. Most problems can be solved within a few minutes.
