What does the vlookup function do?

I know, "VLOOKUP function" sounds like the geekiest, most complicated thing ever. But by the time you finish reading this article, you'll wonder how you ever survived in Excel without it. Microsoft Excel's VLOOKUP function is easier to use than you think. What's more, it is incredibly powerful, and is definitely something you want to have in your arsenal of analytical weapons. How does VLOOKUP work? VLOOKUP stands for "vertical lookup." In Excel, this means the act of looking up data vertically across a spreadsheet, using the spreadsheet's columns -- and a unique identifier within those columns -- as the basis of your search. When you look up your data, it must be listed vertically wherever that data is located. The formula always searches to the right. When conducting a VLOOKUP in Excel, you're essentially looking for new data in a different spreadsheet that is associated with old data in your current one. When VLOOKUP runs this search, it always looks for the new data to the right of your current data. For instance, if one spreadsheet has a vertical list of names, and another spreadsheet has an unorganized list of those names and their email addresses, you can use VLOOKUP to retrieve those email addresses in the order you have them in your first spreadsheet. Those email addresses must be listed in the column to the right of the names in the second spreadsheet, or Excel won't be able to find them. (Go figure ... ) The formula needs a unique identifier to retrieve data. Th...

Approximate and exact matching is supported, and wildcards (* ?) can be used for partial matches. How to use VLOOKUP function in Excel The first questions I hear from people are “how does VLOOKUP work?” and “how to do VLOOKUP?” The function retrieves a lookup value from a table array by matching the criteria in the first column. The lookup columns (the columns from where we want to retrieve data) must be placed to the right. It’s important to understand the VLOOKUP function syntax. There are four arguments: =VLOOKUP(lookup_value, table_array, col_index_num, match_type) lookup_value: the value you are trying to find in the first column of the table table_array: the table containing the data (the Excel lookup table) col_index_num: the column number in the table that contains the value you want to return match_type: true = approximate match; false = exact match I strongly recommend downloading the free Excel VLOOKUP example file, as this VLOOKUP tutorial will be based on the data provided inside. Let’s look at how the VLOOKUP function works step by step using an easy example. When you want to write your VLOOKUP formula, you need to follow the four-step process: • Identify which is the value that you want to use (finding the lookup value); • Understand for which data to perform the VLOOKUP (selecting the table array); • Select which info you wish to retrieve (selecting the col_index_num); • Determine if you need to use an exact or approximate match (setting the match type). Th...

Vlookup Syntax & Rules This page provides details of the Vlookup function syntax and rules of use. You might find it useful to bookmark this page to refer back to, during later sections of the tutorial. This will be followed by practical examples of the Vlookup function to help you to put this information to use. Vlookup Syntax The syntax of the Excel Vlookup function is: VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] ) where the function arguments are: lookup_value - The value that you want to search for. table_array - The array of data that is to be searched for the lookup_value.Note: The Vlookup function searches in the left-most column of this array. col_index_num - An integer, specifying the column number of the supplied table_array, that you want to return a value from. [range_lookup] - An optional logical argument that describes what the function should return in the event that it does not find an exact match to the lookup_value. The [range_lookup] can be set to TRUE or FALSE, meaning: TRUE - If an exact match is not found, use the closest match below the lookup_value. Note: if this option is used, the left-hand column of the table_array must be in ascending order. FALSE - If an exact match to the lookup_value is not found, return an error. If the [range_lookup] value is omitted, it uses the default value of TRUE. A reminder of the Vlookup syntax, in the form of a handy Vlookup Quick Reference Card, can be printed from the Vlookup Rules It is impo...

• Courses • Pricing • Free Courses • Power BI Course • Excel Power Query Course • Power Pivot and DAX Course • Excel Dashboard Course • Excel PivotTable Course – Quick Start • Advanced Excel Formulas Course • Excel Expert Advanced Excel Training • Excel Tables Course • Excel, Word, Outlook • Financial Modelling Course • Excel PivotTable Course • Excel for Customer Service Professionals • Excel for Operations Management Course • Excel for Decision Making Under Uncertainty Course • Excel for Finance Course • Excel Analysis ToolPak Course • Multi-User Pricing • Resources • Free Downloads • Excel Functions Explained • Excel Formulas • Excel Add-ins • IF Function • Excel IF Statement Explained • Excel IF AND OR Functions • IF Formula Builder • Time & Dates in Excel • Excel Date & Time • Calculating Time in Excel • Excel Time Calculation Tricks • Excel Date and Time Formatting • Excel Keyboard Shortcuts • Excel Custom Number Format Guide • Pivot Tables Guide • VLOOKUP Guide • ALT Codes • Excel VBA & Macros • Excel User Forms • VBA String Functions • Members • Login • Blog • Excel Webinars • Excel Forum • Register as Forum Member • • Search Search site Submit search The Excel VLOOKUP formula is my favourite! Perhaps because it was one of the first formulas I mastered. It gave me an insight into the power of Excel and how it could help me in my job. Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match. However, I find that most peop...

