Table of Contents

Excel is a vital tool that is widely used in every business operation. It can easily format, arrange, calculate, analyze and organize data in a spreadsheet. Vlookup, which stands for “Vertical, look up” is an Excel built-in function. It is used to locate (look up) a value from one column of data and get the corresponding or related value from another column. For instance, if you have a product list with prices, you can easily find the price of any item of your choice. See below a step-by-step guide on how to Use VLOOKUP in Excel.

a step by step guide on how to use vlookup in excel
A Step by step guide on how to use vlookup in excel.

In easy words, the VLOOKUP function instructs Excel to “look for this item of information (for example, excel books) in this data set (a table) and give me some corresponding information about it (for example, the price of the excel books)”. Using the VLOOKUP function we can easily get to know the price of excel books. Learn below how to do this using the function VLOOKUP in Excel.

Understanding VLOOKUP in Excel

  • VLOOKUP, as its name suggests, is an Excel function used to look for a specific value by searching for it vertically in the entire sheet. The first column of a table is searched by the VLOOKUP function to find a value. Further, it returns the value in the same row in the index number position.
  • A built-in Excel function known as VLOOKUP is classified as a Lookup/Reference Function. Moreover, Excel has a spreadsheet tool for it (WS). Additionally, as a worksheet function, the VLOOKUP function can be included in a formula in a worksheet cell.
  • VLOOKUP in Excel may sound complicated, but you will find out that it is a very easy and useful tool once you try it. Moreover, look at the Cadbury Dairy Milk example below to understand manually how VLOOKUP works. 
Images

While looking at the price list,  I want to buy a Dairy milk crackle roast almond chocolate.  I also know that I want a packet of 42 gms.  Now I need to determine the price to make sure I have enough money in my purse to pay for this tempting chocolate. 🙂

example manual with cadbury on guide on how to use vlookup in excel.
manually understanding the steps involved in VLOOKUP in excel

Indeed, this was a small example. However, in a large company, if you have ever manually searched for data in Excel, you are aware of how difficult and irritating it can be. Thus, we can quickly retrieve data on a spreadsheet and return results using functions like VLOOKUP. Everything from a simple list of names to interactive financial models can benefit from this. Lookup Formulas can help us save a lot of time and make the most of Excel’s full potential. Every Excel user can profit from these fantastic features.

VLOOKUP Formula in Excel

The VLOOKUP formula in excel above in easy words is instructing you to “search for a piece of information, in the following region, and provide some corresponding data from another column,” 

The following arguments are used by the VLOOKUP function:

Lookup value (mandatory parameter) – The value that we want to search up in the first column of a table is specified by the Lookup value.

Table array (mandatory parameter) – The data array that is to be searched is the table array. The leftmost column of this array is where the VLOOKUP function looks.

Col index num (mandatory argument) -It specifies the column within the given table array from which you wish to retrieve a value. It is an integer.

If the lookup(elective argument) -The value is not exactly matched by the function’s return value, range lookup (optional argument) specifies what the function should return. When the parameter is set to TRUE or FALSE, the following results:

TRUE – Approximation match, meaning use the closest match below the lookup value if an exact match cannot be obtained.

FALSE – Exact match, which means that if an exact match cannot be identified, an error will be returned.

vlookup

vlookup formula in excel
vlookup formula in excel

Learn to use VLOOKUP in Excel in a pro manner

Step 1: Arranging the data

Firstly, to use the VLOOKUP function make sure that your data arrangement is perfect and suitable to use the function.

VLOOKUP executes in a left-to-right manner. Equally important, you must be sure that the information you want to look up is to the left of the related data you want to find.

“Bananas” are listed in the leftmost column in the above VLOOKUP example. The “good table” may quickly run the function to search for “Bananas” and return their price. You can see that there is an error message in the “bad table” example because the columns are not in the correct order.

Step 2: Using the function what to lookup

