What is EXCEL MATCH FUNCTION
The Excel MATCH Function is listed under Microsoft Excel's Lookup Functions category. It returns the index number of the value from an array. In simple words, the MATCH function looks up for a value in the list and returns the position number of that value in the list.
How to use it
To learn how to use the MATCH function in Excel, you need to understand its syntax and arguments:
MATCH (lookup_value, lookup_array, [match_type])
- lookup_value: The value whose position you want to get from a list of values.
- lookup_array: The range of cell or an array contains values.
- [match_type]: The number (-1, 0 & 1) to specify how excel look for the value from the list of values.
- If you use 1, it will return the largest value which is equal or less than the lookup value. The values in the list must be sorted in ascending order.
- If you use -1, it will return the smallest value which is equal or greater than the lookup value. The values in the list must be sorted in ascending order.
- If you use 0, it will return the exact match from the list.
To master the MATCH function we need to try it out in an example, so make sure to check out the below one:
In the below example, we have used 1 as match type and we are looking for value 5.
As I have already mentioned if you use 1 in match type it returns the largest value which is equal or smaller than the lookup value. In the entire list, there are 3 values that are smaller than 5 and 4 is the highest in them.
That's why in the result it has returned 3 which is the position of value 4.
This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:
- EXCEL ADDRESS Function
- EXCEL AREAS Function
- EXCEL CHOOSE Function
- EXCEL COLUMN Function
- EXCEL COLUMNS Function
- EXCEL FORMULATEXT Function
- EXCEL HLOOKUP Function
- EXCEL HYPERLINK Function
- EXCEL INDEX Function
- EXCEL INDIRECT Function
- EXCEL LOOKUP Function
- EXCEL OFFSET Function
- EXCEL ROW Function
- EXCEL ROWS Function
- EXCEL TRANSPOSE Function
- EXCEL VLOOKUP Function
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.