How to use the INDEX + MATCH formula in Excel? (+ example)

How to use the INDEX + MATCH formula in Excel? (+ example)


Acclaimed by both individuals and professionals, the Excel software offers many functions for making complex tables and performing calculations. When a user wishes to find specific data in a table, he can use the “VLOOKUP” or “HLOOKUP” functions.

> Download: Excel Chart Templates” align=”middle”/>

However, these have limitations. Thus, the alphabetical classification of the table data is mandatory and the column targeted in the search must be the first. However, these constraints do not apply to all scenarios. The combination of the two functions INDEX and MATCH can then be the solution.

Why combine the INDEX and MATCH functions?

The INDEX and EQUIV functions are complementary in the provided results. One allows you to search for data by knowing its precise location in the table, while the other displays the position of the value.

The combination of these two search functions gives more possibilities to the user. She gives him the opportunity toextract data for which only one characteristic is known and for which the line numbermandatory in the INDEX function, is unknown.

Taking the example of a customer database, the user can look for the amount of the average basket of one of them, whose name he knows, without knowing the line where he is. The INDEX+MATCH function is ideal here to meet this need.

Often, the tables produced in Excel have a large number of rows and columns, and therefore a lot of data. Unlike the functions mentioned above, INDEX+EQUIV is able to process large amounts of data. Its execution is also fast, which allows the user to gain in efficiency.

Also, the order of the columns does not matter. The search can be performed in any area of ​​the table, without any problem. Similarly, adding or deleting columns does not disturb the obtaining of the result.

See also  Marketing automation (or automated marketing)

Finally, the INDEX + MATCH function gives a wide range of possibilities since it can perform both vertical and horizontal searches. It is therefore an extremely powerful tool, very useful in many situations.

How to use INDEX + MATCH?

The MATCH function is nested in the INDEX function, thus making it possible to find the number of the line in which the final search must be carried out.

The function is written as follows:

=INDEX(Integer_array;MATCH(known_value;column_containing_known_value;type);no. of column_value sought)

Returning to the example cited above, the search focuses on the customer file and aims to find the average basket of one of them, Green & Co.

Example of using the INDEX + MATCH formula

The formula will therefore be written:

=INDEX(A1:D21;MATCH(F5;A:A;0)4)

Indeed, the search concerns the entire table, from cell A1 to cell D21. The line number is unknown and the MATCH function identifies it. The known value sought in the table is the name of the customer, Green & Co, indicated in cell F5.

The formula should look for the name of this customer in the table (in column A). The value must perfectly match the specified customer name, so the type is 0.

Finally, the value sought, corresponding to the average basket, is in column D, the fourth in the table.

See also  how to exceed 280 characters?

Both formulas therefore execute and find the sought-after value easily.

Value searched by index + equiv

HubSpot Community

To take it a step further, download these free Excel templates and use them to format your data and highlight your results.New call-to-action

Leave a Comment

Your email address will not be published.