Furthermore, we tell Excel what to look for.  We execute by typing the formula “=VLOOKUP(“ and then choose the cell that contains the information we want to search. In this example, we found the cell that contains “Bananas”.

vlookup formula in excel
vlookup in excel
vlookup formula in excel

Step 3: Tell the programme where to look.

First, we choose the table containing the data. Next, we instruct Excel to look for the data we chose in the previous step in the leftmost column.

For instance, from column A to column C, the entire table is marked in this period. Excel will search column A for the data we instructed it to look for.

vlookup formula in excel
step by step guide on how to use vlookup in excel
vlookup formula in excel

Step 4: Instruct Excel on what column to output the data from

Additionally, we need to tell Excel which column has the data that we want to find as an output from the VLOOKUP.  To do this, Excel needs a number that relates to the column number.

In the above example, the output data resides in the 3rd column. Hence, the number “3” enters into the formula.

step-by-step guide on how to Use VLOOKUP in Excel.
vlookup formula in excel
step-by-step guide on how to Use VLOOKUP in Excel.

Step 5: How to find an exact/approximate match

To tell Excel whether you’re seeking an exact or approximate match, you must do the following. By inserting “Yes” or “False” in the Excel VlOOKUP formula, this happens.

In the VLOOKUP example mentioned above, we sought an exact match (“Bananas”). We enter “FALSE” in the formula as a result. If “TRUE” has been used, we would have received a close match.

Only when looking up a precise number that might not be in the table is an approximate match useful. Take the number 2.9585, for instance. Excel will now search for the value that is closest to 2.9585. Yet, the dataset does not contain that number. It helps to prevent mistakes in the VLOOKUP formula.

A VLOOKUP example with multiple values

Use this easy illustration to use VLOOKUP in excel with multiple values:

Retail worker Sarah wants to make a table using her “Helper” values along with the “Product,” “Region,” and “Prices” columns. She makes a table similar to this one by including distinct rows for each value:

A VLOOKUP example with multiple values
vlookup formula in excel
A VLOOKUP example with multiple values

She wishes to include the terms “Shoes” and “New York” in her formula in this instance. Her VLOOKUP formula finishes after using the following syntax:

vlookup formula in excel syntax

The cell values B2 and C2 that Sarah wants to seek are defined by this syntax. The table search range is then specified, running from A3 in the top-left to D5 in the bottom-right. Finally, the syntax selects a search row and instructs the formula to look exclusively for exact matches. When the formula is used, it either outputs the exact terms Sarah sought or, if these values are not present elsewhere in the table, an “Error” message.

Excel VLOOKUP from another sheet

Consider a spreadsheet with an employee’s name, employee ID, salary, and position as an example. On another worksheet in the same workbook, copy the result table.

Excel VLOOKUP from another sheet
Excel VLOOKUP from another sheet

The VLOOKUP formula in the result sheet does not close. Cell A2 gets chosen as the lookup value.

chosen lookup value to search in another sheet
Excel VLOOKUP from another sheet

The table array is now on a separate sheet. Choose the Data Sheet.

choose the data sheet 
vlookup in excel
Excel VLOOKUP from another sheet

Look at the table array’s formula right now. Further to the table reference, it also includes the name of the sheet.

table array's formula in vlookup in excel
table array’s formula in vlookup in excel

The sheet name does not need to be manually typed in this case. When a cell on another worksheet is selected, an automatic display of the sheet name and cell reference for that sheet appears.

Use the F4 key to lock the range after choosing it in the various worksheets.

You don’t need to return to the worksheet where you are applying the formula; you can do it right now. The equation leaves the column of worksheet empty. Enter the range lookup type and column reference number.

Enter the range lookup type and column reference number in vlookup in excel
Enter the range lookup type and column reference number.

Possible errors in the VLOOKUP function in excel

  • The VLOOKUP function in Excel is a strong and practical function that can help you narrow down your data to a specific item. To narrow down the information in your spreadsheet to a specific search item, you sort of combine database functionality and search engine simplicity.
  • Despite its impressive capabilities, VLOOKUP can occasionally be frustratingly challenging to utilise correctly. When you know the data is on your sheet but can’t seem to get the VLOOKUP function to properly retrieve the data, this frustration is often the worst. Worry no more, though, since the following article offers a thorough strategy for handling the eight most typical VLOOKUP mistakes. 
  • The table array to be searched does not contain the lookup value in the first column.
  • Your lookup didn’t yield an exact match.
  • Several values appear in your table array.
  • In spite, it’s a #VALUE that you’re getting! The return value for the lookup has a bug.
  • A REF is granted to you! The return value for the lookup has a bug.
  • A sizable floating-point number makes up the lookup value.
  • Compared to the lowest value in the table array, the lookup value is lower.
  • Equally important is that if you neglected to update the table reference, although the table array reference has changed.
vlookup in excel

Key points to remember about the VLOOKUP in Excel

  • If no range lookup is given, the VLOOKUP function permits a non-exact match. However,  if one is available, it will use an exact match.
  • The function’s primary flaw is that it always looks to be right. It will get data from the columns in the table that are next to the first column.
  • If the lookup column contains duplicate values, VLOOKUP will only match the first value.
  • The case has no bearing on the function.
  • Consider an existing VLOOKUP formula in an excel worksheet. If we add a column to the table in that case, formulas can fail. This is true because when columns are added or removed, hard-coded column index values do not immediately update.
  • Wildcards, such as an asterisk (*) or a question mark (?) can be used with VLOOKUP.
  • Let’s say the function in the table we are using has numbers supplied as text. That is irrelevant if all we are doing is reading numbers as text from a table column. But, if the lookup value is not likewise in text format and the first column of the table contains numbers input as text, we will receive a #N/A! error.
  • The #N/A! error appears when the VLOOKUP function cannot locate a match.

Vlookup in Excel -Tips and Tricks

vlookup in excel tricks sticker

Furthermore, it’s critical to comprehend a few tips and tactics to utilise Excel Vlookup to its full potential. In addition, the Key pointers and advice include the following:

  • Practise consistently
  • Use online tools
  • Register with forums and communities
  • Get practical experience

Vlookup in Excel- Exercises and Practice

Regular practice is one of the finest methods to advance your Excel Vlookup abilities. Likewise, online practice exercises are widely available and can help you improve your abilities. The following are some of the top techniques and exercises too :

  • complete instructions
  • Learn to code.
  • Take tests online
  • Activate projects
  • download pdf

Points to consider while using Excel’s Vlookup alternative

  • An INDEX MATCH function is preferable to a straightforward VLOOKUP function.
  • The INDEX MATCH has a right-to-left lookup option.
  • Safely add and remove columns.
  • The VLOOKUP function has no size restriction on lookup values. Your lookup criteria should not be more than 255 characters. Otherwise, Excel’s #Value error will be used.
  • Faster processing than a regular VLOOKUP.

Google Sheets and Excel’s VLOOKUP function (same or different)

The idea and syntax of VLOOKUP are essentially the same in Google Sheets and Excel. There won’t be any issues if you go from Google Sheets to Excel. Yet, there are some differences, of course.

How to master the skills of VLOOKUP in Excel

You might have now learnt the step-by-step guide on how to use VLOOKUP in Excel. Move ahead and impress your boss by upskilling other Excel dynamic formulas besides the VLOOKUP formula in Excel. There are  IF, VLOOKUP, INDEX, and MATCH functions and many more to do excel in Excel.

Similarly, one can also learn different courses to master all the skills of Excel. Likewise, the courses that are perfect for the best future careers offer overall in all the career sectors. A few of them are as below: 

  • Henry Harvin Education
  • Coursera.org
  • Mygreatlearning.com
  • Udemy.com

Yet, one might gain the advantages to outperform in every career path with the help of the most recent in-demand courses from Henry Harvin®.

Ratings 

9.9 out of 10

henry harvin logo
henry harvin education

Henry Harvin is undoubtedly one of the best e-learning platforms. As a result, it provides a range of diplomas, certificates, and degrees for different courses. Additionally, it offers noteworthy course material with the aid of lecturers who have expertise in the relevant industry. As a result, Henry Harvin® is also among the Top 30 Most Trusted Businesses according to Insight Success.

Indeed, Henry Harvin is as highly known for his superior craftsmanship and creativity. As a result, it started operating in July 2013. Despite this, it has more than 600 courses to offer and ranks among the Top 500 global Edtech enterprises. It is therefore accompanied by a range of lectures, 3000+ practice questions, study aids, video lessons, and more.

Teaching mode

Online illustrations are accessible from anywhere.

Course-Duration

Depending on the course’s content, the course may last 24 hours or more than 80 hours of online sessions.

Fee

INR 10000 to 85000 in total fees, depending on the course.

Projects

Projects, In addition, one may have projects in the chosen courses.

Internship

You’ll undoubtedly get some of the best internship support for the full educational experience.

Certification

A Hallmark Certification from Henry Harvin®, a Govt. of India-approved and Award-Winning Institute, might, on the other hand, aid in demonstrating expertise.

Moreover, Jain University offers degrees for a handful of courses.

Placement

Nevertheless, 100% Placement Assistance (1 Year) following successful course completion

E-learning

Of course, having access to many techniques, tools, videos, and other resources.

Bootcamps 

Without a doubt, boot camps over the subsequent year were in response to the demands from the related sector.

Hackathons

Free access to #AskHenry hackathons is also available.

Membership

Moreover, make sure to sign up for Henry Harvin1-Year ®’s Gold Membership for prestigious courses.

Salary Hike

150% salary increase cap for placement

Partner Recruiting

2100+ partners, including Paytm, Concentrix, Accenture, Netflix, etc.

WhatsApp

9891953953 to get in touch with us.

Address

Henry Harvin Asia Pacific Head Office, Henry Harvin House, B-12 Sector-6 Noida (UP) 201301

Other centres

Delhi, HyderabadMumbai, Noida, and other cities have Henry Harvin centres.

advanced excel course from Henry Harvin – learn how to earn

Henry Harvin® has trained more than 4,60,000 students and provides more than 7,000 classes each month. Furthermore, there are 130+ institutions and more than 210+ corporate clients globally who use it as a recognised training vendor. Moreover, Henry Harvin® offers 600+ upskilling and reskilling training programmes in over 27 different categories. Moreover, the following courses in Excel and related subjects are :

Recommended reads

Conclusion

Many users are afraid of Excel. They struggle to understand concepts like PivotTables, VLOOKUP, and circular references. Yet, you can perform all of these things and more once you have a thorough knowledge of Excel.

If you’re learning Excel, you must have a solid understanding of the widely popular VLOOKUP in Excel. The fact that this function is simple to understand is its greatest advantage. VLOOKUP searches the data table for a match, locates it, and displays the accurate outcome.

Don’t forget to share this with your buddy :). Doing so will help everyone utilise their time more effectively, win over the bosses, and provide the most excellent spreadsheets for clients. 

