When you are working with massive datasets in Microsoft Excel, you rarely need to know a value’s exact cell reference (like C42). Instead, you usually need to know where a specific item is located in relation to the rest of the data. For example, if you have a ranked list of 500 salespeople, you might want to know exactly what position “John Smith” occupies on that list. To find an item’s relative numerical position within a specific array, you must use the MATCH function.
How the MATCH Function Works
The MATCH function does not return the data itself; it strictly returns a number representing the item’s position. If “John Smith” is the 5th name in your list, the function will output the number 5.
The syntax for the formula is: =MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: What exactly are you searching for? This can be a text string (like “John Smith”) or a reference to another cell containing the search term.
- lookup_array: Where should Excel look? This is the specific range of cells containing your data (e.g., A2:A501).
- match_type: This tells Excel how strict the search should be. You almost always want to use 0, which forces the function to find an exact match.
Writing a Basic MATCH Formula
Imagine you have a list of employee names in column A, from cell A1 down to A10. You want to find the exact position of “Sarah Connor” in that list. You would select an empty cell and type the following formula:
=MATCH("Sarah Connor", A1:A10, 0)If Sarah Connor’s name is located in cell A4, the formula will calculate and instantly output the number 4, because it is the fourth item in the specified array.
Advanced Usage: Combining MATCH with INDEX
While the MATCH function is useful on its own, it is almost exclusively used by data analysts in combination with the INDEX function to create a highly flexible alternative to VLOOKUP. This combination is universally known as INDEX MATCH.
The INDEX function is designed to return the actual data from a specific position. By feeding a MATCH formula directly into an INDEX formula, you can perform highly advanced, multidirectional data lookups.
For example, if you have names in column A and salaries in column B, and you want to find the salary for “John Smith,” you would nest the functions like this:
=INDEX(B1:B10, MATCH("John Smith", A1:A10, 0))The internal MATCH function dynamically figures out what row John Smith is on (e.g., Row 7). It then passes that number to the INDEX function, which pulls the data from Row 7 of the salary column, creating a flawless, highly optimized search engine within your spreadsheet.