excelMATCH function


Introduction

(Optional) Every topic has a focus. Tell the readers what they will find here and let future contributors know what belongs.

Parameters

ParameterDescription
lookup_valueThe value you want to match. Can be either a fixed value, cell reference or named range. Strings may not exceed 255 characters (required)
lookup_arrayThe cell reference (or named range) that you want to search, this can either be a row or a column sorted in ascending order for default type 1 matches; desceding order for -1 type matches; or any order for type 0 matches (required)
match_typeControls the way the search works. Set to 0 if you only want exact matches, set to 1 if you want to match items less than or equal to your lookup_value, or -1 if you want to match items greater than or equal to your lookup_value. (Optional - defaults to 1)

Remarks

Purpose

Use the MATCH function to check if (and where) a value can be found in a list. Often seen as a parameter return for the row and/or column in INDEX(array, row, column) function. Allows negative row/column references allowing left or above lookups.

Similar functions:

  • VLOOKUP - like MATCH but returns data from the table, rather than the row or column number. Can only search a table vertically and return values in or to the right of the found value.
  • HLOOKUP - like MATCH but returns data from the table, rather than the row or column number. Can only search a table horizontally and return values in or below the found value.