## 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.

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.

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.

## Sample File

## 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.

## Leave A Comment