Yesterday, in the 30XL30D challenge, we found text strings with the SEARCH function, and used IFERROR and ISNUMBER to deal with its error results.
For day 19 in the challenge, we'll examine the MATCH function. It searches for a value in an array, and returns its position, if the value is found.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let's take a look at the MATCH information and examples, and if you have other tips or examples, please share them in the comments.
Function 19: MATCH
The MATCH function returns the position of a value in an array, or #N/A if not found. The array can be sorted, or unsorted, and the MATCH function is not case sensitive.
How Could You Use MATCH?
The MATCH function returns the position of an item in an array, and that result can be used by other functions, such as INDEX or VLOOKUP. For example:
- Find position of item in unsorted list
- Use with CHOOSE to get student grades
- Use with VLOOKUP for flexible column choice
- Use with INDEX for to show winner's name
The MATCH function has the following syntax:
- lookup_value can be text, number or logical value
- lookup_array is an array, or array reference (contiguous cells in a single row or column)
- match_type can be -1, 0 or 1. If omitted, assumed to be 1
MATCH function returns the position of the item found, not the value. If you need the value, combine MATCH with another function, like INDEX.
Example 1: Find Item in Unsorted List
For an unsorted list, you can use 0 as the match_type argument, to find an exact match. If you're searching for text, and using 0, you can include wildcard characters in the lookup value.
In this example, you can type a month name, or partial name with wildcards, to find that month's position in the list.
Instead of an array reference, you can enter an array as the lookup_array argument. In this variation, the lookup month name is entered in cell D5, and three month names are entered in the MATCH function's second argument. If a later month, such as Oct, is entered in D5, the result will be #N/A.
=IF(C2+0<14,"Time to upgrade","Latest version")
Example 2: Change Student Grades to Letters
Just as you did with VLOOKUP, you can use MATCH to help convert a student's score to a letter grade. In this example, it is combined with CHOOSE, to get the letter grade. The match_type is -1, because the scores are sorted in descending order.
When the match_type is -1, the result is the smallest value greater than or equal to lookup value. The lookup value is 54, and it's not in the list of scores, so the position for 60 is returned. Because 60 is in position 4, the 4th value in the CHOOSE options is the result -- cell C6, with a value of D.
Example 3: Create Flexible Column Choice in VLOOKUP
To make a VLOOKUP formula more flexible, you can use MATCH to find a column number, instead of hard-coding it into the formula. In this example, users can select a region in cell H1, as the value for the VLOOKUP. Then, they can select a Month in cell H2, and the MATCH function returns the column for that month.
Example 4: FIND Closest Match with INDEX
The MATCH function also works well with the INDEX function, which we'll see later in the challenge. In this example, the MATCH function is used to find the guess that is closest to the correct number.
- The ABS function returns the absolute difference between each guess and the correct number.
- The MIN function finds the smallest difference.
- The MATCH function finds the smallest difference in the list of differences. If there are multiple identical differences, the first one will be returned.
- The INDEX function returns the name in that position in the list of names.
Download the MATCH Function File
To see the formulas used in today's examples, you can download the MATCH function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the MATCH Video
To see a demonstration of the examples in the MATCH function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Find Item Position in List With Excel MATCH Function