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 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_value It 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.
Popularity: 6% [?]
{ 3 } Comments
Implemented INDEX & MATCH formula last month in a budget vs actual report.
Still need to do a manual data entry on the actuals, so next step is to find a way to download the correct actuals through ODBC from our ERP (MS Dynamics).
For now the report has been received as impressive…finally some acknowledgement.
Thanks John !
John, thanks for always distilling the functionality of Excel down to a level making it easy enough for me to understand. Over the last couple of years I have completely replaced VLOOKUP with INDEX and MATCH (I&M). Once I got used to it I’ve never looked back (no pun intended). I was also thrilled to discover that one can combine I&M with the INDIRECT function when faced with multiple arrays set up as named ranges–since then I’ve gone 3D with my LOOKUPs!
This is truly a well-rehearsed and well-written article which brings insight to the in-depth use of excel formulae. The examples succintly explains the use of the formula in real life work situations and will be of immense help not only to dummies but also to users who think they know all there is to know in MS Excel.
Thank you Mr. John Franco for your time and effort to put this article onto the web.
Minocher Nadir Buhariwalla
Post a Comment