Mastering the search functions in Excel is one of those super powers that make a difference when you want to work faster, avoid mistakes and manage large amounts of data. Surely you have ever encountered an immense spreadsheet and have thought: how do I locate here just the data I am looking for without going crazy? Well, for that, they are functions such as searching, searching, searching, index and coinciding, authentic lifeguards for anyone who uses Excel regularly.

In this article you will learn in detail how to take advantage of searching, index and other search formulas in Excel. We are going to explain it step by step in a simple way, with examples that you can apply in your day to day. In addition, we will tell you how to combine these functions to make complex searches, tricks to gain even more power and avoid the usual mistakes that are usually committed when using them.

Why are search functions so important?

In Excel, when managing data tables, having tools to find specific information becomes essential. Imagine that you have a list of employees with their office numbers and you should know in which room Juan Pérez works without looking one by one. Search functions automate this process, saving you a lot of time and manual errors.

Search, search (which means "vertical search"), search (horizontal search), index and coincide are the classic formulas for this type of tasks. Although each one has its peculiarities, they all share the goal of locating data efficiently.

The BUTIV function remains the most popular to look vertically within tables, while index and coincide allow much more flexible and powerful searches, especially in more complex or poorly structured tables. Knowing the advantages of each function gives you the freedom to always choose the best tool for each situation.

Key differences between search, search, index and match

Before diving into details, we will make the differences clear and when each function should be used:

• Buscad : look for a value in the first column of a table and return the result of a specific column in the same row. Just look from left to right.
• Search : does the same as Busc, but search in the first row and returns results from other ranks in the same column. Search from top to bottom.
• INDEX : Returns the value of a cell given its position (row and column) within a matrix or range. You can search both vertical and horizontally and is more flexible.
• Coincide : return the relative position of a value within a range, ideal to combine it with index.

The combination of index and coincides exceeds the limitations of Buscad and Buscar, since it allows both horizontal and vertical searches, can direct the search in any direction and does not require that the value sought to be in the first column or row.

Business by searching: the classic vertical search function

Buscia is the most used function for vertical searches in Excel. Its syntax is simple:

