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
- How to use the Match function in Excel
- How to combine Index and Match functions in Excel
- 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
- MIN function in SQL Server
- MAX function in SQL Server
Maybe you are interested
Match function in Excel: How to use the Match function with examples
Instructions to fix Excel files with format and extension don't match errors
Bug fix: Voice match cannot be enabled
11 best free word matching games on Android and iPhone to improve English vocabulary
Apple Watch can change color to match the clothes you wear in the future
The curse of Kevin de Bruyne in decisive matches