Index and Match functions in Excel

The following article gives detailed instructions on how to use the Index and Match functions in Excel. Use these functions to help you find the exact value on any column and row.

1. Index () function

There are two types of Index () functions: Index () is an array and Index () is a reference form.

Index () in array form:

- Meaning: Returns the value of a data cell with the row and column index searched.

- Syntax: Index (Array, Row_num, [column_num]) .

Inside:

+ Array: Array of reference data or an array constant.

+ Row_num: Row contains the value to retrieve.

+ Column_num: Column containing the value to get.

Index () as reference:

- Meaning: Returns the value of a data cell with the row and column index searched.

- Syntax: INDEX (Reference, Row_num, [Column_num], [Area_num] .

Inside:

+ Reference: The reference area contains the value to get.

+ Row_num: The row index containing the value to retrieve.

+ Column_num: The column index containing the value to retrieve.

+ Area_num: Number of regions returned. If omitted the default is 1.

- For example:

Index and Match functions in Excel Picture 1

2. Match ()

- Meaning: Returns the number of characters in the data area of ​​the value to be searched.

- Structure: MATCH (Lookup_Value, Lookup_array, [Match_type]) .

Inside:

+ Lookup_Value: The value to search.

+ Lookup_array: Array contains the value to search.

+ Match_type: Search type. There are 3 types of searches:

* Search for values ​​smaller than the values ​​to search when match_type = 1 (les than).

* Search for the correct value by searching for value when match_type = 0 (Exact match).

* Search for value greater than the value to search when match_type = -1 (Greater than).

- For example:

Index and Match functions in Excel Picture 2

3. Summary example of using Index () and Match ()

For example, fill in the unit price of a product based on the product code and manufacturer in the lookup table.

Index and Match functions in Excel Picture 3

Note:

- MATCH (B3, A9: A12,0): returns the ordinal number of the value in cell B3 in the data range from A9 -> A12. In which the value of cell A9 is considered as the first number. => Returns the number of rows in the Index () function.

- MATCH (C3, A9: D9,0): returns the ordinal number of the value in cell C3 in the data cell from A9 -> D9. In which the value of cell A9 is considered as the ordinal number. => Returns the number of columns in the Index () function.

- Note: How to create a data table and select the search area of ​​the Match functions. Good luck!