Excel Practice & Exercises with INDEX-MATCH Formula

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will get eleven INDEX MATCH Excel practice exercises. Most of the problems are easy to solve. You will need an advanced level of Excel knowledge to solve all the problems. To solve all the problems, you should know about the following functions: INDEX, MATCH, CONCAT, CONCATENATE, and TEXTJOIN. Additionally, you will need to learn about the following things: examples with the INDEX-MATCH formula, use of the INDEX MATCH formula, INDEX-MATCH with multiple matches, INDEX MATCH for multiple results, the sort feature, and Excel VBA. You will need the latest version of Excel (Microsoft 365) to solve all the problems.


Download Practice Workbook

You can download the Excel file from the following link.


Problem Overview

There are eleven Excel INDEX MATCH related practice exercises in this file. The datasets are all different for each of the problems. You should open the Excel file and go through the problem statements. The exercises are provided in the “Problem” sheet, and the solutions to those are in the “Solution” sheet. Moreover, the first exercise is shown in the following picture.

Problem Overview of INDEX MATCH Excel Practice Exercises

  • Exercise 01 Lookup from Left to Right: Find the price of a specific Order ID (131403).

The animated image below shows the formula to solve the first problem.

  • Exercise 02 Lookup from Right to Left: Lookup the Order ID from a specific price ($50). This time, you will search for data from the right side to the left side.
  • Exercise 03 Lookup Two Columns (Array Formula): Use an array formula to search data from two columns (Product and Order ID) and return a single output (Price).
  • Exercise 04 Two Way Lookup: Apply the MATCH function twice to find the product name from an order ID (133409) and column name (“Product”).
  • Exercise 05 Lookup with Partial Match: In this exercise, find the price of a partially matched product name (“Lap”) using a wildcard character(*).
  • Exercise 06 Multiple Criteria Lookup: Use a different formula than the exercise 03 to find the price of a product sold (“Mobile Phone”) by a specific salesman (“Aaron”). Hint: use a multiplication formula inside the lookup_array of the MATCH function.
  • Exercise 07 Retrieve Values of Entire Row: Create a formula to return the row values of a salesman named “Dexter”.
  • Exercise 08 Find Approximate Match: Find the product name from the nearest price given ($450). Remember, you need to sort your data to use this formula.
  • Exercise 09 Multiple Criteria Without Array Formula: Find the student who got 100 in both the subjects. You need to use a helper column to do this.
  • Exercise 10 Use of VBA INDEX MATCH: Apply VBA code to return the Gender and Country name for the student named “Samuelson”. You can press Alt+F11 to bring up the VBA window. Additionally, you can enable the Developer tab to do this.
  • Exercise 11 Find Upcoming Event: Five people’s names and birthdays are given. Additionally, the current date is shown. Combine the functions to create two formulas to return the name and date of an upcoming birthday.

The following image shows the answers to the first two exercises.

INDEX MATCH Excel Practice Exercises


Conclusion

Thank you for reading this article. By completing these practice exercises, we hope that you have gained knowledge about the INDEX MATCH functions of Excel. Moreover, you can find more articles similar to this on the ExcelDemy website. Additionally, if you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

8 Comments
  1. We have spent several days / weeks looking for specific INDEX / MATCH assistance with NO LUCK. Have four (4) tabs / sheets contain schedules (each with 40 rows and 15 columns). Want to create the “Summary’ schedule on the first tab. Would like to create this tab with INDEX/ MATCH functions that will connect the other 4 tabs – NO ARRAY TAB, NON-ARRAY TAB, or any other tabs JUST SUMMARY TABS AND DATASET1 AND DATESET2 AND DATASET3. PERIOD.

    It appears all Excel internet sites like yours have seminars for everything under the sun BUT the pure ans simple “Summary” tab and A DATASET1 tab – INDEX / MATCH Formula in Different Sheet – ROW & COLUMN Formula

    • Dear RICH SAUNDERS,

      I understand your frustration with trying to find specific INDEX/MATCH assistance for your Excel project. Here, I’ll create the Summary tab using INDEX/MATCH functions to connect the other four tabs.

      To begin, let’s assume that your four tabs are named Dataset1, Dataset2, Dataset3, and Summary. To retrieve data from the Dataset1 tab, use the following formula:

      =INDEX(Dataset1!$A$1:$O$40,MATCH($A2,Dataset1!$A$1:$A$40,0),MATCH(B$1,Dataset1!$A$1:$O$1,0))

      Note: Be sure to update the tab name and range of cells to match the appropriate tab and data range.

      I hope this helps! Let me know if you have any further questions.

      Regards,
      Yousuf Khan Shovon

  2. Dear Rafiul,

    Thanks for your article and I downloaded all and practiced. I found one problem on

    “Exercise 05 Lookup with Partial Match: In this exercise, find the price of a partially matched product name (“Lap”) using a wildcard character(*)”

    There are 2 results when we searched for Laptop using wild card(*) “Lap”.

    How can it be solved?

    • If you want to return the price of the second matched value, you can use the following formula that returns the value $715.

      =INDEX(FILTER(D58:D63,ISNUMBER(SEARCH(“Lap”,C58:C63))),2)

  3. Can u explain me the Question 6. I saw the solution what does 1 refers to ?

    • Question 6: You need to return the price of the Mobile Phone sold by Aaron. This question is similar to question 3. So, I have asked to use different formula to do so.

      Now, 1 is used as a logical value to identify the matching records based on the conditions specified by the formula. This enables the INDEX function to retrieve the desired value from the given range.

      (B79=B71:B76)*(C79=C71:C76) returns, {0;1;0;0;0;0}. So, when we put 1 inside the MATCH function, it will return the second value. Thus, we have obtained the value $429.

  4. For Question 11 if we add additional data with birthday as 25th November 2022, then how can we derive the upcoming birthday. I tried the solution provided but it doesn’t work and if I remove +1 from the formula, still the answer is not correct.
    New data is pasted below

    Birthday Of Date
    Robbins August 17, 2022
    Venus November 11, 2022
    Hawkings December 3, 2022
    Samuelson December 19, 2022
    Helena January 27, 2023
    Samantha November 25, 2022

    • Hello Baijul, Thank you for your question. The Date column needs to be in ascending order. You’re adding an earlier date to the end of the data, that’s why the formula is not working.

      You can sort the data or use the following formulas:

      Cell C148: =LOOKUP(2,1/($C$140:$C$145>$C$147),$B$140:$B$145)
      This returns Samantha

      Cell C149: =LOOKUP(2,1/($C$140:$C$145>$C$147),$C$140:$C$145)
      This returns November 25, 2022

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo