Subscriber only lesson.

Sign up to the Financial Modeling Basics course to view this lesson.

## About this lesson

Illustration and issues with the MATCH function.

## Exercise files

Download this lessonâ€™s related exercise files.

MATCH.xlsx11.2 KB MATCH - Solution.xlsx

11.3 KB

## Quick reference

### MATCH

Discover how to use MATCH functions in a formula.

### When to use

**MATCH(Lookup_value,Lookup_vector,[Match_type]) **returns the relative position of an item in an array that (approximately) matches a specified value. It is not case sensitive.

### Instructions

#### Overview

**Match_type 1 [default if omitted]**: finds the largest value less than or equal to the**Lookup_value**– but the**Lookup_vector**must be in strict ascending order, limiting flexibility;**Match_type 0**: probably the most useful setting,**MATCH**will find the position of the first value that matches**Lookup_value**exactly. The**Lookup_array**can have data in any order and even allows duplicates; and**Match type -1**: finds the smallest value greater than or equal to the**Lookup_value**– but the**Lookup_array**must be in strict descending order, again limiting flexibility.

When using **MATCH**, if there is no (approximate) match, *#N/A* is returned (this may also occur if data is not correctly sorted depending upon **Match_type**).

#### Example

In the figure below, MATCH(F6,B6:B16,0) gives a value of 2, being the relative position of the first ‘1 in the range. Note that having Match_type 0 here is important. The data contains duplicates and is not sorted alphanumerically. Consequently, using Match_type 1 would give the wrong answer: #N/A

Lesson notes are only available for subscribers.