Learn from these scenarios and practice your skills
Are you using one of the LOOKUP functions in Microsoft Excel when you need an item’s position instead of the item itself? If so, you should know that there’s an easier and more accurate way to do this: Use the MATCH function instead.
With more than 450 functions, it’s easy to overlook handy capabilities in Microsoft Excel like INDEX, INDIRECT, FREQUENCY, TREND, GROWTH, and MATCH. But mastering functions like MATCH using insider tricks and hacks will open up a new world of Excel tools.
Use This Syntax for MATCH
How it works: You can use the MATCH function in Microsoft Excel to search for a value in an array and discover that item’s relative position, according to a Tech on the Net (TONT) tutorial. As a built-in “Lookup/Reference Function” in Excel, you can use MATCH as a worksheet function, entering it as part of a formula in a cell.
The syntax that you’ll use for the MATCH function is: =MATCH(value, array, [match_type]).
“Value” is the value to search for in the “array,” which is the range of cells that contains the value you’re searching for, TONT explained. “Match_type” is optional. This is the type of match that the function will perform, with a default of one (1).
Here are how the match_type choices work:
- Default or 1 – MATCH will find the largest value that is less than or equal to “value.” Sort your array in ascending order.
- 0 – MATCH will find the first value that is equal to “value,” meaning an exact match. You don’t need to sort.
- -1 – MATCH will find the smallest value that is greater than or equal to “value.” Sort your array in descending order.
Another way:
You can also combine MATCH with the INDEX function, according to ExcelJet. Using these two functions together will retrieve the value at the position returned by MATCH. The syntax is: =MATCH (lookup_value, lookup_array, [match_type]).
Put Your New Knowledge into Practice
If no match is found, you’ll see the #N/A error, ExcelJet noted. Also, if your match_type is 0 and your value is text, you can use the wildcard characters (?) and (*) in the value. Using an asterisk (*) matches any sequence of characters, while using a question mark (?) matches any single character.
Example: The range is A1:A3, which contains the values 5, 25, and 38. You enter the formula =MATCH(25,A1:A3,0). In this case, the MATCH function would return the number 2, because 25 is the second item in the range.
More: Microsoft offers the following scenarios that you can try out to practice your MATCH function skills:
First, open a new Excel worksheet, and then copy the example data in the table below and paste it into cell A1.
| Product | Count |
| Bananas | 25 |
| Oranges | 38 |
| Apples | 40 |
| Pears | 41 |
Try this: To show results, select each formula, press F2, and then press Enter:
- =MATCH(39,B2:B5,1) – Because the results won’t yield and exact match, the position of the next lowest value (38) in the range B2:B5 is returned. Your result should be 2.
- =MATCH(41,B2:B5,0) – You should get an exact match for this query, with a result of 4.
- =MATCH(40,B2:B5,-1) – You should get an error (#N/A) because the values in the range B2:B5 are not in descending order.
MATCH is Just One of Many Hidden Gems
Keep in mind: Another benefit is that MATCH isn’t case-sensitive. Hence it doesn’t matter if you use uppercase or lowercase letters when you’re searching, ExcelJet added.
(This post first appeared in a ProfEd blog)
By Sarah T on June 8th 2018
