MATCH
How far down the list is it?
Find how far down a list a value sits
A number: the position of the value in the list
Syntax
=MATCH(find_value, look_in, [how])
| Argument | Required | What goes here |
|---|---|---|
find_value | Required | The code or name you are looking for. |
look_in | Required | The single column to search, like B2:B13. |
how | Optional | Write 0 for an exact match, which is what you want here. |
How to use MATCH
MATCH returns a position, not a value. Fourth in the list, not what the fourth thing is.
The third argument says how to match, and it should be 0 for exact. Leave it out and Excel matches approximately against a list it assumes is sorted, which returns a confident wrong answer on an unsorted column.
Its usual job is feeding INDEX. It is also useful on its own as a check: MATCH tells you whether a code is on a list at all, and where.
Position is relative to the range you gave it, so a range starting at row 2 reports the first row as 1.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=MATCH("Meridian Freight",G2:G6,0)Fourth in the terms table. The number is only useful to something else. | 4 |
=INDEX(H2:H6,MATCH("Meridian Freight",G2:G6,0))Handed to INDEX, it becomes a lookup. | 45 |
=MATCH(2450,E2:E9,0)Which row of the ledger holds that amount, counting from the first data row. | 3 |
=IFERROR(MATCH("Harbour Ltd",G2:G6,0),"Not on the list")Used as a check rather than a lookup: is this supplier on the table at all. MATCH stops with an error when it is not, so something has to catch that and say so. | Not on the list |
Notes
- Always write 0 as the third argument. Without it MATCH assumes the list is sorted and returns the nearest one below.
- MATCH errors when the value is not there, which is useful information and not a failure.
Related
A reference tells you what it does. A unit makes you use it.