What is a VLOOKUP in Excel? A VLOOKUP, short for "vertical lookup" is a formula in Microsoft Excel to match data from two lists. Instead of jumping between spreadsheets and typing out your matching data, you can write a VLOOKUP formula to automate the process. Combing two lists is a perfect situation to use a VLOOKUP. On the left (in the image above), we have employee shift information. We want to add the employee's job title to the shift data. With a separate list of employees and their job titles, we can write a VLOOKUP formula to pull in the title from a lookup list. A successful VLOOKUP needs three things: • A primary key in each list that you can use to match your data up. The two lists need to share at least one piece of data in common (in the Excel VLOOKUP example above, this is the employee ID) • A Lookup List, which contains your "database", or basically the information (the list of employee job titles) • Your data, which you want to pull a match into (the shift data) Quick Example of an Excel VLOOKUP Formula in Action VLOOKUP is a Microsoft Excel formula that's essential for working with multiple sets of data. In this tutorial, I'll teach you how to master and use it. Example VLOOKUP formula used to look up employee data. Using the example above, I've now written a VLOOKUP formula that looks up the employee's ID and inserts the job title into the shift data. Because both sheets have an employee ID, Excel can lookup the matching job title. The best part of VLOOKUP...

Tip: Also, refer to the Problem: The lookup value is not in the first column in the table_array argument One constraint of VLOOKUP is that it can only look for values on the left-most column in the table array. If your lookup value is not in the first column of the array, you will see the #N/A error. In the following table, we want to retrieve the number of units sold for Kale. The #N/A error results because the lookup value “Kale” appears in the second column (Produce) of the table_array argument A2:C10. In this case, Excel is looking for it in column A, not column B. Solution: You can try to fix this by adjusting your VLOOKUP to reference the correct column. If that’s not possible, then try moving your columns. That may also be highly impracticable, if you have large or complex spreadsheets where cell values are results of other calculations—or maybe there are other logical reasons why you simply cannot move the columns around. The solution is to use a combination of INDEX and MATCH functions, which can look up a value in a column regardless of its location position in the lookup table. See the next section. Consider using INDEX/MATCH instead There are several benefits of using INDEX/MATCH instead of VLOOKUP: • With INDEX and MATCH, the return value need not be in the same column as the lookup column. This is different from VLOOKUP, in which the return value has to be in the specified range. How does this matter? With VLOOKUP, you have to know the column number that cont...

You Need an Exact Match The last argument of the VLOOKUP function, known as range_lookup, asks if you would like an approximate or an exact match. In most cases people are looking for a particular product, order, employee or customer and therefore require an exact match. When looking for a unique value, FALSE should be entered for the range_lookup argument. This argument is optional, but if left empty, the TRUE value is used. The TRUE value relies on your data being sorted in ascending order to work. The image below shows a VLOOKUP with the range_lookup argument omitted, and the incorrect value being returned. Solution If looking for a unique value, enter FALSE for the last argument. The VLOOKUP above should be entered as =VLOOKUP(H3,B3:F11,2,FALSE). Lock the Table Reference Maybe you are looking to use multiple VLOOKUPs to return different information about a record. If you are planning to copy your VLOOKUP to multiple cells, you will need to lock your table. The image below shows a VLOOKUP entered incorrectly. The wrong cell ranges are being referenced for the lookup_value and table array. Solution The table that the VLOOKUP function uses to look for and return information from is known as the table_array. This will need to be referenced absolutely to copy your VLOOKUP. Click on the references within the formula and press the F4 key on the keyboard to change the reference from relative to absolute. The formula should be entered as =VLOOKUP($H$3,$B$3:$F$11,4,FALSE). In th...

You can use the following syntax to use a VLOOKUP function in Excel to look up a number that is stored as a text in a range where the numbers are stored as ordinary numbers: =VLOOKUP(VALUE( E1), A2:B11, 2, FALSE) This particular formula looks up the number in cell E1 (in which this value is saved as text) in the range A2:B11 and returns the corresponding value in the second column of the range. Note that this formula uses the VALUE function to convert a text string that represents a number into a numeric value. The following example shows how to use this syntax in practice. Example: How to Use VLOOKUP with Numbers Stored as Text Suppose we have the following dataset in Excel that contains information about the sales made by various employees at some company: Now suppose that we would like to look up the employee ID 3490 and return the corresponding sales value. However, suppose this ID value is stored as Text in Excel. Suppose we attempt to use the following VLOOKUP function to look up this value: =VLOOKUP( E1, A2:B11, 2, FALSE) The following screenshot shows how to use this formula in practice: We receive #N/A as a result because the value in cell E1 is stored as Text. To get around this issue, we must use the VALUE function within the VLOOKUP function: =VLOOKUP(VALUE( E1), A2:B11, 2, FALSE) The following screenshot shows how to use this formula in practice: The formula correctly returns a value of 22, which matches the sales value associated with employee ID 3490. If you...

Today we'll look at how to use VLOOKUP in Excel with many detailed step-by-step examples. You'll learn how to Vlookup from another sheet and different workbook, search with wildcards, and a lot more. This article begins a series covering VLOOKUP, one of the most useful Excel functions and at the same time one of the most intricate and least understood. We will try to explain the basics in a very plain language to make the learning curve for an inexperienced user as easy as possible. We will also provide formula examples that cover the most typical usages of VLOOKUP in Excel, and try to make them both informative and fun. • • • • • • • • • • • Excel VLOOKUP function What is VLOOKUP? To begin with, it is an Excel function :) What does it do? It searches for the value you specify and returns a matching value from another column. More technically, the VLOOKUP function looks up a value in the first column of a given range and returns a value in the same row from another column. In its common usage, Excel VLOOKUP searches through your data set based on the unique identifier and brings you a piece of information associated with that unique identifier. The letter "V" stands for "vertical" and is used to differentiate VLOOKUP from the The function is available in all versions of Excel 365 through Excel 2007. VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Where: • Lookup_value (required) - is the value to search for. This can be a value (number, date or text), cel...