LiveFlow

The MATCH function is a powerful tool available in both Excel and Google Sheets, used to find the position of a value within a range. It is often combined with INDEX to create dynamic and flexible lookups. This article breaks down how MATCH works, its syntax, and real-world applications.

The MATCH function searches for a specific value in a given range and returns its relative position rather than the value itself. Unlike VLOOKUP, which retrieves data from a column, MATCH simply tells you where a value is located, making it highly flexible and efficient.

Follow the steps below on how to use the MATCH function in Excel and Google Sheets:

Step 1: Understanding the Match Syntax

The MATCH function follows this format: MATCH (lookup_value, lookup_array, [match_type])

1. lookup_value : The number or value you want to find.
2. lookup_array : The range of numbers where Excel/Google Sheets will search.
3. match_type (optional) : Controls how the search is performed
 1. 0 : Exact match (most commonly used).
 2. 1 : Largest value ≤ lookup_value (range must be sorted in ascending order).
 3. -1 : Smallest value ≥ lookup_value (range must be sorted in descending order).

1. The MATCH function follows this format: MATCH (lookup_value, lookup_array, [match_type])
 1. lookup_value : The number or value you want to find.
 2. lookup_array : The range of numbers where Excel/Google Sheets will search.
 3. match_type (optional) : Controls how the search is performed
 1. 0 : Exact match (most commonly used).
 2. 1 : Largest value ≤ lookup_value (range must be sorted in ascending order).
 3. -1 : Smallest value ≥ lookup_value (range must be sorted in descending order).

Select the range of cells that contain the values you want to search through

Identify the position of the desired value

1. For example, we want to extract column number for Services within the Account Range

1. Select the range of cells that contain the values you want to search through
2. Identify the position of the desired value
   1. For example, we want to extract column number for Services within the Account Range
3. Enter the MATCH formula as shown below

Enter in the lookup value that you would like to see

1. Enter in the lookup value that you would like to see

For this example, Services is in cell D9

Enter in the Account Range that “Services” is under in the sheet

1. Enter in the Account Range that “Services” is under in the sheet

For this example, it is from cell B12:B17

In this instance, I want an exact match for the lookup value, so I add in 0 after my lookup array.

1. In this instance, I want an exact match for the lookup value, so I add in 0 after my lookup array.