Mastering Excel Search Functions: A Complete Guide

The importance of research

Research is a fundamental part ofExcel, no matter what you use it for, you've probably already had to look for data. But doing a search can be complicated, especially when the size of the tables increases.

This is not a new problem. That is why, from its first version, Excel had functions to search for data.

Unfortunately, they are among the functions that cause the most errors and are misused by many people. Indeed, they can be difficult to understand and use, and the error messages ofExcel are not always clear.

In this article, I will help you understand how to search for Excel, how to use it, and how to choose the search function that best fits your situation.

The function Search V

We start with some of the most used and oldest functions ofExcel : The Search V (VLookup In English) allowing you to search vertically and the Search H (HLookup In English), its equivalent to the horizontal one.

Despite their 35 years of age, they remain among the most used functions still used today alongside the functions Somme & Average !

La Search V allows you to search for a value in the 1st column of a table and to extract a value from this row in the column of your choice. La Search H works in the same way, but horizontally (here we will focus on the Search V, but everything also applies to the Search H).

The function looks like this

SEARCH V (search_value; matrix; no_index_col)

• search_value: the value sought in the 1st column of the matrix.
• matrix: the data table in which we want to do our research. It should contain all the information.
• col_index_number: the index of the column in which the data you want to extract is located.

• La value sought must be in the 1st column of the table.
• La value sought is case insensitive, i.e. uppercase & lowercase, so “key” will match “Key”.
• If the value sought is present several times in the table, only the 1st occurrence will be selected.
• The value sought and the cell must have the same format, for example if the value sought is the character string 1 and a cell contains the number 1, there will be no match!

Example of the use of Search V

Let's say we have a table containing employee IDs and employee information. We can use the Search V, using the identifier as value sought And lThe index of the column containing the information you want, in order to access an employee's information.

In the example on the right, we use a Search V to search for the contents of column 4 (theaddress) of the employee with theidentifying “2".

LThe column index must refer to a column that is part of the selection, if we had entered 5 to refer to the “Telephone” column we would have had an error.

La Search V can also take another optional parameter”near_value“which allows you to choose the type of correspondence.

SEARCH V (search_value; matrix; no_index_col, near_value)

If the argument is valid Wrong the search will be accurate, that is to sayExcel will look for the key exactly, if it does not find it, it will return an error.
If the argument is valid True the research will be approximate, Excel will look for the key, if it does not find it, it will return the value Smaller the nearest.

For example, if we go back to the previous example, if we use 10 as the value we are looking for, with the exact match we will have an error, but with the approximate correspondence we will have the previous result, that is to say the one that corresponds to the ID 9.

For the approximate correspondence works correctly the table should be sorted in ascending order

Most of the time you will need an exact match. Unfortunately, The base value of the argument is TRUE, and therefore an inaccurate comparison. Be sure to add this argument if you want an accurate comparison.

The values returned by the Search V

La Search V returns either the value found or an error, the possible errors are:

• #N /A : The value you were looking for was not found. If you do an approximate search, this error will be displayed if your key is smaller than the 1st value in the table
• #REF! : The column indicated is not part of the table of the table
• #NOM! : Excel Can't find one of your arguments

You can use the function IF WRONG (IFERROR Has someEnglish) to set a specific value when there is an error.

As we have seen, the Search V is a very powerful and practical function for looking for data, but it also has flaws.

• The value you are looking for must be the first column in the table. La Search V cannot search to the left, only to the right.
• The column index is a number, If you delete, add, and move a column, the function will no longer work !
• The correspondence is approximate by default
• The search is only vertical, you have to use an H search to do it the other way around

These limitations make the Search V less flexible. They force you to structure your table in a specific way and can cause errors that are difficult to spot.

To find out more you can go see our article dedicated to V research

The function Equiv (Match In English)

One way to prevent the search from stopping working when you change the column structure is to use the function Equiv. It allows you to search for a value and return its position. It can be used to obtain the index of a column via its header and therefore no longer have to enter a column index manually.

EQUIV (search_value, search_matrix, [type])

