How to find data in an Excel sheet with the CONSULTAV function

In this section we show you an interesting function to search for data in a matrix . It is ideal for high-volume database or table queries, forms, or reporting. We will explain in a simple way, how to search for data in an Excel sheet with the QueryV function . First we present you a brief overview of what you need to know before using it.

Index(  )

1. ConsultaV
2. Using ConsultaV
3. How to find data in a table

ConsultaV

The first thing to keep in mind is that if you have a version prior to Excel 2010 , it is most likely that you will not find this function. Not because it is not there, but they use it with another name, SearchV or searchH . So from version 2010 onwards, the active function is ConsultaV. If you want to know what other functions have been renamed depending on the version, you can go to support.microsoft.com .

Let’s briefly examine why both functions end in “V” . Well, that “V” indicates that the search in the data table is done vertically, by columns . Therefore, ConsultaV , is an ordered search function within an array obeying a range of data, returning the value deposited in a cell. Next, we explain how to find data in an Excel sheet with the QueryV function.

Using ConsultaV

The first thing is, familiarize ourselves with the syntax of the function. CONSULTAV (lookup_value, lookup_array, column_indicator, [sorted]) . We reveal the meaning for each of them. So you can use the syntax without problem:

• Lookup_value. Usually refers to the value of the 1st column. This value can be numeric or a text string.
• Array_search_en. Covers the range of the data table or database where you will search for the value.
• Indicator_columns. Here we will indicate the column of the table that will return the value that matches the search.
• Ordered. It is a logical value. If we put “true” it will look for data that is close to the criterion. If it is the opposite, “false” will locate the exact data.

How to find data in a table

• Previously you must have created a data table already filled in in an Excel sheet . To facilitate the explanation, we will give an example. You have a table whose columns are: ID, name, age, city.
• Besides, you will create a small table(it can be on the same sheet or in the next excel sheet). You will use this small table only so that you can visualize the data you are looking for, according to the criteria that you put. Example: It will be composed of 2 columns and 3 rows (ID, name, age and city).

Excel offers us 2 ways to use the QueryV function . The manual method is to write the syntax of the formula, since having experience in practice can be more flexible. And the method with Assistant. Through a window indicates what to put. Well, let’s get started.

1. Manual method. We will write in the 2nd column corresponding to the cell “name” the function = CONSULTAV (G8; SHEET1 | $ A: $ F; 2; FALSE) . Where G8 will be the cell that we will indicate the identity card to search for your data. SHEET1 || $ A: $ F , is the parameter of the whole table. The number 2 refers to the column where all the data corresponding to the “name” field is located. FALSE . So that you find the exact information.
2. We repeat step 1for each row of the small table, namely age and city.
3. Now when you write any identification number (contained in the large data table) in the small table, it will return the data name, age, city corresponding to the identification.
4. Wizard method. We go to the menu of “formulas” .
5. We click on “insert function”.
6. A window will appear where you will write CONSULTAV in “Search for a function“.
7. When you select it, you will see a box with 4 fields ready for you to enter the data. In “searched value” youselect the identity card. The “ Look In Matrix” points to the large data table . “Column indicator”, the number of the column where you are going to find the data, in this case 2. “Ordered” you put FALSE. And then “OK”. And ready. You can already consult.

We hope that this knowledge has been very useful in your field of work. With practice you will acquire skills and will be able to use more robust formulas to optimize the search, such as the use of the index . If you want to share other ways of searching, leave us your comments.