How to use the MATCH function and combine the MATCH function with the INDEX function
In previous articles, TipsMake introduced you how to use INDEX, VLOOKUP, HLOOKUP and has an appointment with you that will guide you to add the super cool search function, followed by MATCH and how to combine functions. MATCH with the INDEX function. And do not let you wait any longer, let's find out through the article below
1. How to use the MATCH function in excel
The MATCH function in excel is used to search for a given number in a list
a) The syntax of the MATCH function
MATCH (Lookup_value, Lookup_array, [Match_type])
Inside:
- Lookup_value: Search value, can be a numeric value, text, logical value or a cell reference to a number, text or logical value, required
- Lookup_array: Array to search, required
- Match_type: Search type.Optional.
There are 3 types of search:
+ 1: Less than (Less than the search value)
+ 0: Exact match (Exact match value)
+ 1: Greater than (greater than the search value)
When omitting nothing, the default MATCH function is 1
b) Illustrative example
Give the following list of students.Find the student serial number named Nguyễn Hà
Step 1:
In cell F18, you will enter the formula according to the above syntax, and then we press Enter to perform the MATCH function
F18 = MATCH ('Nguyen Ha', $ E $ 5: $ E $ 14,0)
Step 2:
Soon we will be returning the value of position F18 as the number '5'
2. How to combine INDEX and MATCH functions
People often use the MATCH and INDEX functions to find a certain value.Because index searches only when it knows the row and column addresses in the worksheet area.
Therefore match acts as a search for the position of the row and column of the value to be searched.
Here, to better understand how to combine, let's find out through the example below:
Give 2 tables as examples in the image.Please fill in the ticket price corresponding to the route code and transportation
Step 1: In cell F4, you will enter the formula with the syntax = INDEX ($ C $ 15: $ E $ 18, MATCH (D4, $ B $ 15: $ B $ 18,0), MATCH (E4, $ C $ 14: $ E $ 14,0)) and then we press Enter to execute the MATCH function
Step 2:
Soon we will be returned to the F4 position value of 500000
We then drag the formula to complete the worksheet and get the following result table
The jaw looks very long but actually it's not that hard right?We just need to understand the nature and usage of functions to be flexible and combined to handle difficult excel exercises.Good luck!
You should read it
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
- Match function in Excel - Usage and illustrative examples
- How to use the INDEX function in excel?
- The Match function (the function searches for a specified value in an array or cell range) in Excel
- Index function in Excel
- Save time with these text formatting functions in Microsoft Excel
- Function in programming C
- DAY function in SQL Server
May be interested
- Match function in Excel: How to use the Match function with examplesthe match function in excel has many practical applications. below are details on how to use the match excel function.
- Use Index in Access 2016an 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.
- How to combine IF, AND and OR functions to filter datahow to combine if, and and or functions to filter data if you only use the if function, the and function and the or function, you cannot see the full usefulness of these functions in excel. the following article will combine the if function, the and function and the or function to make jars
- How to write the above index, below index in Excelthis article will guide you to some quick ways to write the above index and lower index in excel for both text and numeric values.
- How to combine Vlookup function with Left functionwhen combining the vlookup function with the left function, we will easily separate the values to find the right information we need.
- How to combine Vlookup function with If function in Excelwhen combining vlookup function with if function in excel, we can set conditions to search for values by column.
- The Match function (the function searches for a specified value in an array or cell range) in Excelthe match function helps you search for a specified value in an array, a range of cells, and then returns the position of the value in that array or range.
- How to use the FIND function in Excel?the find function in excel is a function that finds characters in a text string, having the same purpose as string separators like the mid function in excel or the left function or the right function.
- Instructions for indexing on the index on the index in Wordguidelines for indexing upper and lower numbers in word to write chemical formulas or first-order equations, quadratic equations, etc.
- 10 T-SQL Index statements needed with DBAsql server dba (database administrator) people - database administrators know very well that index entries in the database are very similar to index in the library section. or simply understand that index in database is a structure that is closely linked to tables to quickly gather information from the rows in that table.