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/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
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
in question#11 what does +1 indicate in column array of index match formula !
Hello Minhaj,
In the formula =INDEX($B$140:$B$144,MATCH($C$146,$C$140:$C$144,1)+1), the +1 is used to return the value from the next row after the matched value.
Formula Explanation:
MATCH($C$146,$C$140:$C$144,1): This part searches for the value in cell C146 (which contains “Today’s Date”) within the range C140:C144 (the list of birthdays). The 1 at the end of the MATCH function indicates that the function looks for the largest value that is less than or equal to C146. So, it will return the position of the latest birthday before or on the current date.
+1: After the MATCH function finds the closest previous birthday, the +1 moves the selection to the next row in the range, which corresponds to the next upcoming birthday.
INDEX($B$140:$B$144,…): This part retrieves the name of the person who has the next upcoming birthday based on the MATCH result (which is adjusted by +1 to move to the next entry).
Regards
ExcelDemy