VLOOKUP / INDEX / MATCH are created in category reference and lookup function in Microsoft Excel.
Main function of VLOOKUP is look for a value in the leftmost column of a table and retrieve data from a specific column of that table.
Using combination of INDEX and MATCH we can return a specific value like in the VLOOKUP case.
In this first example i have a data table with 4 columns:
I want to return the city of Mark (Madrid) using VLOOKUP:
Three important aspect:
- When i use the Vlookup, the lookup_value (Mark), must be the first column (column B) at the left of the table array selected
- You must have to select all the columns between the column with the lookup_value and the column that you want to return the value (in this example select range $B$1:$D$5)
- Cannot return a value, in a column that is at the left at the column that have the lookup_values
- In this example, the value returned is in column of the table_array with index 3 (column B index 1;column C index 2; column D index 3)
Making now the same exercise using INDEX/MATCH.
First the MATCH function. This function returns the relative position of an item in a array. In the next example i want to find the position of Mark in the array of names:
Mark is the name i want to find (A10), in the array of names (B2:B5). Position 4 of the array is returned:
Now the function INDEX. This function returns a value or reference of a cell using the intersection of a particular column or/and row.
I know that the city that corresponds to Mark is Madrid and corresponds to raw 4 of column D
Making a simple function using column D i get:
Position 4 of array D2:D5 returns value Madrid.
You learn that you can return the raw position using the function MATCH, position that you use in the INDEX function (number 4 in second parameter). So you can “mix” the two function returning the final value (City of Mark=Madrid):
Advantages of INDEX/MATCH:
- With this two function you have the flexibility that VLOOKUP cannot give you. It’s not mandatory that the lookup value is at the leftmost column (it can be in any place)
- If you have a big data range, the processing time is higher using VLOOKUP because in this function you must select all range between column with lookup_value and the column with the final value that you want to return (if lookup_value is in column A and final value in column Z, you must select all columns from A to Z).Using INDEX/MATCH jut need to select column A and column Z.
Excel file: index_match