vlookup excel tutorial

VLOOKUP Excel Tutorial: Master the Formula Fast with Easy Examples

By Christopher Moore
AD

If you have ever stared at two Excel lists and wondered how to pull matching information without copying and pasting for hours, VLOOKUP is the function that changes your workflow. It is one of the most widely used lookup tools in Excel, and learning it gives you a practical way to connect data across sheets, workbooks, and reports. This VLOOKUP Excel tutorial walks through the syntax, real examples, common errors, and advanced tricks you can use in everyday spreadsheets.

VLOOKUP stands for vertical lookup. Instead of scanning rows manually, you tell Excel which value to find, where to search, which column contains the answer, and whether you need an exact or approximate match. Once you understand those four pieces, you can build formulas that update automatically when your source data changes, which saves time and reduces mistakes.

What VLOOKUP Does and When to Use It

VLOOKUP is designed to search down the first column of a selected range and return a value from another column in the same row. For example, if you have a product list with product codes in column A and prices in column C, VLOOKUP can find a code and bring back the matching price. This makes it ideal for invoices, order forms, sales reports, inventory sheets, and any worksheet where records need to be matched by a unique identifier.

AD
How to use Excel VLOOKUP 2019
How to use Excel VLOOKUP 2019
61 Excel Charts Examples! | MyExcelOnline
61 Excel Charts Examples! | MyExcelOnline
How to Use VLOOKUP in Excel | Easy VLOOKUP Formula Tutorial
How to Use VLOOKUP in Excel | Easy VLOOKUP Formula Tutorial

The function is most effective when your lookup value appears in the leftmost column of your table. It also works best with clean, consistent data. Extra spaces, mismatched number formats, or duplicate lookup values can cause unreliable results. Before writing a VLOOKUP formula, take a moment to confirm that the lookup column is unique and that both lists use the same data type.

Understanding the VLOOKUP Syntax

The VLOOKUP formula follows this structure: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). The lookup_value is what you want to find, such as a product ID or employee name. The table_array is the range that contains both the lookup column and the return column. The col_index_num is the column number you want Excel to return, counting from the first column of the table_array. The range_lookup tells Excel whether you want an exact match or an approximate match.

5 Advanced Excel VLOOKUP Tricks You MUST Know
5 Advanced Excel VLOOKUP Tricks You MUST Know
Using VLOOKUP function (step by step tutorial)
Using VLOOKUP function (step by step tutorial)
VLOOKUP Excel Formula: Easy Guide for Beginners
VLOOKUP Excel Formula: Easy Guide for Beginners

Suppose cell A2 contains the product code you need, and Products!A:D contains product data. The formula =VLOOKUP(A2, Products!A:D, 3, FALSE) looks for the value in A2, searches the first column of Products!A:D, and returns the third column from the matched row. The FALSE at the end requests an exact match, which is the setting most Excel users should start with for IDs, names, and codes.

Exact Match vs Approximate Match

Exact match uses FALSE or 0 as the final argument. It tells Excel to find only an identical match. If no exact match exists, Excel returns #N/A. This is the safest option for most business lookups because a wrong but similar match can be more dangerous than a visible error. Product codes, invoice numbers, customer IDs, and email addresses should almost always use exact match.

AD

Approximate match uses TRUE or 1 as the final argument. It is useful for tiered calculations, such as tax brackets, commission rates, grade scales, or shipping discounts. The lookup table must be sorted in ascending order for approximate match to work correctly. For example, a grade table might list 0, 60, 70, 80, and 90 in the first column along with letter grades in the second column. A VLOOKUP with TRUE can then assign the correct tier based on a score.

VLOOKUP Approximate Match | MyExcelOnline
VLOOKUP Approximate Match | MyExcelOnline
How to Use VLOOKUP in Excel
How to Use VLOOKUP in Excel
VLOOKUP Formula
VLOOKUP Formula
the only guide you need to know about excel vlookup, by peter j schreck
the only guide you need to know about excel vlookup, by peter j schreck
How Excel VLOOKUP Works: How to use it - Explanation with Example - PakAccountants.com
How Excel VLOOKUP Works: How to use it - Explanation with Example - PakAccountants.com
VLOOKUP formula
VLOOKUP formula

How to Build a VLOOKUP Formula Step by Step

Building a VLOOKUP formula is easier when you approach it in stages. First, identify the cell that contains the value you want to look up. Second, select the table range that contains both the lookup column and the return column. Third, count the columns from the start of that range to the column you want to return. Fourth, decide whether you need an exact or approximate match. Once those decisions are made, the formula itself is straightforward.

