Function Index-Match in Excel

October 18, 2019

You can use a combination of index and match instead of vlookup.
And it is better than vlookup.

Function Index

INDEX(array, row_num, [column_num])
  • A range of cells or an array constant.
  • Selects the row in array from which to return a value.
  • Selects the column in array from which to return a value.

Returns the value of an element in a table or an array, selected by the row and column number indexes.

Function Match

MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: The value that you want to match in lookup_array.
  • lookup_array: The range of cells being searched.
  • match_type: -1, 0, or 1. The match_type argument specifies how Excel matches values. Default value is 1.
  • 1: values in lookup_array <= lookup_value
  • 0: values in lookup_array = lookup_value
  • -1: values in lookup_array >= lookup_value

MATCH returns the position of the matched value within lookup_array, not the value itself.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

String search
  • MATCH does not distinguish between uppercase and lowercase letters when matching text values.
  • 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.

© 2021 CXSMXS, An essay by a Korean developer living in Japan