The VLOOKUP function is a very useful function for handling data, but many of you may have experienced that once you get stuck in the #N/A or #REF! error swamp, it is difficult to get out. In this article, we will introduce some causes of #N/A and #REF!
The "#N/A" search value is not an exact match.
The VLOOKUP function can only extract data if the search value is an exact match. It may be difficult to visually check whether the match is exact or not if the string is a little long, or if there is a name or a space after a character.
For example, the pattern is shown in the figure below. The table on the right is a customer list, and the data here is picked up using the customer name in the table on the left as the search value.
As you can see from the numbers, most of them are "#N/A", but at a quick glance I can't tell what the difference is. It looks like an error even though it is indeed there.
The answer to the data error here is
- January 1: Half-width space after "Hachioji" in the sales ledger.
- January 2: The space in front of the branch name is not a full-width space but three half-width spaces.
- January 3: Apostrophe (') in sales ledger is half-width.
- January 5: The character "齋" in Saito is "齋".
It is.
Most of these are not determined by appearance alone. So the function is not wrong and the data is indeed there, but it looks like an error.
How to check
This can actually be found by doing a search.
As shown in the figure below, select the cell with the search value you wish to check and copy it by pressing "[Ctrl]+C".
![[Ctrl]+C](https://rebizru.com/wp-content/uploads/2024/07/vlookupna9.png)
Select the column of customer names in the customer list and press "[Ctrl]+F" + "[Ctrl]+V".
The search box will now contain the search text.
In this state, press the "Search Next" button.
If you see a message like the one below, it means that there are no exact match cells, so there are slight differences that are not apparent.
In this case, copy the customer name from the customer list and paste it into the corresponding named cell in the sales ledger.
Then the "#N/A" error could be erased as shown in the figure below.
counter-measure
- Use input rules so that names that do not exist in the master cannot be entered. To use the input rule, click on theCreating a pull-down (drop-down) Excel (Excel)You can also refer to the following for setting up the system.
- Use codes such as sequential numbers, which are less likely to cause distortion of notation, as search values.
The "#N/A" function copy is out of range.
In many cases, this is caused by copying a function that is a relative reference, as shown below.
=VLOOKUP(B3,F3:H9,2,0)How to check
Looking at the function in the figure below, it is a relative reference without the "$" mark.
If you copy the function down to the bottom in this state
You can see in the figure below that the part referring to the customer list has shifted from the table.
The result is displayed for the numbers that happen to be within the reference range even though they are out of range, but the number "1002" that is out of the reference range is "#N/A".
counter-measure
Now let's add a "$" mark to the reference range to make it an absolute reference, as shown below.
=VLOOKUP(B3,.$F$3:$H$9,2,0)
If we copy the function down to the bottom in this state
We were able to correct the error in this way.
Exceeds the "#N/A" line count range.
First, let's look at the error situation here.
How to check
It's hard to tell just by looking at it, so I double-clicked on the "C3" cell to display the formula,
As shown in the figure below, the range selection is only selected up to the middle of the customer list, and the customer number "1005" is out of range.
counter-measure
In this case, you can extend the range selection to the end of the table.
This fixes the error.
Then copy the modified function to the line below.
The "#N/A" search value is not at the left end of the range.
How to check
This time, I wrote a function that pulls the customer name using the customer code as the key.
However, this all resulted in a "#N/A" error.
This is a property of the VLOOKUP function: the search value in the database must always be in the leftmost column of the range. In the example above, the customer code is in the middle and the customer name is in the left column.
This has resulted in errors in all cells.
counter-measure
The customer list has been rearranged and corrected so that the customer code is on the leftmost side.
I was then able to eliminate all "#N/A" errors.
The "#REF!" column number exceeds the range.
How to check
See the figure below. All lines show a "#REF!" error.
Let's look at the formula here.
The function shows that the range is specified only up to the second column, but the column number specifies "column 3".
You can see that the "#REF!" error occurred because you specified "column 3" when there is only up to column 2.
counter-measure
The solution to this pattern is to extend the range to the "third column". Then copy the modified function to the bottom.
This fixes the "#REF!" error.
Conclusion
If an error occurs, there are many possible causes. Please try each of the methods introduced so far and see if there is a corresponding cause.
I hope this article will be of help to you.
Comment