What is Index and Match in Excel
Index and Match in Excel are two powerful functions that allow users to perform complex lookups based on multiple criteria. The Index function returns a value from a specific row and column in a given range, while the Match function searches for a specified value within another range and returns its position. Combined, these functions can be used to locate data within large tables or datasets with great accuracy and efficiency. Unlike VLOOKUP or HLOOKUP, which only search for matching values in a single direction, Index and Match provide greater flexibility by allowing users to match values using multiple criteria (similar to SQL queries), making them ideal for working with large and complex data sets where traditional lookup formulas may fall short. Overall, mastering the use of Index and Match can significantly enhance your Excel skills and streamline your data analysis processes.
INDEX and MATCH vs. VLOOKUP
When deciding which function to use, especially for vertical lookups, the INDEX and MATCH function is way better than the VLOOKUP function. However, below are some of the main reasons why you should use Index and Match instead of VLOOKUP function:
VLOOKUP cannot look to its left columns. That means the lookup value must always be in the first column of the table. However, Index and Match can do left lookups easily.
With Index and Match, you can insert or delete columns safely while the VLOOKUP function gets broken or returns incorrect results.
The Excel Match and Index function works faster than VLOOKUP in case you have hundreds of rows and formulas because the Match and Index just look up the index columns and returns their values rather than looking at the whole range of cells. The Excel Index and Match function has no size limit for a lookup value while the VLOOKUP function cannot exceed 255 characters. So, if your dataset contains longer strings than the limited size, the VLOOKUP ends up with #VALUE! Error.
How to use Excel INDEX and MATCH function
The excel Index and Match are two formulas that work together to return a lookup value. The INDEX function returns the value at a given location within a specific range, and the MATCH function finds the position of an item in a range.
Syntax of the INDEX function
|
1 |
INDEX(ARRAY, ROW_NUM, [COLUMN_NUM]) |
ARRAY – a range of cells that you want to return a value from.
ROW_NUM – the row number in an array from which you want to return a value. If omitted, the COLUMN_NUM is required.
COLUMN_NUM – the column number in an array from which you want to return a value. If omitted, ROW_NUM is required.
Syntax of the MATCH function
|
1 |
MATCH(LOOKUP_VALUE, LOOKUP_ARRAY, [MATCH_TYPE]) |
LOOKUP_VALUE – the number or text value you are looking for.
LOOKUP_ARRAY – a range of cells being searched.
MATCH_TYPE – specifies whether to return an exact match or the nearest match:
1 or omitted – finds the largest value that is less than or equal to the lookup value. Requires sorting the lookup array in ascending order.
0 – finds the first value that is exactly equal to the lookup value. In the INDEX / MATCH combination, you almost always need an exact match, so you set the third argument of your MATCH function to 0.
1 – Finds the smallest value that is greater than or equal to the LOOKUP_VALUE. Requires sorting the lookup array in descending order.
Syntax of Index and Match function
|
1 |
INDEX (range to return a value from, MATCH (lookup value, range to look up against, 0)) |
Example of Index and Match function
The following example uses the Index and match function to return the Continent name based on its serial number. The formula we used is:
|
1 |
=INDEX(B2:B8,MATCH(E2,A2:A8,0)) |
