Want to get your podcast sponsored and make more $ from it?
[even if it’s new or has low downloads!] Check out my free course!

Follow Lilach

Tips to Using the Excel Functions INDEX+MATCH With Multiple Criteria

Tips to Using the Excel Functions INDEX+MATCH With Multiple Criteria

Microsoft Excel has been with us for a while now. Over the years, it has evolved into very advanced stuff that is created to help businesses and people from all over the world. Today, you can even use excel as a database manager to help you keep track of certain aspects of your life. When you want to go through your records, you can either go through them one at a time, row by row. Although it will provide you with the information you seek, itโ€™s quite lengthy and tiresome. Whatโ€™s more, due to fatigue, you might miss something which is very important. Instead of using what is usually called a “normal” INDEX+MATCH Excel function, we should employ the MATCH+ INDEX functions with several criteria for correct results. And this is how to go about it.

Tips to Using the Excel Functions INDEX+MATCH With Multiple Criteria

Understanding the foundation

For instance, when building a tool which is capable of looking for a certain worker and return their salary, excel is the perfect one to use. Here, you should look for an area where you can type in the formula and select the various criteria needed. Any lookup function in excel looks for unique information which is then used to identify the target information. As such, you need to identify which pieces can serve as unique traits and use them.

Input a normal MATCH INDEX formula

Using a multiple criteria index match, you need an array formula. Array formulas donโ€™t follow normal syntax rules for most formulas. For this reason, we input a normal MATCH INDEX formula which will then be converted into the array formula.

With all the information needed, select an area outside the information and place a โ€œThick outside Borderโ€ in this area, we shall input the names of the fields which we will be using to find the information stored in our databases. For our case, this content is placed in the I column. So, โ€œLast nameโ€ will be placed I2, โ€œDate of birthโ€ in I3 and โ€œSalaryโ€ in I4. So, we place the MATCH function in the J4 cell and then put the INDEX function around the MATCH function to complete it.

So, we start with:

=MATCH(

Where we will then use the value placed in cell J2 which in our case represents the names of people. The value we placed in the cell will be looked up against all other selected cells for similar content. The lookup array helps the Excel identify where you are looking for information, followed by a comma, then the column with last names. Since the names in the employee database run from A to G, you also need to include it in the formula. So, the formula will look like this;

=INDEX(A1:G55,MATCH(J2,B:B,0))

Here from the drop-down menu, choose between 1,0 and -1.

Switching lookout value to 1

Now that you can look up one record, you might want to perform a search on various criteria. In Excel, the value 1 usually means TRUE and 0 FALSE.

So, by changing the formula to

=INDEX(A1:G55,MATCH(1,B:B,0))

We tell Excel to look for the row where all our conditions are true. If we used a 0, it would then be the other way round.

Follow Lilach

In this post:


Listen to the podcast today!

About Lilach Bullock


Hi, Iโ€™m Lilach, a serial entrepreneur! Iโ€™ve spent the last 2 decades starting, building, running, and selling businesses in a range of niches. Iโ€™ve also used all that knowledge to help hundreds of business owners level up and scale their businesses beyond their beliefs and expectations.

Iโ€™ve written content for authority publications like Forbes, Huffington Post, Inc, Twitter, Social Media Examiner and 100โ€™s other publications and my proudest achievement, won a Global Women Champions Award for outstanding contributions and leadership in business.

My biggest passion is sharing knowledge and actionable information with other business owners. I created this website to share my favorite tools, resources, events, tips, and tricks with entrepreneurs, solopreneurs, small business owners, and startups. Digital marketing knowledge should be accessible to all, so browse through and feel free to get in touch if you canโ€™t find what youโ€™re looking for!

Podcasts:


Popular Articles:


Hope you enjoyed this blog post!

If you want our team to grow your business with digital marketing, book a call.