Relative and absolute references matter a great deal in VLOOKUP. If you plan to copy the formula down a column, lock the table_array with dollar signs, such as $A$2:$D$100. Otherwise, Excel may shift the range as you drag the formula, causing missing or incorrect results. Locking the table range keeps the lookup area stable while the lookup_value changes from row to row.

AD

Writing Your First VLOOKUP in Excel

Imagine an Orders sheet where column B contains product codes and column C needs product names. On a Products sheet, column A contains product codes and column B contains product names. In Orders cell C2, type =VLOOKUP(B2, Products!$A$2:$B$100, 2, FALSE). This tells Excel to find the code from B2 in the first column of Products!A2:B100 and return the second column, which is the product name.

How to use the VLOOKUP Function in Excel | ExcelSuperSite
How to use the VLOOKUP Function in Excel | ExcelSuperSite
How to VLOOKUP to the Left in Excel
How to VLOOKUP to the Left in Excel
Excel VLOOKUP: 5 Advanced LOOKUP Tricks You MUST Know
Excel VLOOKUP: 5 Advanced LOOKUP Tricks You MUST Know
How to Use VLOOKUP in Excel - Step by Step Guide | MyExcelOnline
How to Use VLOOKUP in Excel - Step by Step Guide | MyExcelOnline

After pressing Enter, copy the formula down the column. If the result is correct for the first row, check several other rows to make sure the matches are accurate. A common beginner mistake is forgetting to lock the table range. Another is counting the return column from the sheet instead of from the start of the table_array. If your table starts in column A, column C is the third column. If it starts in column B, column C is the second column.

Common VLOOKUP Errors and Fixes

The #N/A error usually means Excel could not find the lookup value. Check for typos, extra spaces, hidden characters, or numbers stored as text. A product code entered as 1001 may not match a text value of "1001". Functions such as TRIM and VALUE can help clean the data. You can also wrap the formula in IFERROR, like =IFERROR(VLOOKUP(B2, Products!$A$2:$B$100, 2, FALSE), "Not found"), to replace the error with a friendlier message.

AD

The #REF! error appears when col_index_num is larger than the number of columns in the table_array. If your table has three columns and you ask for column five, Excel cannot return a value. The #VALUE! error often means col_index_num is not a number. Also check sheet names with spaces, which require single quotation marks, such as 'Sales Data'!A:D. These small fixes resolve most VLOOKUP problems quickly.

Advanced VLOOKUP Techniques for Real Workbooks

Once the basics are comfortable, VLOOKUP can handle more complex reporting tasks. You can use wildcards for partial matches, combine it with MATCH to make the return column dynamic, and apply it across large datasets with structured references. These techniques are especially useful when column positions change or when you need to search for text patterns rather than exact values.

Advanced VLOOKUP is not about memorizing complicated formulas. It is about understanding how Excel stores and matches data. When your lookup column is clean, your table range is locked, and your match type is chosen deliberately, VLOOKUP becomes a dependable part of your reporting toolkit. You can then decide whether VLOOKUP, XLOOKUP, or INDEX and MATCH is the best fit for each task.

Using Wildcards and Approximate Matches

Wildcards let VLOOKUP match part of a text string. An asterisk represents any number of characters, while a question mark represents a single character. For example, =VLOOKUP("North*", A2:C10, 3, FALSE) can find the first entry that begins with "North". This is helpful when product names, regions, or categories share a common prefix. If you need to search for an actual asterisk or question mark, place a tilde before it.

Approximate match is powerful for tiered tables but requires sorted data. A commission table might list lower bounds in ascending order, such as 0, 10000, 25000, and 50000, with rates in the next column. A VLOOKUP with TRUE returns the rate for the largest value that does not exceed the lookup value. If the table is not sorted correctly, the result may be wrong, so always verify the order before using TRUE.

Making Column Index Dynamic with MATCH

Hard-coding col_index_num works until someone inserts, deletes, or rearranges columns. To make the formula more resilient, use MATCH to find the position of the column header automatically. For example, =VLOOKUP(A2, Data!$A$1:$Z$100, MATCH("Revenue", Data!$A$1:$Z$1, 0), FALSE) searches for the header "Revenue" and supplies the correct column number to VLOOKUP. If the Revenue column moves, the formula can still return the right data.

This approach is common in dashboards and recurring reports where column order may change between refreshes. It also reduces maintenance when you copy formulas across multiple months or departments. For newer Excel versions, XLOOKUP offers similar flexibility with a simpler syntax and supports left lookups by default, but VLOOKUP with MATCH remains a valuable skill in shared workbooks and older files.

Keep a practice workbook nearby and test VLOOKUP with your own data. Start with a simple exact match, then try wildcards or a dynamic column reference once the basic pattern feels natural. The more you use it on real reports, the faster you will recognize when VLOOKUP is the right tool and when a newer function might serve you better.

AD