HOW DO YOU USE MATCH INDEX FUNCTIONS IN AN EXCEL DATA WITH MULTIPLE CRITERIA?

For years, many organizations used to record their unique information on various spring files. Since an organization can have thousands of files, they used to have a room designated for files and all that involves records. Nevertheless, this bulk was reduced into a folder in excel with the introduction of the computer in various organizations, especially in MS Excel.

MS Excel

With the increased knowledge of computer around the globe, there are various advancement and discoveries made in the most used programs. One of the most dominantly used programs besides MS Word is Excel.

Now an organization can save their data in their computers. This has made discoveries emerge related to excel hence the emergence of various functions to enable efficient use of Excel. One such function is the MATCH INDEX which can be used to get some information about an employee in your organization’s database.

But how do you use this function to get that specific information about a specific employee from the database? Let’s get started.

Also Read : COMMON DATABASE SECURITY VULNERABILITIES YOU NEED TO BE AWARE OF

Using MATCH INDEX Function When Dealing with Multiple Criteria

Assuming that you want some information about an employee named John but you have 4 employees in your organization called John. That can be hectic. Normal MATCH INDEX function will just show you information about one of the Johns.

So how do you get the right information about the specific John? That’s where MATCH INDEX function with multiple criteria comes in. Follow the following steps:

  • Have an Understanding of the function

There is no function in Excel that you can just use without having an in-depth knowledge about it and so is this function. Any lookup function like MATCH INDEX requires some unique piece of information for it to provide the results you need. That’s why you need to combine maybe Last name and Date of Birth. This will increase the chance of us getting the right John.

By using different criteria like last name and date of Birth, you will come up with a unique identifier that this lookup function will rely on to give you the right information. Make an Array Formula

Make an Array Formula

An array formula is a formula that differs from normal formulas due to its syntax. You create it by inserting a normal MATCH INDEX formula and converting it into an array formula. You insert the MATCH function then around it you put INDEX function. The lookup array will always tell the Excel where MATCH function is to look for the value. You can click to here see how a normal MATCH INDEX is formed and later converted to an array formula.

  • Enter the Criteria you want to use

In this case, we were searching for the last name and date of birth of the employees named John. You can change the criterion easily without necessarily changing the formula. Click here to see how you enter the criteria. You can add more criteria to get the specific John.

  • Don’t press Enter when Done, Use Ctrl + Shift + Enter

Array formulas are a bit different from the other formulas. Every time you make your changes to the formula click Ctrl + Shift + Enter else it will display that an error occurred.
MATCH INDEX function for multiple criteria is simple to use and can save you from making simple mistakes that can cost you a lot. If you would want more enlightenment on these functions, just click here.

Also Read : TOP INTERNET BROWSERS OF 2018