VLOOKUP Formula In Excel: How To Use VLOOKUP In Excel?

Microsoft Excel can be tricky to use if you’re unfamiliar with certain Excel formulas that can automatically calculate values for you. With Excel, you don’t have to manually calculate different data. You can use various VLOOKUP formulas in Excel to sort data automatically. 

These formulas can help you perform addition, subtraction, multiplication, division, averages, and percentages. They can also be used to manipulate date and time values in the sheet. The VLOOKUP formula in Excel is also used to search the sheet for a certain value. In this blog, we will learn these formulas with examples, how to use them in Excel, and more.

What is a VLOOKUP Formula?

VLOOKUP stands for Vertical Lookup and is a formula used in MS Excel sheets. It helps you look for a value vertically across the sheet. This function can also be used to pull data within a large segment of information to another table for use. It is one of the most used formulas in MS Excel today. Simply type in VLOOKUP in the insert function tab along with the specified row or column to use this formula.

This formula can be used in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000. 

VLOOKUP Syntax

The VLOOKUP syntax is:

 =VLOOKUP([value], [range], [column number], [false or true])

or to be more precise,

=VLOOKUP(]lookup value], [range containing the lookup value], [the column number in the range containing the return value], [Approximate match (TRUE) or Exact match (FALSE)]).

To use this VLOOKUP formula, you need to present the following 4 pieces of vital information, or “arguments”:

• The value you want to look up (known as the lookup_value).
• The range of cells in which you find this value that makes up the table (known as the table_array).
• The number of columns – to retrieve a result. This contains the return value (otherwise known as the column_index).
• The match mode where 0 or FALSE comes up for an exact match and TRUE comes up when it is an approximate match (range_lookup where the value TRUE = approximate and the value FALSE = exact).

Use of VLOOKUP Formula in Excel with Examples

Here’s how to use VLOOKUP in Excel sheet:

1. First, organize all your data effectively. This will ensure your data is suitable for using this function.
2. You can use this formula to tell the function that you are looking for. Start by typing the formula =VLOOKUP and then selecting the cell that contains the information you want to look up. 
3. Next, select the table where the data is located. It tells the Excel sheet to search for the value in the leftmost column in the data that we selected in our previous step.
4. After that, you need to tell Excel which of the columns contain the data that you are looking for to get an output from the VLOOKUP.  It’s a good idea to provide the Excel sheet with a number that corresponds to the column number in the table.
5. The final step is to tell Excel if you’re looking for an exact or approximate match by entering “True” or “False” in the formula.

Example –

Let’s say we have a list of vegetables on our sheet with the amount in stock along with their current prices. We want to find the price of an onion from this list so we first select the cell, then type in:

=VLOOKUP (B12; A2:C10;3;0) and hit ENTER.

Where,

• B12 is the value we want to look up, which in this case, is onion.
• A2:C10 is the range in which we want to find our value.

We then type in the number of columns in this range, which is 3 in this case. This is then followed by the number 0 or FALSE to get an exact value within the range. You can learn more about this feature in Excel by taking this advanced Excel course.

How to do VLOOKUP in Excel from Another Sheet

Now that you know how to use VLOOKUP in Excel, let’s see how you can use this formula to pull matching data from a different worksheet. To use the VLOOKUP formula in a different Excel sheet, first put the worksheet’s name followed by an exclamation mark in the table_array argument before the range reference

Example – Let’s say to search in the range A2:B10 on Sheet number 2, you can use this formula:

=VLOOKUP(“Product1”, Sheet2!A2:B10, 2)

Then you can simply start typing the formula and when it comes to the table_array argument, you can switch back to the lookup worksheet. However, don’t forget to select the range using your mouse.

To look up the A2 value in the range A2:A9 on the Quantity worksheet and return a matching value from column C:

=VLOOKUP(A2, Quantity!A2:C9, 3, FALSE)

Also Read: AVERAGE Formula in Excel

VLOOKUP in Google Sheets

Here are some things you should remember while using VLOOKUP in Google Sheets:

• It is case-sensitive and cannot distinguish between lowercase and uppercase alphabets.
• It always searches for the value in the first leftmost corner of the sheet within a range.

The syntax for using the VLOOKUP function in Google Sheets is as follows:

VLOOKUP(search_key, range, index, [is_sorted])

search_key: This is the value to search for in the first column of the range as a unique identifier. For example – You can search for the word “orange” in cell 9 or the value in cell B2.

Range: It is two or more data used for the search of the upper and lower values to consider for the search.

Index: The index is the column with the return value of the range. It must be returned as a positive integer.

is_sorted: This indicates whether a LOOKUP column value is TRUE or FALSE, where:

• FALSE = This indicates an exact match. This also means no sorting is required and is recommended.
• TRUE = This indicates an approximate match. This is the default if is_sorted is unspecified and looks for the next closest match.

Return value: The first matched value from the selected range.

The Benefits of Using VLOOKUP in MS Excel

Excel has many advanced formulas that you can learn to make your work easier and more efficient. While that is true, there are many benefits of using the VLOOKUP formula as well. They are as follows:

• It saves time in finding the values you are looking for.
• It can be used to find corresponding data in an adjusting column of your sheet.
• This single formula can be used to find values which makes it quite helpful.
• It can easily give you the perfect or the next similar match.
• This formula can be used to find correlated data.
• It can be used to change a certain value in one worksheet while automatically being updated in all the other relevant places.
• It is used for advanced business analysis or data analytics where this function is a staple toolkit.

VLOOKUP and HLOOKUP in Excel

Now, let’s take a look at the comparison between the VLOOKUP and the HLOOKUP formulas which will come in handy during your work.

• The H in HLOOKUP stands for ‘Horizontal’ while the V in VLOOKUP stands for ‘Vertical.’
• While HLOOKUP might perform essentially the same function as VLOOKUP, it even goes a step further by looking up data that has been formatted by rows. 
• You can use the HLOOKUP formula when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows while you can use the VLOOKUP when your comparison values are located in a column to the left.
• The syntax for HLOOKUP is (lookup_value, table_array, row_index_num, [range_lookup]) while the syntax for VLOOKUP is VLOOKUP (lookup_value, table_array, col_index_num, range_lookup). 

Reverse VLOOKUP

The VLOOKUP function is used to search values from left to right in an array table. This requires the lookup value to always stay on the left side of the target value. 

In certain instances, you might know the target value and would want to find out the lookup value in reverse. This is where you will need to use VLOOKUP backward in Excel. It can also be called Reverse VLOOKUP.

To use Reverse VLOOKUP, it’s important to understand that match tells the index the position (or the cell number) of a value in a column or row, while the index helps return that value using that position (or the cell number).

This Reverse VLOOKUP formula can be used for:

• Finding values in the reverse order.
• Finding values from right to left.
• Finding values from bottom to up.

Conclusion

You are bound to come across MS Excel if you are working in an organization and by learning the VLOOKUP formula in Excel with examples, you won’t be intimidated to use the spreadsheet loved by many. This is one of the most useful functions of MS Excel and can be used for advanced data analysis as well. Working with this formula can put you through ten steps ahead of others by helping you sort data efficiently.

FAQs