Excel: matches multiple criteria from different arrays

In computing, an array is a data holder of elements of the same type. An array can hold the data values of products, names, ages, and students in an excel sheet. To obtain a match from an array is more of getting the elements in the array that are similar or have similar characteristics.

Matching is the process or the act of comparing two or more items with similar characteristics. If the two items or objects happen to match, we call that a match. Data values in the excel sheet may also appear to match in that the values in the rows match those in the columns.

To match multiple criteria from different arrays is more of extracting the values from their original array to another new array of the same name but it is empty. It is like you are transferring the values to a new location.

The generic formula for performing an INDEX MATCH function is {=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Note that it is an array formula hence you need to press Ctrl+Shift+Enter

To come up with a match of multiple criteria from different arrays, we have to follow some of the steps below.

Step 1

Open a new excel sheet from your computer or laptop and insert some data into it. In case you have already a working excel sheet you can wait for step 2.

Step 2

In this step, we are going to match multiple criteria from different arrays. Arrays as said earlier, are placeholders of elements of the same type, in our case the arrays are the column headers that is, the name, age, and gender.

We are going to do this with the help of two functions; the INDEX function and the MATCH function. The INDEX function does the function of ordering the values while the MATCH function will do the match based on the cell in the formula.

In the formula bar or the result cell F1, write the following formula; {=INDEX (A2:A5, MATCH (1, (F1=A2:A5)*(F2=B2:B5)*(F3=C2:C5), 0))}.

This formula will change the values of the cells so that you can obtain the full match of all the values. Upon clicking the enter button you will have something like the one below.