## MATCH function

**Table of Contents**

- Overview - Match function
- Lookup_array in ascending order
- Lookup_array in any order
- Lookup_array in descending order
- Array formula
- Using wildcard character
- VBA Example
- Download excel *.xlsx file

#### Quick example

The formula in cell C3 searches for "A" in cell range C6:C10 and finds it in cell C8. The relative position of C8 in cell range C6:C10 is 3, see relative positions in cell range B6:B10 on picture below.

The formula returns 3 in cell C3, remember to use 0 (zero) in the third argument if you want to find an exact match.

#### Overview

**MATCH(***lookup_value*, *lookup_array*, *[match_type]*)

*Returns the relative position of an item in an array that matches a specified value in a specific order
*

*lookup_value* - is the value you use to find the value you want in the array, a number, text or logical value, or a reference to one of these.

*lookup_array* - is a contiguous range of cells containing possible lookup values, an array of values, or a reference to an array.

*match_type* 1: Finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order

*match_type* 0: Finds the value that is exactly equal to lookup_value. Lookup_array can be in any order

*match_type* -1: Finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order

**My comments:**

- The lookup_array must be a one dimensional horizontal or vertical cell range
- The lookup_value can also be an array, one or two dimensional. (array formula)
- The lookup_array can be one value. (array formula)
- You can use other excel functions in the lookup_value argument.
- You can use other excel functions in the lookup_array argument. As long as they return a one dimensional array.
- I use match_type = 0 almost all the time.

### Example 1 - Lookup_array in ascending order

=MATCH(C17, C19:C21,1)

becomes

MATCH(2, {1; 5; 10; 12},1)

Match_type 1 - Find the **largest** value {1; 5; 10; 12} that is **less** or **equal** to lookup_value (2)

MATCH(2, {**1**; 5; 10; 12},1)

The relative position of number 1 in array {1; 5; 10; 12} is 1. 1 is returned in cell D14.

### Example 2 - Lookup_array in any order

=MATCH(C17, C19:C21,0)

becomes

MATCH(1, {10; 12; 1; 5},0)

Match_type 1 - Find the **first** value {10; 12; 1; 5} that is **exactly** **equal** to lookup_value (1)

MATCH(1, {10; 12; **1**; 5},0)

The relative position of number 1 in array {10; 12; 1; 5} is 3. 3 is returned in cell D14.

### Example 3 - Lookup_array in descending order

=MATCH(C17, C19:C21,-1)

becomes

MATCH(2, {12; 10; 5; 1},-1)

Match_type 1 - Find the **smallest **value {12; 10; 5; 1} that is **greater than **or **equal** to lookup_value (2)

MATCH(2, {12; 10; **5**; 1},-1)

The relative position of number 5 in array {12; 10; **5**; 1} is 3. 3 is returned in cel D14.

### Example 4 - Array formula

=MATCH(C19:C22, D19:D22,0)

becomes

MATCH({12;1;10;5}, {10; 12; 1; 1},0)

Match_type 1 - Find the **first** value {10; 12; 1; 1} that is **exactly** **equal** to lookup_value {12;1;10;5}

Lookup value 12 is the **second** value in the lookup_array {10; **12**; 1; 1}.

Lookup value 1 is the **third** value in the lookup_array {10; 12; **1**; 1}.

Lookup value 10 is the **first **value in the lookup_array {**10**; 12; 1; 1}.

Lookup value 5 is not found in the lookup_array {10; 12; 1; 1}.

{2; 3; 1; #N/A} is returned in cell range B19:B22.

### Wildcard character

fghdfn

### VBA Example

The following macro uses the MATCH function to find the position of the lookup value in cell range C5:C8. It then displays a message box containing that position.

**VBA code**

Sub VBA_MATCH() MsgBox Application.WorksheetFunction.Match(Range("C2"), Range("C5:C8"), 0) End Sub

### Download excel *.xlsx file

### Category: Functions

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel, Functions

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Comments(10) Filed in category: Excel, Functions

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Comments(8) Filed in category: Excel, Functions, Mod, Quotient

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Comments(4) Filed in category: Excel, Functions, Transpose

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Comments(4) Filed in category: Excel, Functions, Textjoin

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]

Comments(4) Filed in category: Excel, Functions, Mmult

### 12 Responses to “MATCH function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] MATCH(lookup_value;lookup_array; [match_type]) Returns the relative position of an item in an array that matches a specified value [...]

Oscar,

You might want to read the comments of the below post to see couple of more interesting uses of the MATCH Function

https://fastexcel.wordpress.com/2013/02/27/speedtools-avlookup2-memlookup-versus-vlookup-performance-power-and-ease-of-use-shootout-part-1/#comments

sam,

Very interesting comment! I never thought of that.

Thank you for commenting!

Respected sir,

You solved one of my excel related problem some months back. This time I have another problem. This time I have some fields as below:

Capacity City books Big_Boxes small_Boxes Total

90 Kanpur 400 4 1 5

Lucknow 690 7 1 8

Jhansi 240 2 1 3

Allahabad 20 0 1 1

Total 17

Now, I have to generate slips to paste on boxes which are to be despatched:

City: Kanpur

No_of_Text_Books in this Box 90 Box No.1

Total_No._of_Books 400 in 5 Boxes

These slips are to be generated till end. Please help me.

neeraj kumar,

Formula in cell A10:

Formula in cell A11:

Download excel *.xlsx file

neeraj-kumar.xlsx

[…] MATCH(lookup_value, lookup_array, [match_type] Returns the relative position of an item in an array that matches a specified value […]

[…] MATCH(lookup_value, lookup_array, [match_type] Returns the relative position of an item in an array that matches a specified value. […]

[…] Match function […]

[…] and returns 2. Read more about MATCH function. […]

[…] MATCH(lookup_value;lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value […]

[…] MATCH(lookup_value,lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value […]