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.

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.