Formulas that combine the INDEX and MATCH functions to perform searches better than the VLOOKUP function.(Microsoft Excel)
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.
Formula
Enter the following formula.
=INDEX(displayCellRange,MATCH(searchValue,searchCellRange,0))
Argument Explanation
MATCH function
| Argument | Value |
|---|---|
| Argument 1:lookup_value | Like argument 1 of the VLOOKUP function, the Specifies the value to be searched.る値を指定します。 |
| Argument 2:lookup_array | Similar to argument 2 of the VLOOKUP function, but specifies only one column to be searched, not the entire table. |
| Argument 3:match_type | 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. |
INDEX function
| Argument | Value |
|---|---|
| Argument 1:array | Similar to argument 3 in the VLOOKUP function, but different. Specify only one column that will be the display item, not the column number. |
| Argument 2:row_num | Specifies the MATCH function. |
| Argument 3:column_num | Omitted. If specified, specify 0. |
Comparison with VLOOKUP Function.
Strengths
- 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.
The XLOOKUP function, however, specifies a range of cells instead of column numbers, so the XLOOKUP function is more effective than the INDEX and MATCH functions.
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 VLOOKUP function requires some ingenuity to do the same thing.
However, the XLOOKUP function improves on this point and is more effective than the INDEX and MATCH functions.
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.
---