INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)

Get FREE Advanced Excel Exercises with Solutions!

While working with large Microsoft Excel, now and then, we have to search for specific information in our large Excel worksheet. But, it’s tiresome to search for it manually. A formula collaborating the INDEX and MATCH functions can do the amazing work of searching data very easily. It can also perform advanced lookups. Today, in this article, I’ll show you the simple and effective ways to use the INDEX MATCH formula multiple criteria with a wildcard in Excel.


INDEX MATCH Multiple Criteria with Wildcard in Excel: 2 Easy Steps to Apply

Let’s assume a scenario where we have an Excel file that contains information about several sales representatives of Armani Group. We will use the INDEX and MATCH functions multiple criteria with wildcard. To do that, we will also use the Data Validation command. Here’s an overview of the dataset for our today’s task.excel index match multiple criteria wildcard


Step 1: Create a Drop Down List to Apply INDEX MATCH Multiple Criteria with Wildcard

First of all, we will create a drop-down list by using the Data Validation command to use the INDEX MATCH functions multiple criteria with a wildcard. This is an easy and time-saving way. Let’s follow the instructions below to learn!

  • First, select cell E5.

Create a Drop Down List to Apply INDEX MATCH Multiple Criteria with Wildcard

  • After that, from your Data tab, go to,

Data → Data Tools → Data Validation → Data Validation

  • As a result, a dialog box named Data Validation will appear in front of you. From the Data Validation dialog box, firstly, select Settings Secondly, select List from the Allow drop-down list. Thirdly, type John, David, Oscar in the Source typing box. Hence, press OK button.

Create a Drop Down List to Apply INDEX MATCH Multiple Criteria with Wildcard

  • After clicking on the OK button, you will be able to create a drop-down list by using the Data Validation command which has been given in the below screenshot.

Create a Drop Down List to Apply INDEX MATCH Multiple Criteria with Wildcard


Step 2: Combine INDEX and MATCH Functions Having Multiple Criteria with Wildcard in Excel

After creating a drop-down list, we will apply the combination of the INDEX and MATCH functions to solve multiple criteria with wildcard. We can easily do that by using these two functions. Let’s follow the instructions below to learn!

  • First of all, select cell E7.

Combine INDEX and MATCH Functions Having Multiple Criteria with Wildcard in Excel

  • Further, write down the INDEX MATCH formula in the Formula bar. The formula in the Formula bar is,
=INDEX(D5:D14,MATCH($E$5&"*",B5:B14,0))

Formula Breakdown:

MATCH($E$5&”*”,B5:B14,0)

$E$5&”*” is our lookup value where the asterisk is a wildcard character that represents any number of characters starting with John. B5:B14 is the reference cells. 0 is used for the exact match. The formula returns 2 first.

INDEX(D5:D14,MATCH($E$5&”*”,B5:B14,0))

Lastly, the INDEX function returns $72,568.00 which is in the 2nd row in the range D5:D14.

  • Hence, simply press ENTER on your keyboard and you will get the Revenue earned by the sales representatives name’s starting with John. The return of the functions is $72,568.00.

Combine INDEX and MATCH Functions Having Multiple Criteria with Wildcard in Excel

  • After that, AutoFill the functions to get the Revenue Earned by those sales representatives whose names start with John which has been given in the below screenshot.

  • Now, we will change the name from the drop-down list to check the INDEX and MATCH functions with multiple criteria with wildcard. We will check the Revenue Earned by those sales representatives whose names start with David which has been given in the below screenshot.

Combine INDEX and MATCH Functions Having Multiple Criteria with Wildcard in Excel


Things to Remember

➜While a value can not found in the referenced cell, #N/A error happens in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to use the INDEX and MATCH functions multiple criteria with wildcard will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel

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.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

1 Comment
  1. Very clear explanation, but how do I autofill the functions to get the revenue off the others who’s name starts with John or David or whatever? What if there are more people who’s name starts the same? What is the formula in cel E8 for instance.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo