Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Merge tables with VLOOKUP in Excel
  • How to get last row in text data in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to get address of last cell in range in Excel
  • Basic INDEX MATCH approximate in Excel

Data Analysis Examples

  • How To Perform and Interpret Regression Analysis in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to create a Histogram in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation number multiple 100
  • Excel Data validation only dates between
  • Excel Data validation unique values only
  • How To Create Drop-down List in Excel

How to use Excel MATCH Function

by

This Excel tutorial explains how to use the MATCH function with syntax and examples.

Excel MATCH function Description

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Basic exact match example

Explanation: When match type is set to zero, MATCH performs an exact match. In the example above; the formula in E3 is:  MATCH(E2,B3:B11,)

Return

A number representing a position in lookup_array.

Syntax

MATCH (lookup_value, lookup_array, [match_type])

Arguments

  • lookup_value – The value to match in lookup_array.
  • lookup_array – A range of cells or an array reference.
  • match_type – [optional] How to match, specified as -1, 0, or 1. Default is 1.
Worked Example:   Check if range contains a value not in another range in Excel
Match offers several different matching modes, which makes it more flexible than other lookup functions. Used together with INDEX, MATCH can retrieve the value at the matched position.

Note: Match type information

Worked Example:   Example of COUNTIFS with variable table column in Excel
  • If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.
  • If match_type is 0, MATCH finds the first value exactly equal to lookup_value. lookup_array does not need to be sorted.
  • If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.
  • If match_type is omitted, it is assumed to be 1.
  • Note: All match types will find an exact match.
Worked Example:   Get column index in Excel Table

Basic approximate match

Explanation: When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:

Basic wildcard match

Explanation: When match type is set to zero, MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is: MATCH(E2,B3:B11,)

This is equivalent to:

MATCH("pq*",B3:B11,)

Post navigation

Previous Post:

How to use Excel MMULT Function

Next Post:

How to use Excel ROW Function

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
  • How to Capitalize first letter in a sentence in Excel
  • Remove text by matching in a cell in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • How to split text string at specific character in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • Get date from day number in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate quarter from date in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • Example of Future value of annuity in Excel
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • SYD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles