Formulas that combine the INDEX and MATCH functions to perform searches better than the VLOOKUP function.(Microsoft Excel)

05/31/2024

Japanese version.

Although the VLOOKUP function is the standard way to perform searches in Excel, the INDEX and 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

ArgumentValue
Argument 1:lookup_valueLike argument 1 of the VLOOKUP function, the Specifies the value to be searched.る値を指定します。
Argument 2:lookup_arraySimilar to argument 2 of the VLOOKUP function, but
specifies only one column to be searched, not the entire table.
Argument 3:match_typeIt 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

ArgumentValue
Argument 1:arraySimilar 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_numSpecifies the MATCH function.
Argument 3:column_numOmitted.
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.

---

Links

Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)