The VLOOKUP function is a function that retrieves cell values and has many uses.
However, newer versions of Excel have added a higher-level version, the XLOOKUP function.
XLOOKUP function also serves as the HLOOKUP function and improves many of the inconveniences of the VLOOKUP function.
It is recommended that you switch to the XLOOKUP function if you have an available version of Excel.
How it works
=VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
| Name | Omission | Explanation |
|---|---|---|
| lookup_value | Required argument. Value to be searched. | |
| table_array | Required argument. Cell range to search. The column to look for the search value must be the leftmost column, and include the column from which to retrieve the results. | |
| col_index_num | Required argument. Specifies the column in which to display the search results."Sequential number of table_array with the leftmost column as 1. | |
| range_lookup | TRUE | FALSE: Obtain an exact match. TRUE: Get the largest match under the search key. |
Demonstrate
The most common usage is to match the ledger with a code, etc., to retrieve and display information such as names and prices.
This can eliminate posting errors.
In the example below, the code in column F is retrieved from column B, and the corresponding product name and price are automatically displayed.
Argument 1: lookup_value
In the example, we specify the Code in column F.
Given the use of auto-fill and copy & paste
it is safer to fix the specification of column F with an absolute cell reference.
Comparison operators are not allowed here. Specify the string you want to match.
Forward, partial, and backward matches are also possible, but since the VLOOKUP function itself searches a single cell, it is not suitable for displaying multiple search results.
Use a combination of other functions or use the FILTER function.
Argument 2: table_array
Note that the column to look for the search value must always be the leftmost column. If you want to search for a column other than the leftmost column, you must be creative.
If you want to search for Code as in the example, the leftmost column of the range must be Code.
The XLOOKUP function improves on this point by allowing the search to be performed even if the left-most column is not the left-most column.
If you are copying formulas to multiple cells, it is safer to fix the formulas to named ranges or columns by absolute cell reference only, rather than by cell specification.
Argument 3: col_index_num
The items to be retrieved as a result.
In this example, 1 displays the code, 2 the product name, and 3 the price.
In the XLOOKUP function, it is a cell range, not a number.
Absolute cell reference are also possible, making it more convenient.
In addition, images can now be used in the search results.
Argument 4: range_lookup
In most cases, FALSE is specified for an exact match.
If TRUE is specified, the E of P-005 will be retrieved when P-006, which does not exist, is specified.
If FALSE is specified, there are no results for P-006.
When omitted, the result is TRUE, which is inconvenient for beginners. However, in the XLOOKUP function, FALSE is used as the omitted value, which is a great improvement.
If there is no match.
If the search result does not exist, it will result in a #N/A error, which will adversely affect other expressions.
If it is only a display problem, there is no serious impact even if it is left as it is.
However, it is not desirable to have the error remain in the documentation, so we recommend that it be addressed.
In the XLOOKUP function, this can be handled with an argument.
Spill
If you specify the argument "lookup_value" the cell range, it will be Spil.
---