= Search (_busado value, range_

Let's see what each argument means:

• Value_busado : the data we want to find. It can be a text, number or reference of a cell.
• RANGO_TABLA : The cell area where Excel will seek the value and where it will return the result.
• Index_Columna : Column number within the range_table from which the value will be extracted.
• Coincidence_Aproximated : Optional. If you put true or leave it blank, Excel will seek approximate coincidences; If you use false, only exact coincidences.

For example:

= Search (B2, C2: E7, 3, True)

In this case, Excel will seek the value of cell B2 in the first column of the C2 range: E7 and will return the corresponding value of the third column of that range. If there is no exact coincidence and it has become true, it will give the closest value by default.

Trick: If you need to search in ranks instead of columns, use search , which works exactly the same but horizontally. To deepen how to take advantage of these functions, you can consult our complete guide on search in Excel.

Clear examples of search and search

So that there is no doubt, we review practical examples of each function (remember that you can take fragments of real tables and paste the formulas to test them):

Basic example of search

Suppose you have a list of densities and viscosities. You want to find the viscosity value that corresponds to a concrete density of 1:

• = Search (1, A2: C10, 2) - Look for value 1 in column A, and returns the value of column B in the same row (in this case, 2.17).
• = Search (1, A2: C10, 3, true) - Look for value 1 in column A and return the value of column C in the same row (100).

Basic example of search

• = Search ("axes", a1: c4, 2, true) - seeks "axles" in row 1 and returns the value of row 2 in the same column.
• = Search ("bearings", A1: C4, 3, false) - Look for "bearings" in row 1 and return the value of row 3 in column B.

You see that they are easy to remember formulas! But what if the column where you want to search is not left, or if you need a more versatile search?

Index and coincide: the perfect couple for advanced searches

This is where index and coinciding can give you super powers. When the limitations of Buscad leave you lying, especially if you need to search to the left, combine index and coincide opens a range of possibilities.

Let's first with the clear explanation of each function:

How does the index function work?

Index allows you to extract the value of a cell exactly wherever you want, specifying the row and the column within a range or matrix. Its syntax is:

= Index (matrix ,_fila number,)

For example, if you have a sales table and want to know what value there is in row 4 and column 2, you just have to put:

= Index (A1: C10, 4, 2)

This returns the value of cell B4. An important detail : Index can search vertical or horizontal, and is very versatile for dynamic formulas.

What is the use of matching?

Coincide, on the other hand, indicates the relative position of a value within a range . That is, he tells you what row or column is the data you are looking for. Its syntax is:

= Coincide (value_busado, matrix_buscada,)

• Value_busado : the data you want to locate.
• Matrix_Buscada : The range where you are looking for it.
• Type_Coincididad : Normally 0 for exact coincidence (recommended for most cases).

For example, to look for the position of "Juan" on a list:

= Coincide ("Juan", A2: A20, 0)

This returns the position (row number within the range) where it is "Juan."

Combining index and coinciding for powerful searches

True magic is to use index and match together . Thus you can find the crossing value of a row and column that dynamically change with your search criteria.

For example, you have a table with commercial names in rows and years in columns, and you want to know how much "Laura" invoiced in 2011. The names are not in the first column, so search does not work, but you can use:

= INDEX (RANGO_VENTAS, match ("laura", rank_nombres, 0), coincide (2011, rank_e?os, 0))

In this way, if you change the name or year, the formula returns the correct data automatically. The key is:

1. Coincide locate the position of "Laura" in the name matrix.
2. Coincide locate the position of the year in the heading matrix.
3. Index crosses those positions to access the exact cell.

This allows dynamic consultations, comparing, filtering and crossing data without limitations.

The reference -shaped index function

In addition, Index has an advanced reference variant, which can return several areas simultaneously. In this modality, you can work with several tables and search all of them with a single formula .

Its syntax is:

= Index ((area1; area2), no

For example, if you have both areas (one of colors and another of fruits), you can use this method to search both according to your criteria.

Remember : When you put several areas, use double parenthesis to avoid errors.

Practical example of searches with index and match

Suppose you manage an invoice table with number, city and date, and want to get the first invoice of each city with its number and date. The formula would be:

= ?City:? & Index (rank_facturas, coincide ("city", range_ris, 0), 1) ", date:" & text (index (range_facturas, coincide ("city", range_cades, 0), 3), "d/m/aa")

In this way, for each city, you get the necessary data in a single cell, with adequate format.

Essential tips to avoid search functions

• Always use 0 to coincide to ensure exact coincidences.
• In search , if you modify the structure, the_columine index can fail; Therefore, Index coinciding is more resistant.
• Set references with F4 when you copy formulas, so that the ranges do not change accidentally.
• Check that the values sought exists, the ranges are correct and the arguments of the functions are well defined to avoid errors such as #n/a or #ref! .

ADVANTAGES OF INDEX Coincide in front of Bus

Although Busov is widely used for its simplicity, Index coinciding has several advantages:

• It allows to search in any direction : from left to right and vice versa.
• Resist better changes in the table : if you insert columns, the formulas remain correct.
• It allows two -dimensional searches : fixed in rows and columns, ideal for complex or dynamic data.
• Work with multiple ranges or areas : Useful in advanced spreadsheets.

Practical exercises to consolidate search functions in Excel

Practice these functions with your own data or with ready examples. Here are some ideas:

1. Find the price of a product : with a list of products and prices, use search or to obtain the cost by entering the name.
2. Locate an employee headquarters : in a table with names and offices, find in which office an employee works, regardless of the placement of the columns.
3. Extract the date of the first sale in a city : combining index and coincide, you get the desired data quickly and efficiently.

Mastering functions such as searching, searching, searching, index and coinciding with Excel makes you more efficient and allows you to solve complex problems, perform advanced analysis and automate processes. Continuous practice will make you an expert, achieving more precise and fast works on your spreadsheets.

The above is the detailed content of Master Search and Index in Excel for powerful searches. For more information, please follow other related articles on the PHP Chinese website!