FAQs

Q1. What is the VLOOKUP Function, and what are its limitations?

 A1. The correct response is: “VLOOKUP is an Excel LOOKUP function used to fetch data from one table to another based on the lookup value supplied. 
A separate worksheet or workbook may contain tables. It, therefore, makes no difference whether the Excel worksheet it is in. If the lookup value is present in both tables, the VLOOKUP function can get the data.
The VLOOKUP function’s restriction is that “it can fetch the data only from left to right.” VLOOKUP cannot collect the data from right to left if the necessary column is located to the left of the lookup value column.

Q2. Are there any alternatives to VLOOKUP that are available?

A2. Yes, there is a workaround for this restriction. There are several options, but combining the excel “Index MATCH” function is a popular and frequently used option. 
The result can be fetched for us by this function.

Q3. How do pivot tables work?

A3. The Excel tool used to quickly summarize the data is called a pivot table. With just a few drag-and-drop operations, PivotTable enables us to reveal the meaning behind the facts. 
The totals of each of the multiple input items combined into one will be added to produce the summary report. Moreover, the summary can be provided in terms of “SUM, AVERAGE, COUNT,” etc.

Q4. How do Excel tables vary from regular data tables, and what are they?

A4. Excel tables are structured reference objects that expose the user to several characteristics rather than simply being a collection of cells with data in them.
Tables differ from regular random data sets in that any data additions or deletions in Excel tables will immediately affect the data range made available to the formulas and pivot tables.

E&ICT IIT Guwahati Best Data Science Program

Ranks Amongst Top #5 Upskilling Courses of all time in 2021 by India Today

View Course

Join the Discussion

Interested in Henry Harvin Blog?
Get Course Membership Worth Rs 6000/-
For Free

Our Career Advisor will give you a call shortly

Someone from India

Just purchased a course

1 minutes ago
Henry Harvin Student's Reviews
Henry Harvin Reviews on Trustpilot | Henry Harvin Reviews on Ambitionbox |
Henry Harvin Reviews on Glassdoor| Henry Harvin Reviews on Coursereport