Search in Excel: all the tricks

  • Excel allows you to organize data effectively and makes it easy to find specific information.
  • There are different search methods in Excel, such as filters, key combinations, and functions.
  • The VLOOKUP and HLOOKUP functions are essential for accurate searches in large tables.
  • Combining VLOOKUP or HLOOKUP functions with MATCH yields even more accurate results.

search in excel

The spreadsheets of Excel They offer endless possibilities to their users. They are not only very useful for students or professionals, they are also very practical for organizing all kinds of private affairs, from home finances to collecting and much more. But to get good performance out of all this, you need to know how to search in excel

Those who handle a more or less large volume of data know that searching Excel templates can be very cumbersome. Doing it "by eye" can take us a long time and, sometimes, it can make us desperate. Luckily, there are very practical tricks and formulas to search and find what we need in Excel. We explain everything below:

Ways to perform searches in Excel

We have already seen in previous posts that Excel has numerous formulas and functions to make our lives easier. If we talk about how to find specific elements in a table, the program offers us several ways to do it without having to leave our eyes going through rows and columns one by one. Simply, it is enough to apply the appropriate formula in each case.

Because (and this is the interesting thing) Excel has several search modes. Each of them works differently and is used to find specific data depending on each situation. Let's see what they are:

Apply value filters

excel filter

This is a simple search method, but effective in many cases. The Excel value filters They were not designed to perform this function, but we can make them serve to search for certain elements within a table.

How does it work? Suppose we have a table with the days of the week in the rows and a series of columns to the right of it that reflect the sales of a certain product according to the day. To find a specific data, we will select the column headers and, in the upper tool bar, we will press the button "Filter", displayed with a funnel icon.

A dropdown will then appear for each of the headers. Then we go to the days of the week column and select a day; then we click on the dropdown, after which a new search box will be displayed. In this way we can apply filters so that only the results of the selected values ​​are shown.

Ctrl + F keys

If we do not want to make searches that are too complex and we simply want to search Excel for a specific piece of information, we can always resort to an old trick, the Ctrl + F key combination.

Pressing these two keys simultaneously opens a search box in which we will be able to write the exact value we are looking for in the Excel table. It is an old trick that we could not pass up, since it can be very effective and sufficient in most cases.

VLOOKUP and HLOOKUP functions

searchv excel

Although the filter method can help us in many cases, there are much more direct and precise Excel functions when searching a document. Especially if this is especially extensive.

The functions VLOOKUP and HLOOKUP They are practically the same, except that we are going to use the first one to carry out vertical searches and the second one for horizontal searches. That's all. For the rest, the syntax to use to implement these functions is the following:

=VLOOKUP(lookup value; data range; column indicator; match type)

El search value is the reference box (imagining the previous example, it could be "Monday"); he data range is the number of cells on which the search is performed, which could perhaps be the entire table; he column indicator is a number to refer to a certain column* (in our example, a certain type of product); finally, the match type we will put FALSE or TRUE, depending on whether we want it to be an exact result or only approximate.

After implementing the formula, just click OK and the result will appear immediately, saving us a lot of time.

(*) If we use the HLOOKUP function, it will be a row indicator, since the search is carried out horizontally.

VLOOKUP + MATCH

Finally, we explain a way to search in Excel that can give us even more precise results. It is a very useful trick when we are dealing with large sheets where there is a high volume of data. In these situations, we can make serve the combined strength of two different functions: VLOOKUP (or HLOOKUP) + MATCH.

The MATCH function is the one that gives the exact number to insert into the VLOOKUP or HLOOKUP formula for a given element. In this case, the syntax would be this:

=MATCH(lookup value, lookup array, match type)

The result obtained by applying this formula is the one that we will use next in the VLOOKUP function that we have seen previously.


Leave a Comment

Your email address will not be published. Required fields are marked with *

*

*

  1. Responsible for the data: Miguel Ángel Gatón
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.