The VLOOKUP function has become one of the most important within Microsoft Excel. We can also affirm that it is among the Excel functions the one with the most application and use.
This function is used regularly at work, because it facilitates the search for data automatically instead of searching line by line; since this would take a lot of time to find the required data or information, and it can be even more difficult, if you are working with quite large Excel tables.
If you are looking to know and master the Excel VLOOKUP function, in this article we will explain its usefulness, syntax and examples of how you can apply them in different cases at work and save you a good amount of time.
What is the VLOOKUP function?
Buscarv allows you to search, bring data from one place to another and relate information. Basically, the vertical search function gives you access to cross two databases or tables to be able to exchange information or search from one Excel sheet to another to find out if the searched value exists or not.
How does the function work?
This function is part of the search and reference group, which also contains other search functions, but the popular and widely used one is without a doubt VLOOKUP.
At the moment of indicating the value that we are looking for to the function, it will analyze each and every one of the cells of the search column, starting from the top to the bottom and when it finds the first match with the inserted value, it will stop.
When it finds the value we are looking for, VLOOKUP will return the value of any of the data columns, as long as it is located in the same row as the found value.
In the VLOOKUP function the letter “V” located in its name tells us that this type of function will apply a vertical search, in other words, a search on a column of data.
The operation of this function is relatively simple, but it is quite common for users to encounter doubts and difficulties when they want to use it.
Preparing the data to use the function
It is vitally important that before using the VLOOKUP function you must have the data in an appropriate format, because if the requirements for using this function are not fully met, you will not be able to use it or the results will be incorrect.
In order to ensure the correct functioning of the function, the data in the Excel workbook must comply with 3 main rules.
Tabulate the data:
When we talk about tabular format, we mean that you must make sure that all the information is perfectly organized so that it allows the VLOOKUP application and performs vertical searches correctly.
Some Microsoft Excel users do not organize their information in a tabular format and for this reason it is not possible to use the function.
The following image shows an example of data that does not have a tabular format, so you will not be able to perform vertical searches with the function.
The data in the image above was arranged to be printed, but in this format it is impossible for the search function to do its job correctly.
In order to use the function correctly, it is necessary that you place the data in tabular format as we show you in the image below.
In the image above, the data has a header in each of the columns, which serves to identify them. But, it is not mandatory that you have to add headers to search with the vertical search function in Excel.
Search column located on the left
You should always keep in mind that the order of the columns is very important when you want to use the function.
You must do this because the function will always apply its search from the first column of the range you have selected.
To explain this example we will use the data from the previous image and add other data to define the search range (A2:D10), this will cause the search function to apply the search on column A and its values.
In the image above you can see how the search range that we have selected has a blue border and that the cells of the first column have a dark gray background color to express that the vertical search is going to be carried out on the first column. .
This organization that we have applied to the data will allow us to search for a name and this will give us the same name, Surname, Telephone or email as a result.
If instead we performed the search in the range (B2:D10) the function performed the search on column B.
In this search range we can obtain as results the same Surname, Telephone or email. But, it would be impossible for us to obtain the Name since said column was left out of the search range.
The only way to obtain a Name by performing the VLOOKUP search in the Surname, is by making a transformation to our data, inverting the order of the columns, as shown in the following image.
We can notice in the image that the column that has the Last Name heading has been moved to the first column of our search range; In this way we can apply the VLOOKUP function based on the Last Name and thus obtain the Name, Telephone and email.
With these examples you must be clear that when you decide to do a search with the search function you must have the data well ordered and your first search column must always be located, since it will be there where the function will start, and there is no way to modify this behavior of VLOOKUP.
Vertical Lookups Function Syntax
Speaking of function syntax, we refer to the parameters for the correct use of the function. that is, the type of data that must be used compulsorily.
The VLOOKUP function has 4 arguments that we will explain below:
Search Value (Required)
In this first argument we have to show the value to look for. Said value will be searched from top to bottom, specifically in the first column of the search range, we can add the value directly, this can be a text or numeric string. Function does not see case differences.
lookup_array_in (Required)
This argument is rather a reference where the range where the search will be applied is defined. Remember that VLOOKUP will always search in the first column of said search range.
column_indicator (Required)
This third argument refers to a numeric value that yields the column you want as the result if a match is found.
Ordered (Optional)
This last argument of the Excel VLOOKUP function is used for the type of search you want to perform, this can be an exact search or an approximate search. The value FALSE tells VLOOKUP that you want to perform an exact search; on the other hand, the value TRUE will tell the function that you want to perform a fuzzy search.
If you omit this argument for its class (Optional), the function will default to TRUE and perform a fuzzy search.
As you may have noticed, there are 3 mandatory arguments and one optional one in this function. But, since in the vast majority of cases exact searches will be performed, the value FALSE will be placed in the fourth argument.
Examples applying the function
The time has come to carry out several examples applying the function so that you can see its importance and usefulness.
In the image that we will show you below are the data with which we will make the examples
The example data will be a list of books that will be in 5 columns for each row; among which will be: Quantity, Serial, Price, Date and title.
How to search for a numeric value with the VLOOKUP function?
In the first example we want to demonstrate that we can give an indication to Excel’s lookup function of a numeric value to perform the lookup.
By applying the following formula I will be telling the function to show me the book that has an amount equal to 15.
I explain the arguments used.
- We ask vlookup to look for the number 15 in the first column of data, so we can add it directly in the first argument of VLOOKUP.
- In the second argument we indicate the data range, in this case it is A2:E10. There select from cell A2 to cell E10.
- In our third argument we tell the Excel function to reveal the fifth column of data, this corresponds to the TITLE.
- In the fourth and last argument we enter the value “FALSE” so that the function performs an exact search.
We have located our formula for the function in cell A12, and it is there that it reveals the result in the image below.
as you can see it gave me the correct result; the book that has the quantity 15 is the one of MAKEUP, and it was what we asked him to return the result with the TITLE column.
How to search for text string in VLOOKUP function?
For this example we will use the SERIAL column to search for the name of the book.
I explain the arguments used:
- Since we are looking for a text string, it is important to enclose the value in double quotes (“), otherwise the function will give us an error.
- The search range in this case starts in cell B2 because the text string that we have added for the search is in the SERIAL column. (Remember that for the search to be correct we must apply the formula in the first column on the left).
- We have placed the number 4 because it is the one we want the function to return, remember that, like the previous example, it is located in the title column.
- Finally, we indicate the fourth argument FALSE as in the previous example because we want an exact search.
This does not result
The result that it gives us is the title Technology that coincides with the serial that we were looking for.
To learn to fully master the wonderful Microsoft Excel tool with all its features and tools, we invite you to sign up for our Microsoft Excel course so that you become a professional.
