04 Best Examples: How to use INDEX MATCH Function Excel

04 Best Examples: How to use INDEX MATCH Function Excel


Person climbing a staircase. Learn Data Science from Scratch: online program with 21 courses

The INDEX MATCH function Excel is often used together to make a nested formula that performs lookups. For example, both-way lookup or two-way lookup, reverse lookup, multiple criteria lookup, two-dimensional lookup, or 2-D lookup, case-sensitive lookup

The INDEX function is a lookup and reference function in Excel that returns a value at the intersection of a specified row and column. Whereas, the MATCH function is a lookup and reference function in Excel that searches through a range for a specific value and returns the relative position of a value within the range. 

The MATCH function is used to return the position of the item, not the actual item. Whereas the INDEX function not only retrieves the cell content or item, but it also moves to any row or column. Thus, the MATCH function is often used in conjunction with the INDEX function to make the INDEX MATCH formula that obtains an item in the same row of a table, but from a different column. 

While a single MATCH function is used within the INDEX function either as row_num or column_num and returns a value based on the single criterion (either row or column criterion), this type of lookup is called the one-dimensional lookup.  Similarly, while two MATCH functions are used within the INDEX function- one for the row_num and another for the column_num and returns a value based on the two criteria (both row and column criteria), this type of lookup in Excel is called the two-dimensional lookup or 2-D lookup

(I) The INDEX Function

In a cell, press the equality “=” sign to start the formula and just type a few letters ‘=ind….‘ and select the INDEX function from the given suggestion list with the help of a down arrow (↓), if required. 

Syntax of the INDEX function_1

Then press the ‘Tab’ key, INDEX syntax appears with the open parenthesis.

• The syntax for the INDEX Function

Syntax of the INDEX function

The INDEX function always returns a value or item specified by the intersection of the row_number and column_number. If the array contains only one row or column, the corresponding row_num or column_num argument is optional.

• Arguments of the INDEX Function

Array or Reference is the one or several ranges, named range, or table;

Row_num is the row number in the array (if omitted, column_num is required);

Column_num is the column number in the array (if omitted, row_num is required);

Area_num specifies which range from the reference argument to use. 

(01) If the array is one-dimensional that means it contains a single row or column, then both row_num and column_num are not required. 

For example, if we want to return the fourth item within a simple one-dimensional array using the following formula: 

=INDEX ({5,10,15,20,25,30}, 4)

Or, simply using the range =INDEX (A2:A6, 4)  

Which would return a value of 20, the fourth item in this array.

(02) The INDEX function is more frequently used with two-dimensional arrays or tables, where both  row_num and column_num are required. 

For example, =INDEX (A2:D6, 4, 2)

The INDEX function retrieves the item fourth rows down and two columns over a range. That means the formula would retrieve the item from the cell B4.

(03) If the array is two dimensional, which means it contains multiple columns and rows, and one of the arguments is omitted or set to zero, the INDEX function will return the whole row or column. 

For example, =SUM (INDEX (A2:D6 , , 2))

As a result, the entire B column is totaled.

(II) The MATCH Function

Similarly, press equality “=” sign in a cell to start the formula and just type a few letters “mat…” and select the MATCH function from the given suggestion list with the help of a down arrow (↓), if required.

Remember that upper and lower case doesn’t matter for typing the function, Excel by default converts it into the upper case. 

Syntax of the MATCH function_01 Then press the Tab key, by default MATCH syntax appears with an open parenthesis.

• The Syntax for the MATCH Function

Syntax of the MATCH function

• Arguments of MATCH Function

Lookup_value is the item to match. It can be a number, text string, a logical value, or a reference.

Lookup_array is the table or an array containing all of the values to search. 

Match_type is a number that specifies how the match will be applied. 

• If match_type is zero (0), the MATCH function finds the first value/item in the array that is equal to lookup_value.

• If match_type is 1, the MATCH function finds the largest value less than or equal to lookup_value.

• If match_type is -1, the MATCH function finds the smallest value greater than or equal to lookup_value.

In the last two cases, the values in the lookup_array must be in ascending order for the MATCH function to work correctly. 

The match_type is optional and if we omit this argument, this argument is assumed to be 1.

(III) How Do INDEX MATCH Formula Performing in Excel? 

The INDEX-MATCH formula returns the value in a particular row and / or column of a given range of cells.  If the specified range of cells represents a single row, we need only specify a column reference (all of the cells are in the same row).

Similarly, If the specified range of cells represents a single column, we need only specify a row reference (all of the cells are in the same column). This single-way advance lookup is called the one-dimensional lookup.

If the specified range of cells contains both multiple rows and columns, in this case, the INDEX MATCH formula returns the value in the cell at the intersection of the specified row and column. This both-way advance lookup is called the two-dimensional lookup or the 2-D lookup.

A. INDEX MATCH Formula Performing the One-Dimensional LOOKUP:

• Syntax:

syntax of the Index Match formula is performing the one dimensional lookup
Example:
Example of the Index Match formula is performing the one-dimensional lookup
Figure: Example of the Index Match formula is performing the one-dimensional lookup

=INDEX($B$3 : $B$12, MATCH($I3, $C$3 : $C$12, 0))

We get the result for the ‘Dialing code (ISO)’ = CA-1

=INDEX($G$3 : $G$12, MATCH($I3, $C$3 : $C$12, 0))

We get the result for the ‘Project Cost’ = $41,622                                                            

If we observed both examples carefully, we find that only INDEX_array (answer area) changes in both cases. This is the key point for the INDEX MATCH formula with a one-dimensional lookup. 

B. INDEX MATCH Formula Performing the Two-Dimensional LOOKUP/ 2-D LOOKUP:

If we use two MATCH functions within the INDEX function – one for the row_num and another for the column_num, then the formula returns the value based on the matches between the row criteria and the column criteria. This type of lookup is called the two-dimensional lookup or the 2-D lookup.  

• Syntax:
Syntax of the Index Match formula is performing the two-dimensional lookup or 2-D lookup

Example:

Example of the Index Match formula is performing the two-dimensional lookup or 2-D lookup
Figure: Example of the Index Match formula is performing the two-dimensional lookup or the 2-D lookup

=INDEX($A$2 : $G$12, MATCH($I3, $C$2 : $C$12, 0), MATCH(J$2, $A$2 : $G$2, 0) )

We get the result for the ‘Dialing code (ISO)’ = CA-1

=INDEX($A$2 : $G$12, MATCH($I3, $C$2 : $C$12, 0), MATCH(K$2, $A$2 : $G$2, 0) )

We get the result for the ‘Project Cost’ = $41,622

If we observed both examples carefully, we find the two key points for the INDEX MATCH two-dimensional lookup:

(i) All the ranges selected as an INDEX_array, i.e., A2:G12

(ii) While the formula copied to another cell, only lookup_value within the second MATCH function (i.e., column lookup value) changes accordingly, e.g., J2, K2

 

(IV) What are the Merits of INDEX-MATCH Formula in Advanced Excel?

With the help of the INDEX MATCh formula, we can perform multiple activities based on multiple criteria:

• The INDEX MATCH function is performing Excel Reverse VLOOKUP | VLOOKUP to the left.

• The INDEX MATCH function is the best alternative of VLOOKUP Multiple Criteria in Excel.

• The INDEX MATCH function is the best alternative of Multiple VLOOKUP or Multiple columns VLOOKUP.

• INDEX, MATCH, and EXACT function altogether to perform a case-sensitive lookup.

 

(V) INDEX MATCH VS VLOOKUP – Which Function is Better in Excel?

➢ Don’t require to count the column number: Unlike the VLOOKUP function, we do not require to count the column numbers. Here we just select the answer column (for the INDEX MATCH formula) to get the result value.

➢ Safely inserting or deleting the columns: After inserting or deleting the columns in between the table_array, the VLOOKUP formula breaks — because the col_index_num within VLOOKUP couldn’t update automatically. Whereas the INDEX MATCH function independently works with the answer column, not with the count of columns, so it does not matter with the inserting or deleting of columns at all.

➢ Performing Reverse or Backward Lookup: The VLOOKUP function only performs to the right, whereas the INDEX MATCH formula performs a both-way lookup or two-way lookup, i.e., left and right of the lookup column. Left lookup is considered as reverse lookup or backward lookup

➢ Performing Two-Dimensional Lookup / 2-D Lookup: The INDEX MATCH MATCH formula or the VLOOKUP MATCH formula searches the match between the rows and columns criteria and returns the value. This feature in EXCEL is called the two-dimensional lookup / 2-D lookup.

➢ Performing with the multiple criteria: Another best feature of the INDEX MATCH formula is that the formula works with the multiple criteria, however, it is an array formula. To avoid array we can use the INDEX MATCH INDEX formula.

➢ Performing the Multiple Column Lookup / Multiple Lookup: Both the VLOOKUP formula and the INDEX MATCH formula are performing the multiple column lookup is called the Multiple Lookup

Suggested-Courses_Green_Advance Excel Forum
If you would like to improve your academic and professional career as well, then the below premium courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.
AEF_192by192
Advance Excel Forum

Premium Courses on ed2go

advanced excel 20194&bids=739114

Advanced Microsoft Excel 2019/Office 365 (Self-Paced Tutorial)

Bestseller
4.9/5
microsoft excel 2016 advanced training4&bids=739114

Advanced Microsoft Excel 2016

Bestseller
4.9/5
GES2084 Exec Assist MSO Master 2019 Cert Voucher4&bids=739114

Executive Assistant with Microsoft Office Master 2019 (Vouchers Included)

Bestseller
4.9/5

Premium Courses on Coursera

IBM DA Course 34&bids=759505

Data Visualization and Dashboards with Excel and Cognos

Bestseller
4.9/5
PwC fl c4&bids=759505

Data Analysis and Presentation Skills: the PwC Approach

Bestseller
4.9/5
ChartsGraphsTabletThumb4&bids=759505

Introduction to Data Analysis Using Excel

Bestseller
4.9/5

Premium Courses on Udemy

985054 e7004&bids=507388

Excel 2016 Complete Guide

Bestseller
4.9/5
918920 4cb7 34&bids=507388

Excel VBA and Macros User Forms

Top Rated
4.7/5
3057290 6754 64&bids=507388

Microsoft Excel: Beginner to Data Analysis and Dashboards

Bestseller
4.9/5
AI on Microsoft Azure: Machine Learning and Python Basics
Read-More-5

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.


Like it? Share with your friends!

Share via

Join Our Community List

Community grow with You. * VERIFY & CONFIRM YOUR EMAIL *

Thanks for your interest joining to Advance Excel Forum community.

Fill the Correct Information.

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We use cookies to ensure that we give you the best experience on our website (by analyzing).  Please see our cookies page for further details or agree by clicking the 'Accept' button.

 

Cookie settings

Below you can choose which kind of cookies you allow on this website. Click on the "Save cookie settings" button to apply your choice.

FunctionalOur website uses functional cookies. These cookies are necessary to let our website work.

AnalyticalOur website uses analytical cookies to make it possible to analyze our website and optimize for the purpose of a.o. the usability.

Social mediaOur website places social media cookies to show you 3rd party content like YouTube and FaceBook. These cookies may track your personal data.

AdvertisingOur website places advertising cookies to show you 3rd party advertisements based on your interests. These cookies may track your personal data.

OtherOur website places 3rd party cookies from other 3rd party services which aren't Analytical, Social media or Advertising.