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.
What is the INDEX function?
The INDEX function allows you to find the value of a specific cell in a table by indicating its coordinates. It is used as follows:
=INDEX(range_of_cells, row_number, column_number)
What is the MATCH function?
The MATCH function allows you to find the position of a value included in a precise range of cells. It is used as follows:
=MATCH(search_value; range_of_cells; type)
In the formula, “type” is optional. It must be replaced by 0 if the value sought must be exact, by 1 if it must be the highest value less than or equal to the value sought and by -1 if it must be the smallest value greater than or equal to the value wanted.
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.
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.
The formula will therefore be written:
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.
Both formulas therefore execute and find the sought-after value easily.
To take it a step further, download these free Excel templates and use them to format your data and highlight your results.