Microsoft Office Excel is one of the most widely used calculation tools due to the wide variety of functions and facilities it has to offer. Among them is BuscarV , and today together with the Mira Cómo Se Hace team we will teach you how to use it to find the desired value.
How to Use the VLookup Function in Excel to Find the Desired Value
Index( )
- What action is executed when using the SearchV function?
- What is the formula to be able to use the VLookup function in Excel?
- in columns
- in rows
- What mistakes are commonly made when using the VLookup function in Excel?
- Error of type #!REF¡
- Error of type #!VALUE!
- Error type #N/A
- That the column does not have unique values
- What are the limitations of the VLookup function in Excel?
- Are there other functions that can replace VLookup in Excel?
What action is executed when using the SearchV function?
The action executed when making use of this function is the search for a specific value or element found in the first vertical column of a matrix in Excel, yielding another value in the selected cell.
What is the formula to be able to use the VLookup function in Excel?
Excel functions have formulas, called syntax in some cases, which are what guide us to execute them correctly and allow access to their facilities.
In the case of the FindV function, two formulas are used that differ from each other, being one for rows and the other for columns.
in columns
Since this function is straightforward for looking up values in the first vertical column of the array, we use the official VLookup formula here, which would be VLOOKUP(lookup_value,array_lookup_in,column_indicator,[sorted]).
Where the search value is, as its name indicates, the value you want to search for; matrix_search_in indicates the cell of the matrix where you want to search, column indicator is the space destined to place the number and the range to which the column you are looking for that returns the result belongs to.
Also, the [sorted] part refers to the true value or false value , this is where you should choose FALSE to get exact results.
in rows
To use the VLookup function in rows, it is necessary to combine it with the Match function, whose formula would be MATCH(lookup_value, lookup_array, [match_type]). In which you will have to select the value you want to search for, the array that contains it and write a zero representing the match, so that it is exact.
Now, to apply it together with the FindV function, you will have to write the FindV formula, but replacing the column indicator element with the formula of the match function. Also, if you want the data you have in a row to become part of a column before applying this function, it is also possible to do so .
What mistakes are commonly made when using the VLookup function in Excel?
When you are learning to use the Excel tool and are barely familiar with its functions, avoiding certain errors is not an easy task. Among the most popular are:
Error of type #!REF¡
This error occurs frequently when an invalid cell is added as an element of the formula , because it has been deleted or was not entered correctly.
Error of type #!VALUE!
It is another very popular error for Excel users and it usually occurs for two reasons : if the formula’s lookup_value argument settles for a number of characters greater than 255, or if a value less than zero is written in column_indicator or a piece of text.
Error type #N/A
This error can occur if the value selected in the array_lookup_in argument is not located in the first column, on the left side, at the beginning of the array.
That the column does not have unique values
Among the criteria of the Excel search functions, it is expressed that the values of the column used must be unique to avoid that the operation throws errors or incorrect results. Therefore, if you have duplicate data you must eliminate them.
What are the limitations of the VLookup function in Excel?
As we have already mentioned, the FindV function only allows searches with values located in the first column of the matrix, preventing it from being useful to search for values that are found in the following columns. And even, it is not useful for searching in rows, unless it is combined with another function .
Are there other functions that can replace VLookup in Excel?
Some other functions have become popular, replacing SearchV and becoming the preferred search functions for some users. These include Match and Index, or functions like COUNTSI to consider specific data, and these are almost as convenient and easy to use as VLookup .