The VLOOKUP function is used to search for a specific value within a range in the spreadsheet. Its use is simple, but requires certain knowledge, requirements and practice.
Therefore, in this article we will explain how to use the VLOOKUP function in Excel easily and quickly. In this way, you will be able to find an element in a sheet with a lot of data.
Syntax
The elements of the VLOOKUP function are:
- search_value: it is mandatory. Here you must indicate the value you want to search for and this value must be in the first column of the range of cells. For example, if the range is A2:C8, the value has to be in column A.
- table_array: is the determined range in which the required value will be searched.
- column_indicator: represents the number of the column containing the result that will appear if a match is found in the search. For example, if you are looking for the email of a specific contact, then you must write the number of the column in which said emails are located.
- either – Specifies whether VLOOKUP will search for an approximate or exact match. If you want an exact search, you must type “FALSE”. If you are looking for an approximate value, then you will have to write “TRUE”.
Its formula is “=VLOOKUP(lookup_value, lookup_array, column_indicator, )”. You can directly write the function on an Excel cell or go to “Formulas > Search and reference > VLOOKUP”.
How to use the VLOOKUP function in Excel?
To use the VLOOKUP function in Excel, you just have to insert the formula into a cell and complete the explained arguments. Below, you will see different examples according to the value you want to search for.
Search for a numeric value
To search for a numerical value with the VLOOKUP function, the example of an inventory can be used. In this case, the steps you must follow are:
- Enter all the data in the Excel sheet.
- In a separate cell, write “=VLOOKUP(”.
- Manually enter value to be searched. In this case, it is the product ID. Then, press comma (,).
- Now, hold click on the first column until the last data in the last column. This way, you will select the range. In this case it is B2:E12. Following that, press comma to continue with the next argument.
- Enter the number of the column in which the value you are looking for is found.. In this case, counting starts from column B, therefore, the “Price” column would be column 4.
- Choose “FAKE” if you like find the exact value. Otherwise, he writes “TRUE.” To finish, close parentheses and press “Enter”.
Note– This process is similar for all other examples. What will change will be the value that will be searched.
Find a cell reference
This is a more automated process since you do not have to manually enter the data you will search for. You only have to designate a cell in which the value will be entered and the formula will change according to the value that is inserted.
Continuing with the example of product IDs, define a cell for the value you will search. Then, in another box, follow the steps explained in the previous section and in “search_value”, click on the cell that has the value to search for. Everything else remains similar.
Search for a text
To search for a text with the VLOOKUP function is simple, as you just have to follow the procedure explained above, but entering the text in the “search_value” element.
For example, if in a directory you want to search for a person’s phone numberyou have to enter its name in the “search_value” and complete the rest of the arguments.
Note: Remember that any value you search for must be in the first column of the selected range. If not, the function may throw an error.
Find a date
To search for a date with VLOOKUP in Excel, it is recommended use the function “DATENUMBER”. With this function you will be able to insert the date in text format and search for it. Therefore, what needs to be done is an integration of functions.
Following the example that has been demonstrated previously, the “Entry date” column is added and the steps are carried out as explained, with the difference that the beginning of the formula should look like this: “=VLOOKUP(DATENUMBER(“ 11/10/2021”)” The date must be added in quotes so it can be valid.
As you can detail, the VLOOKUP function in Excel is useful when you handle a lot of data and want to simplify the search processes.
Facebook Comments
Disqus