How to use INDEX and MATCH in Excel?

Brief Introduction

              

        The INDEX and MATCH combo is potent and flexible, and you'll see it used in all kinds of formulas, from basic to very advanced. Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Ms. Excel for performing lookups.

    
         However, while VLOOKUP allows you to perform lookups with a single function, INDEX and MATCH requires two functions, one nested inside another. Many users find this confusing, because they aren't used to combining functions in Ms. Excel, so they avoid INDEX and MATCH. That's a shame. Combining functions is the key to more advanced formulas in Excel.

You can download free the Complete Practice of Concern Topic of Ms. Excel File, Click on the below download icon,

          This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, & following below contents we will touch in this post.

1.      Index / Match (Vertical & Horizontal Formula Checking)
2.      Two-way Lookup of Index / Match (Vertical & Horizontal Formula Checking)
3.      Index (Vertical & Horizontal Formula Checking)
4.      Match (Vertical & Horizontal Formula Checking)

            

   Use INDEX and MATCH in Excel and impress your boss. Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH, so let’s start the above contents.

1.  Index / Match (Vertical & Horizontal Formula Checking)

             This practice demonstrates the key strengths of Ms. Excel's INDEX / MATCH function that make it superior from VLOOKUP. As shown in the below image,  

              From the given information through the help of index/match combo function we easily find out the salary of given IDs, As in above practice, we use the index/match function vertically to find out the salary, While we also use the same function horizontally as shown in below image,

                  Index / Match function horizontally also, work perfectly as we see in the above example that ID 108 Salary which is $15000/ perfectly detected.

2.  Two-way Lookup of Index / Match (Vertical & Horizontal Formula Checking)

                In the above example, we use the Index/Match function to find out a variable through given information (like one cell) While in the two-way lookup of index/match function we find out the variable through given information (Like two cells) as shown in below image,

            

          As it works vertically, We can also, use the same function horizontally, see the below image,

3.  Index (Vertical & Horizontal Formula Checking)

               Only the index function is used to find out variable if there is given the Row number of targeted data, as shown in below example,

                  As it works vertically, We can also, use the same function horizontally, see the below image,

4.  Match (Vertical & Horizontal Formula Checking)

                 Only Match function is used to return the value in a given range. For example Match function lookup the value of F11 in the given range of A6: A:14, as shown in the below image,

        As it works vertically to return the value in a specific range, We can also use the same function horizontally, see the below image,