Frequent problems with XLOOKUP/VLOOKUP/HLOOKUP functions and how to deal with them.(Microsoft Excel)
The VLOOKUP/HLOOKUP functions is a powerful function, but it has many arguments to set and many restrictions, making it difficult to find the cause when the function does not work as intended.
This section introduces common problems with the VLOOKUP/HLOOKUP functions and how to deal with them.
It also describes some of the changes that have been made to the XLOOKUP function.
Troubles and how to deal with them.
#N/A error
"lookup_value" does not exist.
#N/A error is an error when the search result does not exist.
If the search result really does not exist, it is inevitable and may be left as it is.
However, if the search result is incorporated into an aggregate function such as the SUM function, the aggregate function will also generate a #N/A error and must be addressed.
The XLOOKUP function has been improved to control this by using “if_not_found(Argument 4)”.
If you get a #N/A error even though it should exist in the “lookup_array(Argument 2)”, there are other causes.
Range specification is a relative reference.
This occurs when a function is cell-copied.
In the example below, the "table_array(argument 2)" is a relative reference (without "$"), so when copied, the range is shifted and does not match.
To cell-copy a function, append $ to the range (argument 2) to make it an absolute reference.
With the XLOOKUP function, Spill are also effective.
The column to be searched is not on the far left.
The VLOOKUP function requires the column to be searched to be placed on the left edge of the table. If it is not on the left edge, a special handling is required.
The XLOOKUP function changes the way columns are specified and eliminates this problem.
Spaces before and after.
If whitespace is mixed in before and after, the match will not occur.
This occurs more often than you might think, so if the VLOOKUP function does not work, check to see if there are any spaces mixed in.
It is also effective to create a new column with no spaces by using the SUBSITUTE or TRIM functions for the search value and the column to be searched, and use that column.
Different format.
The example below shows a failed VLOOKUP function comparing a numeric value of “100000” in display format with a comma-separated value of “100,000” as a string.
If one of the display formats is a number and one is a string, they will not match.
This is an example that is hard to notice because it looks the same.
Consider setting the display format to “standard” for columns used in the VLOOKUP function.
This can happen not only with numbers but also with dates, and it is possible that a few digits are cut off in the middle.
If this happens frequently, it is also useful to use the VALUE function to resolve format differences.
#REF error
REF is an error when a cell reference is invalid.
This may occur when the specified cell range is narrow or when a column operation is performed. Check the cell range and re-specify it.
Unintended Consequences
A zero that should not exist is displayed.
If the search result is blank, the result will be zero.
If there is a problem, it must be addressed in the formula.
There are multiple values matching the search value.
If there are multiple values matching the search value, the first (top) matching value is retrieved.
In this example, the value of "Category" must be changed or the formula must correspond to multiple results.
Argument 4 is omitted or TRUE.
If the search method (argument 4) is omitted or TRUE, an fuzzy search is performed.
Therefore, results are displayed even if there is no match.
In this case, the largest result below the search value will be displayed.
In the example below, 100 is specified as the search value, but “Kiwi”, the largest value of 9, is displayed because it does not exist.
You will probably use this fuzzy search infrequently.
Those who use the VLOOKUP function frequently should get into the habit of setting FALSE at the end.
The XLOOKUP function defaults to an exact match, which eliminates this problem.
---