Find data location with MATCH function in Excel

Content
  1. Find the position of a specific value in your dataset
  2. Syntax and arguments of the MATCH function
  3. Use the MATCH function in Excel
  4. Combine MATCH with other Excel functions

Find the position of a specific value in your dataset

The Excel MATCH function is used to return a number indicating the first relative position of data in a list, matrix, or selected range of cells. Used when the element’s place in the list is required instead of the element itself.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

Syntax and arguments of the MATCH function

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments. Syntax for the MATCH function:

 = ПОИСКПОЗ ( искомое_значение ,  просматриваемый_массив ,  тип_сопоставления ) 

lookup value (required) is the value you want to find in the data list. This argument can be a number, text, boolean, or cell reference.

lookup_array (required) is the range of cells to search.

match_type (optional) tells Excel how to map the Lookup_value to the values ​​in the Lookup_array. The default value for this argument is 1. The options are -1, 0, or 1.

  • As Match_type is 1 or absent, MATCH finds the largest value that is less than or equal to Lookup_value. Lookup_array data must be sorted in ascending order.

  • If Match_type is 0, MATCH finds the first value that is exactly equal to Lookup_value. Lookup_array data can be sorted in any order.

  • If Match_type is -1, MATCH finds the smallest value greater than or equal to Lookup_value. Lookup_array data must be sorted in descending order.

Use the MATCH function in Excel

The MATCH example shown in this tutorial uses a function to find the position of a term gadgets in the inventory list. Function syntax can be entered into a cell manually, or by using Excel’s built-in functions, as shown here.

To enter the MATCH function and arguments:

  1. Open a blank Excel sheet and enter data in columns C, D, and E as shown in the image below. Leave cell D2 blank as this particular cell will contain the function

  2. Select cell D2 to make it active.

  3. Open a tab formulas on the menu ribbon

  4. Select Search and help to open the drop-down list of functions.

  5. Select AGREEMENT in the list to open the Function Arguments dialog box. Excel for Mac opens the Formula Builder.

  6. Place cursor in text field lookup value

  7. Select cell C2 on the sheet to enter a cell reference.

  8. Place cursor in text field lookup_array

  9. highlight cells E2 before E7 on the sheet to enter a range.

  10. Place cursor in text field match_type

  11. Enter the number 0 in that row to find an exact match to the data in cell D3.

  12. Select Okay to end the function. Except for Excel for Mac where you choose Finished

  13. Number 5 appears in cell D3 because Gizmos is the fifth item from the bottom of the inventory list.

When cell D3 is selected, the entire function is displayed in the formula bar above the worksheet.

 = ПОИСКПОЗ (С2, Е2: E7,0) 

Combine MATCH with other Excel functions

The MATCH function is usually used in conjunction with other lookup functions such as VLOOKUP or INDEX and is used as input to other function arguments, such as:

  • The col_index_num argument for VLOOKUP.

  • The row_num argument to the INDEX function.

Leave a Reply

Your email address will not be published.