MATCH Function in Excel


Home >> How to Use Excel Tutorial >> Excel Formulas List >> MATCH Function in Excel



In this tutorial, you will learn about excel MATCH formula. MATCH function in excel can help you finding the column or row position number of your data. Usually, this formula is combined with another particular formula, which is INDEX, to find data from a cell range in this spreadsheet software.


Why We Need to Learn About Excel MATCH Formula?


If we have a table or cell range in spreadsheet software, especially if it has many rows or columns, then it will be pretty hard to find the specific location of one data. And, probably we need this location of the data in one of the column or row so we can get further information of the data to do the data processing that we need.

The simple example of this need is when we want to know the row of one name variable on the name column in this spreadsheet software. It is pretty hard to find it if our data entries consist of more than a hundred rows. It can be a shame as this knowledge is needed to see other indicators for the particular entry in the other columns. The same thing can also be felt when we want to find a column position of a data from more than a hundred columns in a table.

To make our search process easier, then there is MATCH function in excel to help you do that process. This excel MATCH formula is crucial if we often look for data location in the spreadsheet, especially if there are many filled cells that need to be looked at one by one for the search process.


What is MATCH Function in Excel?


MATCH function in excel is a function with the usage objective to find the row or column position of a data that we want to find in this spreadsheet software. Excel MATCH formula will give result in the form of a number which represents the row or column position of the data you input into it. If you have learned this MATCH function in excel, then it is better that you understand its partner formula, INDEX, also so you can optimize more of their usage in the data search process.

A bit explanation about the inputs in it is as follows:


=MATCH(lookup_value, lookup_array, match_type)


Note:
  • lookup_value = the value to be found in our row/column
  • lookup_array = cell range of the row/column where the lookup process will occur
  • match_type = optional. The lookup process type that you want to do, whether it is exact, exact or larger, or exact or smaller



How to Use Excel MATCH Formula?


The following will explain how to write MATCH function in excel step by step. Excel MATCH formula needs the inputs of lookup value, the cell range where the search process will occur, and the nature of the process, whether you want an exact equivalent or not. Please remember that cell range input can only be inputted with one row/column. If there is more than one for the row and column, then this MATCH function in excel will result in an error.


Using MATCH Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result

    MATCH Function in Excel - Screenshot of Step 1

  2. Type MATCH (can be with large and small letters) and open bracket sign after =

    MATCH Function in Excel - Screenshot of Step 2

  3. Type data or cell coordinate where the data that you want to find is after open bracket sign then type a comma sign ( , )

    MATCH Function in Excel - Screenshot of Step 3

  4. Drag cursor from the most top-left cell coordinate to the most bottom right for the cell range of the row/column where you want the search process to happen

    MATCH Function in Excel - Screenshot of Step 4

  5. Optional: Type comma sign. After that, you can input the nature of the search process option here with:
    • -1 if you want to find exact or the next larger value for your search process. Your row/column must be sorted from large to small first to make the formula work well
    • 0 if you want an exact value. Your row/column does not need to be sorted if you choose this option
    • 1 if you want to find exact or the next smaller value for your search process. Your row/column must be sorted from small to large first to make the formula work well


    MATCH Function in Excel - Screenshot of Step 5

    If there is no input here, then the option which is chosen will be 1
  6. Type close bracket sign

    MATCH Function in Excel - Screenshot of Step 6

  7. Press Enter
  8. The process of excel MATCH formula is done!
    • Search Result Example of Option 0

      MATCH Function in Excel - Screenshot of Step 8-1

    • Search Result Example of Option -1

      MATCH Function in Excel - Screenshot of Step 8-2

      MATCH Function in Excel - Screenshot of Step 8-3

    • Search Result Example of Option 1

      MATCH Function in Excel - Screenshot of Step 8-4

      MATCH Function in Excel - Screenshot of Step 8-5



Exercise


After you have learned the steps to use excel MATCH formula in the tutorial, you can practice your understanding to be more adept by working on the exercise below!

Download the file from the following link and answer the questions. Please download the answer if you have done the answering process and sure about the results that you get!

Link to download MATCH function in excel exercise file:
Download here

Questions

  1. What is the position of number 177 in column B? If you cannot find the exact value, find the next larger value position!
  2. What is the position of number 634 in column C? If you cannot find the exact value, find the next smaller value position!
  3. What is the position of number 500 in column D? If you cannot find the exact value, find the next smaller value position!

Link to download answer:
Download here


Additional Notes


  • The process in excel MATCH formula is not case sensitive
  • MATCH function in excel results in an error if it cannot find the lookup value in the cell range you input
  • If there is more than one value which are fit with the lookup value, then the result of this formula is the first data row/column position which fits the lookup value
  • Lookup value is limited to 225 characters max
  • Excel MATCH formula is often used with INDEX to optimize its usefulness (because we more often search a data than its row/column position). If you want to learn too about how to use INDEX formula or the combination between the two, then you can learn them in other tutorials of this Compute Expert blog!




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert