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!