To solve all the problems given in this article, you should know about the following Excel functions: **INDEX**, **MATCH**, **CONCAT**, **CONCATENATE**, and **TEXTJOIN**. You also need to know **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

## 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. The first exercise is shown in the image below.

**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:**Find the price of a partially matched product name (“Lap”) using a wildcard character(*****).**Exercise 06 – Multiple Criteria Lookup:**Use a different formula to the one used in 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, along with the current date is. 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.

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/MATCHassistance 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, andSummary. 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))`

Be sure to update the tab name and range of cells to match the appropriate tab and data range.Note:I hope this helps! Let me know if you have any further questions.

Regards,

Yousuf Khan Shovon

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)

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.

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