30 Excel Functions in 30 Days: 19 – MATCH

Icon30DayYesterday, 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.

Match00

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

MATCH Syntax

The MATCH function has the following syntax:

  • MATCH(lookup_value,lookup_array,[match_type])
    • 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 Traps

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.

=MATCH(D2,B3:B7,0)

Match01

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”)

Match01b

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.

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)

Match02

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.

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)

Match03

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.

  1. The ABS function returns the absolute difference between each guess and the correct number.
  2. The MIN function finds the smallest difference.
  3. The MATCH function finds the smallest difference in the list of differences. If there are multiple identical differences, the first one will be returned.
  4. The INDEX function returns the name in that position in the list of names.

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))

Match04

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

_____________

You may also like...

5 Responses

  1. ikkeman says:

    What I can’t figure out: Why, once you figure out index(match()), would anyone use any of the lookup’s.
    is there any function or value to the lookup that can’t be matched by index?

  2. Gregory says:

    I had to use Evaluate Formula to step through example 4, but now understand the genius involved. Thanks for all the work putting together the examples.

  3. Contextures Blog » 30 Excel Functions in 30 Days: 24 - INDEX says:

    […] MATCH to find the name for closest guess in a […]

  4. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] MATCH […]

  5. Contextures Blog » Excel Bingo Card Random Number Code says:

    […] that you could use to create a set of three cards with random numbers. It uses the INDEX and MATCH functions to pull the numbers from another […]

Leave a Reply

Your email address will not be published. Required fields are marked *