• search_value: the value you are looking for.
• search_matrix: the table in which the value you are looking for is found.
• type (optional): the correspondence mode (by default 1)
• 0 : exact match.
• 1 : approximate correspondence. If the value you are looking for is not found, return the nearest value less than or equal. The table should be drawn in ascending order.
• -1 : approximate correspondence. If the value you are looking for is not found, return the closest value greater than or equal. The table should be drawn in descending order.

• Just like for the Search V, by default the correspondence type is approximate
• The matrix must be one row or one column

Example of using the function Equiv

Let's go back to the previous example.

Using the function Equiv To get the index of the column address, you can now delete, add, or move columns without the risk of the formula stopping working!

However, this method requires the use of two functions, which makes it a bit more complicated to read and use.

Moreover, it is not sufficient to correct the other faults of the Search V such as the requirement to have the value you are looking for in the first column of the table or the impossibility of doing a search to the left.

The combo Index / Equiv (Index/Match in English)

In order to correct other problems with the Search V, you have to use another function, the function Index.

INDEX (matrix; log_log; col_number)

• matrix: The table in which the sought-after value
• log_name: the line number of the value you are looking for
• col_name: the column number of the value you are looking for

If the matrix is a row or a column, you can use only one of the arguments No_lig and Col_number

Example of using functions Equiv & Index

By using Index and Equiv together, we can have the same behavior as with a Search V Or a Search H. It is no longer necessary to have the key in the first column of the table and it is now possible to search to the left!

This method is also faster than a Search V, which can be useful when handling large paintings.

The function Search X

But, this method still has problems, for example, the need to use two functions. This makes the formula more complicated to read and use than a Search V.

It's not really surprising, these features are over 30 years old. At the time Excel was far from being the giant he is today.

microsoft is well aware of this problem. That is why there is a short time, a new function has been developed to solve these problems and to simplify the search.

La Search X !

This function simplifies the search for Excel and combines the strengths of the Search V And the jumpsuit Index/Equiv in one simple to use function.

La Search X is only accessible under Microsoft 365 (formerly Office 365), if you are using another version, you will need to use the Search V or Index/Equiv.

This function has a lot of arguments, which can be daunting at first, but most of the time you'll only have to use a few of them.

SEARCH X (search_value; search_array; result_array; if_not_found; match_mode; search_mode)

• search_value: The value you are looking for.
• search_table: The column or row containing the sought-after value.
• array_returned: The array or values found to be returned.
• IF_not_found (optional) : The value to be displayed if the value sought is not found.
• Correspondence_mode (optional): The correspondence mode
• 0 : Exact correspondence.
• -1 : Approximate correspondence. If the value you are looking for is not found, return the nearest value less than or equal.
• 1 : Approximate correspondence. If the value you are looking for is not found, return the nearest value greater than or equal.
• 2 : Generic correspondence. The special characters “*” and “?” can replace others.
• search_mode (optional): The search mode
• 1 : Search from the 1st to the last element
• -1 : Search for the last to the 1st element
• 2 : Dichotomous research ascending: a very fast search method, but which requires having an array sorted in ascending order
• -2 : Dichotomous research descending: a very fast search method, but which requires having an array sorted in descending order

• By default the correspondence is Exact !
• No need to use IF WRONG to handle errors.
• No need to sort your table to use an approximate search
• Choose a search mode adapted can speed up your search.
• The fact of using a search chart instead of an index, adding or deleting columns no longer breaks the formula.
• Multiple values can be returned.

• If you are using a dichotomous research with an unsorted array, you won't get an error message, but you may get a bad result.
• If the two paintings search_value and array_returned do not have the same format or not the same size, there will be an error #VALEUR

Example of using the function Search X

With the Search X instead of entering the index of the column or row in which you want to search for your value sought, you must enter a reference that allows you to be independent of the change in the other columns and to be able to return the results of several rows/columns in a single function!

We have seen the various search functions From Excel. You should now be able to choose the method that best fits your needs. In a future article we will see tips to avoid mistakes and to use these methods more effectively!

To find out more

• The searchX function (Microsoft documentation)
• The Search V function (Microsoft Documentation)