Featured image
MATCH in Excel
MATCH returns the position of a value within a range, not the value itself: =MATCH(lookup_value, lookup_array, [match_type]). The third argument should almost always be 0, which means exact match. Left off, MATCH assumes the range is sorted ascending and reports a wrong position on anything that is not.
=MATCH(lookup_value, lookup_array, [match_type])
MATCH answers one question: where in this list is it. The answer is a number counted from the start of the range, so a value in the fifth cell of a range comes back as 5 regardless of which row of the sheet that is.
On its own that is useful for a handful of things, checking whether a value exists in a list, finding how far down a sorted series a number falls. Its real job is supplying the position argument to INDEX, which turns the pair into a lookup with none of VLOOKUP's restrictions.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The value to find. Text, a number, or a reference to a cell holding one.
- A single column or a single row to search. Not a rectangle: MATCH reports one number, so it needs one strip of cells to count along.
- optional
- 0 for exact, which is what you want. 1, or the argument left off, finds the largest value at or below the lookup value and requires the range to be sorted ascending. −1 finds the smallest value at or above it and requires descending order.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Where in the list is this name
=MATCH("Eli", A2:A7, 0)Returns 5
Eli is the fifth entry of A2:A7. The answer is 5, not 6: the count starts at the top of the range, which is row 2 of the sheet.
Find a number's position in a different column
=MATCH(7600, D2:D7, 0)Returns 3
The same thing on a numeric column. This is the half of INDEX MATCH that does the searching, and it is worth putting in a spare cell on its own whenever a lookup is misbehaving.
Who had the highest revenue
=INDEX(A2:A7, MATCH(MAX(D2:D7), D2:D7, 0))Returns Chen
MAX finds the largest revenue, MATCH finds which position that value sits at, INDEX reads the name at that position. Three functions, each doing one thing, which is how most real formulas are built.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
The third argument is not optional in practice
MATCH has the same trap as VLOOKUP, in the same place and for the same reason. The argument that controls how matching works is marked optional, and its default is the mode that fails quietly.
- 0 means exact. MATCH scans from the top and stops at the first equal value. Nothing equal means #N/A, which is honest.
- 1, or nothing, means the largest value at or below the lookup value, and assumes the range is sorted smallest to largest. On an unsorted range it returns a position, that position is wrong, and there is no error to tell you.
- −1 means the smallest value at or above, and assumes descending order.
=MATCH("Eli", A2:A7, 0)Returns 5. Write the 0. It costs two characters and removes the only way this function can lie to you.
Using MATCH to check whether something exists
Because a failed MATCH returns #N/A rather than a position, ISNA around it is a clean yes-or-no test for whether a value appears in a list at all, which is a quicker way to reconcile two columns than scanning them by eye.
=ISNA(MATCH("Gus", A2:A7, 0))Returns TRUE. True means Gus is not in the list.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=MATCH("Gus", A2:A7, 0)WhyNothing in the range equals the lookup value. With match_type 0 that is the correct answer rather than a fault.
FixIf the value should be there, compare the two sides properly. Trailing spaces are the usual cause, so try TRIM; a number stored as text is the next, so try ISNUMBER down the column. If the value genuinely is absent, wrap the formula in IFNA.
=INDEX(A2:A7, MATCH("Gus", A2:A7, 0))WhyThe same error seen through INDEX. INDEX did nothing wrong; it was handed an error and passed it on.
FixSplit the formula up while debugging. Put the MATCH in its own cell and see what it returns before looking at anything else.
What goes wrong
These are the mistakes that come up again and again with MATCH. Each one is reasonable. That is why people make it.
Expecting MATCH to return the value.
WhyIt is a lookup function, and every other lookup function returns the thing you were looking for.
InsteadMATCH returns a position. If you want the value, that position goes into INDEX.
Giving MATCH a two-dimensional range.
WhyThe habit comes from VLOOKUP, whose second argument is a whole table.
Insteadlookup_array is one column or one row. A rectangle gives an error or a meaningless number depending on the version.
Reading the result as a sheet row number.
WhyThe number looks like a row number and sometimes coincides with one.
InsteadIt counts from the top of the range. For A2:A7 the answer is one less than the sheet row, and for a range starting elsewhere the offset is different again.
Common questions
What does MATCH return in Excel?
A number: the position of the lookup value within the range, counted from 1 at the start of that range. It does not return the value itself.
Why should MATCH always have 0 as the third argument?
Without it, MATCH assumes the range is sorted ascending and returns the position of the largest value at or below the one you asked for. On unsorted data that is a wrong answer with no error attached to it.
Why does my MATCH return #N/A when the value is clearly there?
Because the two values are not equal. A trailing space, a non-breaking space pasted from a web page, or a number stored as text will all look identical and compare as different. TRIM and ISNUMBER find both cases.
Can MATCH search a row instead of a column?
Yes. The range can be a single row, and the position is then counted left to right. This is how INDEX MATCH handles tables that are laid out across the page.