Finding Positions With MATCH

If INDEX grabs a value by position, MATCH does the opposite job: it tells you what position a value sits in.

You hand MATCH a value to look for and a range to search, and it returns a number, the position where that value was found.

MATCH does not return the value itself, only its location. That number is exactly what INDEX needs to do its work.

MATCH takes three arguments:

• lookup_value — the value you want to find
• lookup_array — the range to search through
• match_type — how to match: 0, 1, or -1

A typical call looks like =MATCH("Cherry", B2:B6, 0). The trailing 0 means "find an exact match."

Almost always you will use 0 for everyday lookups.

Say B2:B6 holds Apple, Banana, Cherry, Date, Fig. Running =MATCH("Cherry", B2:B6, 0) returns 3.

Why 3? Because Cherry is the 3rd item in the range. MATCH counted from the start of B2:B6 and found Cherry in position 3.

Notice the answer is a position number, not the word Cherry. That position is the bridge you will hand to INDEX in the next lesson.

The third argument controls how MATCH searches:

• 0 — exact match; the value must be found precisely, in any order
• 1 — largest value less than or equal to the lookup; data must be sorted ascending
• -1 — smallest value greater than or equal; data must be sorted descending

For finding a specific item like a product code or name, always use 0. The other modes are for sorted numeric ranges.

MATCH works on numbers just as well as text. If C2:C8 holds order IDs and you want to know where ID 5042 is, you write =MATCH(5042, C2:C8, 0).

If 5042 sits in the 4th row of that range, MATCH returns 4.

The lookup value can also be a cell reference. =MATCH(E1, C2:C8, 0) finds whatever ID is typed into E1, so the search updates as you change E1.

MATCH does not care whether your range runs vertically or horizontally. It just counts positions.

Suppose row 1 holds month headers in B1:M1: Jan, Feb, Mar, and so on. To find which column holds "Mar" you write =MATCH("Mar", B1:M1, 0), which returns 3.

This horizontal use is essential for two-way lookups, where you match a row position and a column position separately.

If the value you search for does not exist in the range, MATCH returns the #N/A error, which simply means "not available."

For example =MATCH("Mango", B2:B6, 0) on a list without Mango returns #N/A.

This is normal behavior, not a broken formula. Later you will wrap lookups with IFNA or IFERROR to show a friendly message instead of the raw error.

A big advantage of match_type 0 is that your data does not need to be sorted. MATCH scans the range and returns the position of the first exact match it finds.

So whether your region list is alphabetical or completely jumbled, =MATCH("West", A2:A20, 0) still locates West correctly.

This freedom is why exact match is the default choice for almost every real-world lookup.

Match_type 1 is useful for bands and tiers. With a sorted ascending list of thresholds, MATCH finds the largest value that is still less than or equal to your lookup.

If A2:A6 holds 0, 100, 500, 1000, 5000 and you run =MATCH(750, A2:A6, 1), you get 3, because 500 is the highest threshold not exceeding 750.

This is how grade and pricing tables snap a value to the right band.

By itself, MATCH only tells you a position number, which is not very useful as a final answer. "The customer is in row 7" is just a stepping stone.

What you usually want is the data at that position, the customer's email, balance, or status.

That is why MATCH almost never works alone. In the next lesson you will feed MATCH's position straight into INDEX to retrieve the actual value you care about.

You can run MATCH twice to pin down a row and a column independently. One MATCH finds the row position by searching a column of labels, the other finds the column position by searching a row of headers.

For instance, find which row holds "East" with =MATCH("East", A2:A10, 0) and which column holds "Q3" with =MATCH("Q3", B1:E1, 0).

Together those two numbers describe an exact cell, ready for INDEX.

Make sure you understand what MATCH returns.

You learned that MATCH returns the position of a value inside a range:

• =MATCH(lookup_value, lookup_array, match_type)
• Use 0 for exact matches; data need not be sorted
• Use 1 on ascending data to find the right band or tier
• It works vertically or horizontally and returns #N/A when nothing matches

MATCH gives you the position; INDEX turns a position into a value. Next, you will combine them into one powerful lookup formula.