In Excel’s vast library of functions, it can be said that the Match function is one of the most superior functions but is the least noticed. The main reason why users are indifferent to it is that they can’t really define clearly what the purpose of using this function is for. If not placed in a specific context, you will not be able to realize its usefulness and convenience.
In this article, Learn Excel Online will show you how to use Match function in Excel to be effective.
The main purpose when using the Match function is to: Return the position of any value in the set
The Match function formula has the following form:
= MATCH ( value to reference, set to be referenced, [điều kiện đối chiếu] )
Suppose we have a data table of 5 different numbers as shown below and the task is to find the position of the number 25 in this set.
By using the Match function, we can do that by specifying the following k elements:
Value to be referenced: 25
Collection to be referenced: (specify data sheet above)
Match Type: 0 (meaning that we ask Excel to only search for the absolute exact match of the number 25, not the relative value)
Based on the above parameters, Excel will return the result as 3, because the number 25 is in cell 3 in the sequence of numbers. (The above function formula can infer the position of the numeric cells on its own, so you don’t need to mark cells 1 through 5)
It can be seen that the working mechanism of the Match function is quite simple. The question, however, is whether this is a practical application of this function. How often do you search for the position of any value in the set? It’s probably rare for this task to come through.
Here are 3 typical examples showing the most appropriate use of the Match function. Put in each specific case, we can see the real use of this function.
Mixed Formula
Currently one of the most effective uses of the Match function is through mixed formulas. That is, we will use the Match function along with other basic functions in Excel. For mixed reference formula types, you should always leave the absolute reference condition in the match_type section of the Match function.
When combining the Match function with a reference formula, referencing becomes more convenient. For example, when you combine the Match function and the Vlookup function, the Match function will now replace the column_reference conditional element (the column number to reference) in the Vlookup function. Instead of having to fill this entry with a certain number of characters, like the number 3 for example, you can replace it with the Match function to determine the exact column name you want to reference with the Vlookup function, based only on the name. of the column instead of the sequence number.
Combining the Match function also helps you avoid the error of inserting columns during the reference process, if you want to insert any column in the data table.
One of the most effective ways to combine the Match function is through the Index Match function. Most users are used to using this function without even knowing how the Match function is used, they simply memorize the syntactic structure of the function. Even so, it also shows the great use of the Match function in combination with other mixed reference functions.
Below is a list of the most commonly used reference formulas that include the Match function. The list includes both vertically referenced, horizontally referenced, (INDEX MATCH can be horizontally referenced) and 2D matrix reference functions.
Check if the value exists in the original data
One of the other ways to take advantage of the strength of the Match function is to check if a certain value exists. To do this you need to use the absolute exact match condition in the match_type part of the function.
In the example below, we have 5 reference values to check if they belong to the original data set or not. The job is simply to use the Match function for each value one by one.
Reference value: (select the desired reference value)
Reference set: (select data collection table)
Comparative conditions: 0 (i.e. Excel rules only absolute precision values)
If the return value is an ordinal number, it means that the value you are interested in exists in the original data set. If the result returns an error, it means that the above value does not exist.
Visually speaking, this is the best use of the Match function. If some value simply exists in the given data set, then of course the Match function must make sense. Using the Match function to test helps you get the most intuitive and fast results.
In fact, there are other ways to verify the existence of a certain value. You can use Ctrl+F to launch the search function in Excel. Or you can also sort and filter the data to find values manually. However, the fastest and most efficient solution is probably to use the Match function to check, especially when there is more than 1 value to verify, like the example above. Just write the formula once, fully reference it and copy it down the next lines and you’re done.
Alternative solution for nested IF function
In both of the above examples of the IF function, we used the absolute exact value matching condition. Most of the time, when using the Match function, up to 95% of users only choose to search for the exact value. However, you can also apply this function to the case where the matching condition is relative precision values. One of them is that you can use it to avoid having to write nested IF statements that often have quite complex layouts. Here is a list of matching conditions that you should know about:
| Type of collation condition | Excel Tasks | Data classification conditions in the set |
| “1” or leave it blank | Finds the largest value in a reference set whose value is less than or equal to the reference value | In ascending order |
| “0” | Finds the first value that appears in the reference set whose primary value is equal to the reference value | Do not have |
| “-first” | Finds the smallest value in a reference set whose value is greater than or equal to the reference value | In descending order |
This is an example that shows a reference condition that explicitly states that Excel should only look for relative values. For example, the keywords highlighted below will show the relativity of the search results:
Find greatest value in rally value less than or equal to reference value.
Suppose we assign a reference value to the set of bins of histogram histogram (by default the reference set is the smallest value among the bins of the histogram), then we should use the relative reference. Below we have an example that assigns the numerical value 36 to to the set of bins of the histogram histogram.
The reason why the Match function is superior to the nested If function is because:
- Shorter in length and simpler than the nested If function
- Easier to test and debug
- Using the Match function makes it possible to apply to larger data scales; for example, if you want to increase the number of bins in the histogram, the structure of the formula will not be stretched any longer.
To be able to apply Excel well in work, we not only have to master the functions but also have to use Excel’s tools well. Advanced functions help apply well to work such as SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Commonly used tools are Data validation, Conditional formatting, Pivot table…
All these knowledge you can learn in the EX101 – Excel course from basic to expert of Learn Excel Online. Currently, the system has a great discount for you when you register for the course.
Nguồn : Blog hocexcel