VLOOKUP function. Searching for data.(Microsoft Excel)

06/02/2023

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)
NameOmissionExplanation
lookup_valueRequired argument.
Value to be searched.
table_arrayRequired 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_numRequired argument.
Specifies the column in which to display the search results."Sequential number of table_array with the leftmost column as 1.
range_lookup​TRUEFALSE: 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.

---

Links

Microsoft Excel Functions Lookup & Reference

Examples of Use

How to display Hit if the item exists and Miss if it does not exist using the VLOOKUP function.