Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to add Trendline to a chart in Excel
  • How to calculate average last N values in a table in Excel
  • How to Create Area Chart in Excel
  • How to Create Column Chart in Excel
  • How to Create Gantt Chart in Excel

References

  • Merge tables with VLOOKUP in Excel
  • How to retrieve first match between two ranges in Excel
  • How to use Excel OFFSET function
  • Get nth match with INDEX / MATCH in Excel
  • Multi-criteria lookup and transpose in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation allow uppercase only
  • Excel Data validation only dates between
  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total

MATCH function: Description, Usage, Syntax, Examples and Explanation

by

What is MATCH function in Excel?

MATCH function is one of Lookup and Reference functions in Microsoft Excel that is used to search for an item in a range of cells, and then return the relative position of that item in the range.

Syntax of MATCH function

MATCH(lookup_value, lookup_array, [match_type])

MATCH formula explanation

The MATCH function syntax has the following arguments:

  • lookup_value: The value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
  • lookup_array: The range of cells being searched.
  • match_type (Optional): The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.The following table describes how the function finds values based on the setting of the match_type argument.
Worked Example:   Vlookup Examples in Excel
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
  • MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH(“b”,{“a”,”b”,”c”},0) returns 2, which is the relative position of “b” within the array {“a”,”b”,”c”}.
  • MATCH does not distinguish between uppercase and lowercase letters when matching text values.
  • If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
  • If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Worked Example:   Lookup and sum column in Excel

Example of MATCH function

Steps to follow:

Worked Example:   Example of COUNTIFS with variable table column in Excel

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

Product Count
Bananas 25
Oranges 38
Apples 40
Pears 41
Formula Description Result
=MATCH(39,B2:B5,1) Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned. 2
=MATCH(41,B2:B5,0) The position of the value 41 in the range B2:B5. 4
=MATCH(40,B2:B5,-1) Returns an error because the values in the range B2:B5 are not in descending order. #N/A

Post navigation

Previous Post:

CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Next Post:

VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel XOR Function
  • SWITCH function example in Excel
  • IF with boolean logic in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add months to date in Excel
  • Calculate series of dates by workdays in Excel
  • Convert text timestamp into time in Excel
  • How to calculate nth day of year in Excel
  • Excel Date & Time Functions Example

Grouping

  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Group arbitrary text values in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel

General

  • Excel Autofill Cell Ranges, Copy, Paste
  • Customize Ribbon In Excel
  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to calculate project complete percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning