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.