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

Data Analysis

  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to create dynamic reference table name in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Excel Bar Chart
  • How to calculate current stock or inventory in Excel

References

  • How to create dynamic named range with OFFSET in Excel
  • Vlookup Examples in Excel
  • How to use Excel TRANSPOSE Function
  • How to get last row in numeric data in Excel
  • How to get last column number in range in Excel

Data Validations

  • Excel Data validation must not contain
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation exists in list
  • Excel Data validation whole percentage only

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

Worked Example:   Get first non-blank value in a list in Excel

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

  • 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:   Multi-criteria lookup and transpose in Excel

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:

Worked Example:   Two-way lookup with INDEX and MATCH in Excel

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

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • Invoice status with nested if in Excel
  • Extract multiple matches into separate rows in Excel
  • How to use Excel FALSE Function

Date Time

  • Convert time to time zone in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Calculate number of hours between two times in Excel
  • Count holidays between two dates in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Map inputs to arbitrary values in Excel

General

  • Delete Blank Rows at Once in Excel
  • How to count total columns in range in Excel
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to calculate project complete percentage in Excel
  • Common Errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning