Everything You Need to Know About the Match Function in Excel

✅ Updates⭐ 2023
✅ Number of letters998 words
✅ AuthorsVnhoi
✅ ConfirmationConfirmed
Post Reviews write

The Match function is often used to determine value of a row, column or table of data in Excel. This is a function that returns the relative position of the search value in an area and more. To learn more about Match function in Excel Please follow the following article.

Watch now Categories : Excel . Category includes 52 articles

Syntax of the Match . function

Syntax of the Match . function =Match(Lookup_value,Lookup_array,[Match_type]).

In which:

  • Lookup value: Is the value you want to search in the data list. This argument can be a number, text, value logic or cell references.
  • Lookup_array: This is the range of cells being searched.
  • Match_type: Help Excel know how to lookup data of Lookup_value with values ​​in Lookup_array. The default value of this argument is 1. The user has choices of -1, 0 or 1.
How to use the Match function in Excel

How to use the Match function in Excel

Usually there are three types of searches for the Match function:

  • If the Match_type argument is equal to 1 or omitted. In this case, the MATCH function will find the maximum value that is less than or equal to the Lookup_value argument. Lookup_array data will be sorted in ascending order.
  • If the Match_type argument is 0, it will find the exact value that is equal to the Lookup_value argument. Lookup_array data can be sorted in any order.
  • If the Match_type argument is -1, the MATCH function will find the smallest value that matches the condition that is less than or equal to the Lookup_value argument. Lookup_array data will be sorted in descending order.
Watch now Categories : Work skill . Category includes 208 articles

How to use the Match function in Excel

To better understand the usage of the Match function, you can refer to the following example:

Example of the Match . function

Example of the Match . function

With the above example, you will enter the formula =MATCH(E1,A2:A11,0).

As in the example above, the item code SGK07 is located at the relative position number 7 in the area A2:A11. So the Match se function result is 7.

Do Master excel through 100+ topics from basic to advanced - Dinh Hong Linh

9429 students
  • Understand all the basics of Excel spreadsheets, about custom operations, working with Sheet, keyboard shortcuts...
  • Understand the nature of cells (cells), the most important part of Excel, thereby helping you confident when using Excel
  • Understand the nature and proficiently apply data processing tools to spreadsheet formats (custom format, conditional formatting)
  • Proficient in using all functions in Excel, no longer have to worry about working with difficult functions application more work
  • Create reports, dynamic charts with Pivot Table, Slicer, Pivot Chart, get the first basic look at VBA in Excel
  • Confidence in Excel knowledge in the course to participate in the MOS Excel exam with high scores
  • Make a good impression on managers and leaders, thereby creating a great momentum on the way to career advancement
Islam found a course discount code.
Apply more code VNHOICOM to be down 40%.
Apply more code MAKM50 to be down 50%.
Go to the place that sells the course
Watch now Categories : Character . Category includes 34 articles

Some common errors when using the Match . function

Error #NA when using the Match function in Excel

  • In case the MATCH function does not find a value in the detection range, it returns the value #N/A.
  • When you use a range in INDEX, MATCH instead of a value, you need to press Ctrl+Shift+Enter to switch back to the array formula.
  • When you use MATCH, there should be consistency between the value in the match_type argument and the sort order of the values ​​in the lookup range, or you will get a #N/A value.

#VALUE error

If you want to use it as an array formula to search for results, you need to convert the formula. You can press Ctrl+Shift+Enter otherwise you will see the #VALUE! appear.

Above are the share on how to use and fix some common errors of jaw Match. Hope you will be more proficient in using the Match function.

Frequently asked questions

Syntax of the function Match =Match(Lookup_value,Lookup_array,[Match_type]).
#NA error when using the Match function in Excel #VALUE error
Translate »