Learn Excel Now

Conquer the Fear of Excel

  • Home
    • Our Team
  • Training
    • Custom Training
      • Custom Virtual Group Training
      • Custom Onsite Group Training
      • Consulting Services
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
      • Simply Excel Pt. II
  • Resources
    • Contact
    • FAQ
    • New User Onboarding
  • Login

Making Sense of VLookUp: Quick and Easy Tips

April 27, 2016 by Tyrone Pernsley

Making Sense of VLookUp 

At Learn Excel now, we are always getting questions about VLookUp. Making sense of VLookUp requires breaking the function down and understanding the key components and what you are trying to find.

What is VLookup?

The VLookup function allows you to find data that is stored in a table from another spreadsheet or a smaller table.

There are 2 ways to enter the VLookUp Function:

  1. Go to the Formulas Tab, click the “Lookup & Reference” dropdown and select Vlookup. This will open the following dialogue box:Making Sense of VLookUp Image 1
  2. You can enter the function as a formula:
    =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])

Syntax of the VLookup Function:

Lookup_value: The value to search in the first column of the table. This can be a reference or a value.
Table_array: This is two or more columns of data. You can use a reference to a range or a range name. The values that appear in the first column are those searched by the lookup_value. These values can take many forms including text, numbers, or logical values.
Col_index_num: This is the table from which the matching value is returned. There are several different scenarios for the value’s placement:

  • If the col_index_num is less than 1, then the vlookup shows the error “#Value!”
  • If the col_index_num is greater than the number of columns in the table_array, then the vlookup shows the error “#REF!”
  • If the col_index_num is 1, then the value will be in the first column of the table_array.
  • If the col_index_num is 2, then the value will be in the second column of the table_array (and so on)

Range_lookup: This function is meant to specify whether vlookup should find an exact or approximate match of the value you search.

  • If “true” or 1 is used, then an exact is returned unless there is not an exact match, in which case the next largest value that is less than the lookup_value is returned.
  • If “false” or 0 is used, then an exact match is returned. If there are two or more values that are an exact match, the first value found is used. If no exact match is found then the error “#N/A” is returned

S0, what does that all mean?

Let’s say you wanted to find the total profits from sales to XYZ Company in the following table:

Making Sense of VLookUp Image 0

Now, in this simple table, you could just sum the individual cells under the Profit heading that correspond to XZY Company. But, if this table were hundreds of rows long, that would be impractical.

So, we are going to use VLookUp:

=VLOOKUP(“XYZ Company”,A1:E19,5,)

Making Sense of VLookUp Image 2

In this formula:

XYZ Company Acts as the Lookup_value
A1:E19 is the table_array
5 is the col_index_num
Because we are just looking to return a value, there is no Range_lookup required.

Other Helpful Tips:

  • You need to use “Ctrl,” “Shift,” and “Enter” to create brackets around an array formula rather than simply using “Enter.”
  • Keep in mind that table_array doesn’t differentiate between uppercase and lowercase letters.
  • Put the value of the first column of the table_array in ascending sort order to ensure that vlookup gives you the correct value. To set this, click on the “Data” icon on the tool bar, then select “Sort,” and finally select “Ascending.”
  • If you get the error message “#N/A”, check to make sure that the lookup_value is not smaller than the smallest value in the first column of the table_array.

We here at Learn Excel Now hope you now feel more comfortable using VLookUp. It is one of the most widely used Excel functions but it is a complicated tool. Making sense of VlookUp requires practice and use.

Like Learn Excel Now? Follow our social media pages and share our Excel content with your networks!

Kevin – Learn Excel Now

 

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-484-259-7664 or 1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In