How to Use Index Match Instead of Vlookup
NOTE: Microsoft have released the new XLOOKUP function in Office 365 which replaces Index with Match, read more about XLOOKUP here.
Why would you want to use Index with Match instead of Vlookup I hear you say? Most experienced Excel users feel at home using Vlookup, it's easy, you don't have to think about it but does it always do the job? No, not always because there is a big difference. Here's a few reasons why:-
- VLOOKUP requires more processing power from Excel because it needs to evaluate the entire lookup table you've selected. With INDEX MATCH, Excel only has to consider the lookup column and the return column which makes very large datasets fast and easy to analyse. If your spreadsheets are running slow, then Index Match is the answer.
- Vlookup is easy to break, try inserting a column inside the lookup table and the formula no longer returns the correct data.
- Vlookup searches for a value in the first column of your lookup table and returns a matching value from a corresponding column to the right, it cannot return a matching value to the left, Index Match can.
Learn About Index with Match
At least once a month I use an INDEX/MATCH formula to match and merge data from multiple Excel files. If you’re reading this, chances are you have good Excel skills and already have an idea what INDEX/MATCH formulas do. For the rest of you, here’s a short introduction (I'll keep it brief):
INDEX/MATCH formulas are created by combining Excel’s built-in INDEX function and its built-in MATCH function into a single compound formula. This is an ideal combination when you need to:
- Merge data from one Excel list into another Excel list by matching records from the two lists; or
- Use a common field from two Excel lists to lookup a second (or third or fourth) field by matching records from the two lists.
For instance, suppose you had two worksheets for the same group of customers. The first worksheet contains columns for Customer Code and Email. The second worksheet contains columns for Customer Code, Phone Number and Company. With Customer Code as the common column, you could use an INDEX/MATCH formula to add each customer’s phone number and Company to the email worksheet.
A standard INDEX/MATCH formula is written like this:
Index( value_array, Match( lookup_value, lookup_array, match_type ), column_number )
The MATCH portion returns a position in a list. The INDEX portion returns a value in a cell. So combining them together allows you to lookup a value in a cell based on the position of an item in a list. (What the formula actually does is use a MATCH function as the second argument of an INDEX function.)
Here’s an Easy Trick to Digest all of the Above
Rewrite the formula in the following way and replace the double-bracketed portions with your actual data or cell references.
=INDEX([[find this kind of value]],MATCH([[for this cell within the **first** list]],
[[with a match within this **second** list]],0))
Click the example below: customer list 1 is extracting the company and phone number from customer list 2
A few notes to bear in mind:
- If you plan to use AutoFill to copy the formula down a column, ensure that the lookup array is either a named range or an absolute reference to a range.
- You cannot refer to an entire column as the lookup array for the MATCH function; You must specify an exact cell range.
- The 0 at the end of the MATCH portion is optional and one of three possible choices (1,0,-1). 0 means find an exact match. 1 means find the highest value that matches. -1 means find the lowest value that matches. If you omit this argument, it defaults to 1.