HomeApplicationOffice information

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!

5 ★ | 1 Vote

You should read it

May be interested

  • Index (INDEX) in SQLIndex (INDEX) in SQL
    sql index (index) is a special lookup table that database search engines can use to quickly increase the time and performance of data retrieval.
  • How to use the MATCH function and combine the MATCH function with the INDEX functionHow to use the MATCH function and combine the MATCH function with the INDEX function
    guide you to add a great search function followed by the match function and how to combine the match function with the index function.
  • How to fix the SUM function doesn't add up in ExcelHow to fix the SUM function doesn't add up in Excel
    in the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
  • Complete financial functions in Excel you should knowComplete financial functions in Excel you should know
    fv, date, time, hour, workday, ... functions are the basic financial functions with the same way of getting color codes in excel. show you how to use these financial functions. click view now!
  • How to use the INDEX function in excel?How to use the INDEX function in excel?
    along with vlookup and hlookup, an extremely interesting search function that you should not ignore is the index function. how to use it? is it difficult or not?
  • 3 Functions to Help You Stop Wasting Time in Excel3 Functions to Help You Stop Wasting Time in Excel
    most people think that speed in excel means memorizing hundreds of formulas and functions. that's not the case until you discover that just 3 functions can replace the tons of formulas you used to struggle with.
  • How to Compare Two Excel FilesHow to Compare Two Excel Files
    this article focuses on how to directly compare information between two different excel files. once you get to manipulating and comparing the information, you might want to use look up, index, and match to help your analysis. open the...
  • How to Compare Two Excel FilesHow to Compare Two Excel Files
    this article will show you how to directly compare data between two excel files. after manipulating and comparing information, you might consider using look up, index, and match to aid in analysis.
  • Use Index in Access 2016Use Index in Access 2016
    an index is a pointer to each value that appears in the indexed table / column, which has the same meaning as the entries in the index of a book.
  • MS Excel - Lesson 5: Excel formulas and functionsMS Excel - Lesson 5: Excel formulas and functions
    the formula in excel is a program that performs calculations on data tables. these formulas perform very precise operations such as adding, multiplying, or comparing values ​​in worksheets.

Office information