Although the VLOOKUP function is the standard way to perform searches in Excel, the INDEX & MATCH functions are superior in many ways.
In this article, we will show you how to use a combination of the INDEX and MATCH functions instead of the VLOOKUP function This article will show you how to perform a search using a combination of the INDEX and MATCH functions instead of the VLOOKUP function.
However, the XLOOKUP function was added in January 2020.
This function is superior to the combination of the INDEX and MATCH functions, and its use is recommended in environments where the XLOOKUP function is available.
Enter the following formula.
|Like argument 1 of the VLOOKUP function, the Specifies the value to be searched.る値を指定します。
|Similar to argument 2 of the VLOOKUP function, but
specifies only one column to be searched, not the entire table.
|It is similar to argument 4 of the VLOOKUP function.
Specify an exact match of 0 here.
If this is omitted, the result will be
1, and an ambiguous search will be performed.
This may result in unintended results.
So, make sure to specify 0.
|Similar to argument 3 in the VLOOKUP function, but different.
Specify only one column that will be the display item, not the column number.
|Specifies the MATCH function.
If specified, specify 0.
Comparison with VLOOKUP Function.
- If you want to copy formulas horizontally.
- The display item does not have to be to the right of the search range.
- The result is a reference, not a value.
If you want to copy formulas horizontally.
The difference occurs when formulas are copied or auto-filled.
The column number specified in the VLOOKUP function does not change when copied horizontally, but
INDEX and MATCH functions, unless $ is appended to them.
They move one column at a time.
This is a big advantage in terms of efficiency and fewer errors if you want to display multiple columns.
To do the same thing with the VLOOKUP function requires some ingenuity.
This is an improvement over the INDEX and MATCH functions.
The display item does not have to be to the right of the search range.
One drawback of the VLOOKUP function is that the column corresponding to the search value must be the leftmost column in the range of argument 2.
However, the INDEX and MATCH function methods do not have this restriction.
The result is a reference, not a value.
The result of the INDEX function is a cell reference, while the result of the VLOOKUP function is a value.
Cell references are superior.
The difference is that if the result is a cell reference, you can use the result of the function as an argument to the OFFSET function to shift the cell, or use the result of the
The XLOOKUP function, however, also uses the result as a cell reference, so there is no difference anymore.