Vertical Lookup or VLOOKUP is a function used in Excel to return a certain value in a column from another column in the same row. Today, we will learn everything about VLOOKUP Formula in Excel with examples.
Usually, the VLOOKUP function consists of 4 parts:
- The value you need to find
- The range in which you need to find the value and the return value
- The number of columns in the defined range containing the return value
- 0 or FALSE for an exact match with the value you are looking for and 1 or TRUE for an approximate match
Syntax
VLOOKUP([value], [range], [column number], [false or true])
VLOOKUP Formula
=VLOOKUP(lookup_value_table_array, col_index_num, [range_lookup])
Below you will find some elaboration of this formula.
Arguments of VLOOKUP Function:
Lookup_value: this is the required argument and it specifies the value needed to look up in the first month of a table.
Table_array: this is also the required argument and the data array that you need to look for. In the leftmost column, you will see the VLOOKUP function looks.
Col_index_num: This is a required argument and an integer value that specifies the column number of the given table_array that is needed to return the value from.
Range_lookup: this is an optional argument that shows what this function has to return for that it does not look an exact match to the lookup_value. You can set the argument to TRUE or FALSE, which clearly means:
TRUE – a rough match if an exact match is not found.
FALSE – an exact match if an exact match is not found then it will return an error.
Excel VLOOKUP Examples
Now, let’s move on to make some VLOOKUP formula in Excel with examples.
How to VLOOKUP from Another Sheet in Excel?
Practically, you will hardly notice an Excel VLOOKUP function is used in the same worksheet with data. Usually, you need to drag the matching data from another worksheet.
In the table_array argument, you need to add the name of the worksheet after an exclamation mark to VLOOKUP from a different sheet. For instance, below is the formula you can use to look for in the range A2:B10 on sheet 2.
=VLOOKUP(Product1”, Sheet2!A2:B10, 2)
Typing the name of a sheet is not required, you just type the formula and wait for it to appear in the table_array argument. Now, get back to the lookup sheet and choose the range that the mouse is using.
This is how you can search the A2 value in the given range A2:A9 on the Prices sheet and return a matching value from column C:
=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE
Also note that you have to put a single quotation mark when the sheet name has spaces or non-alphabetical characters, such as: ‘Price list’!$A$2:$C$9.
How to VLOOKUP from another Workbook in Excel?
When VLOOKUP from another workbook is needed, you must add the name of the workbook in square brackets before its name. for instance, the A2 value on the sheet has Prices names in the Price_List.xlsx workbook:
=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)
On the other hand, when the workbook name consists of spaces or non-alphabetical characters, you have to put the name in single quotes:
=VLOOKUP(A2, ‘[Price List.xlsx]Prices’!$A$2:$C$9, 3, FALSE)
Below are some easy-to-follow steps that help you in making a VLOOKUP formula:
- Open both files and enter the formula.
- Go to the other workbook and choose the table array with the mouse.
- Add the arguments, press the ENTER key, and your formula is complete.
Below is the visual display of the outcome:
The VLOOKUP formula will not stop working when the file is closed with the lookup table. However, now you will see the full path for the workbook you have closed:
Things to Consider about the VLOOKUP Function
We have some vital things for you to be remembered about the Excel VLOOKUP Function:
- The VLOOKUP function lets you have a non-exact match if the range_lookup is ignored.
- When the lookup column has duplicates, you will find VLOOKUP matches the first value.
- Among all the other drawbacks, this function never gives an impression of being false and this one is the biggest limitation.
- The function is not case-sensitive.
- Suppose that an existing VLOOKUP formula is in the sheet and formulas can be fake if you add a column in the table. It happens because hard-coded column index values never change by default if the columns are removed or added.
- VLOOKUP function lets you make use of wildcards, for instance, an asterisk (*) sign or a question mark (?).
Final Thoughts
The above-mentioned methods are keynotes to make VLOOKUP formula in Excel with examples. You can use any method and the answer will be as per your requirements. Keep trying new methods for Excel functions and continue exploring Excel.
