How to use Excel MATCH Function

    how to use excel match function

    Quick Intro

    Use Excel MATCH Function to get the position of a cell from a range or an array. In simple words, you can get the position of a lookup value from a list of values.

    Syntax

    MATCH (lookup_value, lookup_array, [match_type])

    • lookup_value A value whose position you want to get from a list of values.
    • lookup_array A range of cell or an array contain values.
    • [match_type] A number (-1, 0 & 1) to specify how excel look for the value from the list of values.

    More Information on MATCH Function

    • You can use wildcard characters with match function.
    • If there is no matching value in the list if will return #N/A. You can use IFNA Function and IFERROR Function to show a meaningful message.
    • Match function is non-case sensitive.
    • You can use match_type as per below information.
      • 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.

    Examples

    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 will return the largest value which is equal or smaller than lookup value.

    Now, in the entire list, there are 3 values which are smaller than 5 and 4 is the highest in them. And, the result is 3 which is the position of value 4.

    Positive Match Value In Excel MATCH Function

    In the below example, we have used 0 as match type and we are for value 2. When you use 0 in match type it will perform an exact match. 2 is in the 3rd position in the list that is why the result is 3.

    Zero Match Value In Excel MATCH Function

    In the below example, I have used -1 as match type and I am looking for value 4. As I have already mentioned if you use -1 in the match_type it will return the smallest value which is equal or greater than lookup value.

    Now, in the entire list, there are 2 values which are larger than 4 and 5 is the lowest in them. And the result is 2 which is the position of value 5.

    Negative Match Value In Excel MATCH Function

     

    Sample File

    download sample file to learn more about this tips

    What’s Next?

    To learn more about Excel MATCH Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

    There is also you have CHOOSE function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.