INDEX and MATCH Revisited – You may Be Missing some Important Point

Think in INDEX and MATCH as a substitute for VLOOKUP in cases of non-left lookup.

Why do I need to substitute VLOOKUP?

VLOOKUP is just a right lookup formula. What does this mean?

It only search values at the left of the table array and return any attribute to the right (included the same index column)INDEX and MATCH can search values at any index column of the table array and can return any attribute to the right, left or included the same index column.

Given say that, let’s explore the syntax of these two functions (INDEX and MATCH) that combined form a powerful lookup formula…

Let’s use the following backend list to illustrate the use

INDEX and MATCH backend list

INDEX Formula.

INDEX(array,row_num,column_num) or INDEX(table to search the value specified by a row and a column,row found by MATCH, attribute column)

Where:

  • array It is the array containing the index column and columns with attributes. In above table: $A$2:$C$6. The index column is not necessarily the left one as in table_array of VLOOKUP
  • row_num It is found by a MATCH function. Here resides the LOOKUP magic (see below…)
  • column_num It is the number of the column with the attribute you want to show for lookup_value (lookup_value is found by a MATCH function). It has the same objective that “col_index_num” of VLOOKUP

A simple formula would be INDEX($A$2:$C$6,2,3) that results in “Below average”

Assume the row 2 was found by a MATCH function…

MATCH formula.

MATCH(lookup_value,lookup_array,match_type) orMATCH(search this value in,this column range,search exact match only)

Where:

  • lookup_valueIt is the value you want to search in lookup_array, for example 60%. It has the same concept as lookup_value of VLOOKUP but the index column is not the left one by default. You can specify any column of the array. See lookup_array below.You can reference or hard code lookup_value this way:Reference the lookup_value (text, number or date) this way: =MATCH(A2, …) Where A2 contains the given value.You can hard code the lookup_value in several ways…
    • Text: =MATCH(“Susan”, …) Don’t type Susan (without quotes) Excel will interpret it as named range
    • Number: =MATCH(1000, …) Don’t put quotes to numbers
    • Percentage: =MATCH(10%, …) You can also use =VLOOKUP(0.1, …)
    • Date: =MATCH(40091, …) You can enter a number that represents a date serial number
    • Date: =MATCH(DATEVALUE(“10/5/2009”), …)
    • Date: =MATCH(DATE(2009,10,5),H5:I7,2,0),…)
  • lookup_array It is the column array (index column) that contains the lookup_value. This index column does not need to be sorted. In above table $A$2:$A$6 or “%”
  • match_type It resembles the “range_lookup” VLOOKUP argument. Set it to 0 for “Exact match”

The formula would be MATCH(60%,$A$2:$A$6,0) that results in 2. Then you feed 2 into row_num of INDEX function, see below…

INDEX($A$2:$C$6,MATCH(60%,$A$2:$A$6,0),3) that could be seen this way: INDEX($A$2:$C$6,2,3) that results in “Below average”

Conclusion about INDEX and MATCH Formula

INDEX and MATCH is not just a better VLOOKUP, they can be used in Array Formulas to produce the most potent lookup formulas like this one: {=INDEX(Haystack!$A$2:$D$7,MATCH(A2,MONTH(Haystack!$B$2:$B$7),0),3)} that searches a month from index column containing date serial numbers.