VLOOKUP in Excel Step by Step Tutorial: 2026 Guide

vlookup in excel step by step tutorial guide

A vlookup in excel step by step tutorial teaches you how to retrieve specific data from a large table by matching a lookup value. You need this skill to save hours of manual searching and avoid costly data entry errors. This guide covers the exact syntax, common errors, and practical examples to help you master the function today. You can practice these exact scenarios on our interactive platform at LogicExcel.

What is VLOOKUP?

VLOOKUP is a built-in Excel function that searches for a value in the first column of a table and returns a value in the same row from another column. You use it to pull data from one list into another. It stands for Vertical Lookup. It works left to right. You can read more about the official definition in the Microsoft support documentation.

You provide four pieces of information to the function. The function uses these pieces to find your answer. You must understand each piece to use the function correctly. You can explore more tools in our functions library.

Here are the four arguments you need:

* lookup_value: This is the value you already know. You want to find information related to this value.
* table_array: This is the range of cells that holds your data. The first column of this range must contain your lookup value.
* col_index_num: This is the column number inside your table array. The function pulls the answer from this column. The first column is number 1.
* range_lookup: This tells Excel if you want an exact match or an approximate match. You type FALSE for exact. You type TRUE for approximate.

You must lock your table array when you copy your formula down a column. This prevents the range from shifting. You can learn more about formula basics in the overview of formulas in Excel.

Pro Tip: Always lock your table array using the F4 key. This creates an absolute reference. It prevents your data range from moving when you drag the formula down.

How do you write a basic VLOOKUP formula?

You write a basic VLOOKUP formula by typing =VLOOKUP( and then providing four arguments separated by commas. You close the formula with a parenthesis. You press Enter to see your result.

Let us look at a practical example. You have a list of employee IDs. You want to find the department for each employee. You have a master table that holds the IDs and the departments. You can practice this scenario with our interactive Excel exercises.

Here is your master data table.

Employee ID	Department	Location
101	Sales	New York
102	IT	London
103	HR	Tokyo

You want to find the department for Employee ID 102. You write your formula in a blank cell.

Follow these steps to write the formula:

Click the cell where you want the answer to appear.
Type =VLOOKUP(
Click the cell that contains your lookup value. In this case, click the cell holding 102.
Type a comma.
Highlight your master data table. Include the ID column and the Department column.
Press F4 to lock the range. Your range might look like $A$2:$C$4.
Type a comma.
Type the number 2. You want the second column, which is the Department column.
Type a comma.
Type FALSE. You want an exact match for the employee ID.
Type ) and press Enter.

Your final formula looks like this: =VLOOKUP(E2, $A$2:$C$4, 2, FALSE). Excel returns IT.

You must use an exact match when you look up unique identifiers like IDs or names. You must use an absolute reference for your table array. You must follow the exact syntax to avoid errors. You must select the correct data range that includes your answer.

Pro Tip: Use FALSE as your fourth argument to ensure you only get exact matches. Approximate matches can return wrong data if your list is not sorted correctly.

How do you handle VLOOKUP errors?

You handle VLOOKUP errors by wrapping the formula in an IFERROR function to catch missing data or formatting issues. VLOOKUP fails when it cannot find your lookup value. It also fails when your data is not formatted correctly. You can see how VLOOKUP fits into the broader Excel functions by category.

The most common error is the #N/A error. Excel shows this error when the lookup value does not exist in the first column of your table array. You might have typed the wrong ID. The ID might be missing from the master list.

The second common error is the #REF! error. Excel shows this error when your col_index_num is larger than the number of columns in your table array. You might type 4 when your table only has 3 columns.

The third common error is the #VALUE! error. Excel shows this error when your col_index_num is less than 1. You cannot ask for column 0.

Sometimes your formula is correct but your data is dirty. Leading spaces cause VLOOKUP to fail. A lookup value of "102" is not the same as "102 ". Text format also causes failures. A number stored as text will not match a number stored as a value.

You can fix these issues by cleaning your data. You can also hide the ugly errors from your spreadsheet. You use a nested formula to hide errors. You put your VLOOKUP inside an IFERROR function.

Here is how you write it: =IFERROR(VLOOKUP(E2, $A$2:$C$4, 2, FALSE), "Not Found"). Excel runs the VLOOKUP. If it finds an answer, it shows the answer. If it finds an error, it shows "Not Found". You can learn more about text manipulation in our other Excel skills section.

Pro Tip: Use the TRIM function on your lookup values to remove hidden spaces. You can write =VLOOKUP(TRIM(E2), $A$2:$C$4, 2, FALSE) to prevent false #N/A errors.

How does VLOOKUP compare to XLOOKUP?

VLOOKUP is older and only searches left to right, while XLOOKUP is a modern replacement that searches in any direction. VLOOKUP requires your lookup column to be the first column in your table array. XLOOKUP does not have this limitation. You can look up a value in column C and return a value from column A.

VLOOKUP also breaks when you insert columns. If you insert a new column inside your table array, your col_index_num does not update automatically. Your formula returns the wrong column. XLOOKUP uses actual column references. It adjusts automatically when you insert columns. You can read more about this in the Excel functions alphabetical list.

XLOOKUP has a simpler syntax. You provide the lookup value, the lookup array, and the return array. You do not need to count columns. You do not need to use an absolute reference for a whole table. You just select the exact columns you need.

XLOOKUP also has a built in IFERROR. You can tell XLOOKUP what to display if it does not find a match. You do not need to wrap it in another function. This makes your formulas shorter and easier to read.

> "The XLOOKUP function searches a range or an array and returns an item corresponding to the first match it finds. It replaces the need for VLOOKUP." - Microsoft

You should learn VLOOKUP because many older spreadsheets still use it. You should learn XLOOKUP to build better new spreadsheets. You can compare them directly using the XLOOKUP function documentation.

VLOOKUP relies on a rigid default behavior. It cannot perform a left lookup. It struggles with complex array manipulation. XLOOKUP is the official replacement function for modern Excel users.

Pro Tip: If you use a newer version of Excel, learn XLOOKUP to avoid the limitations of VLOOKUP. It will save you time and prevent broken formulas. vlookup in excel step by step tutorial quick reference

vlookup in excel step by step tutorial quick reference

Key Takeaways

You must understand the four arguments and lock your ranges to use VLOOKUP successfully.

Point	Details
Basic Syntax	=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Exact Match	Always use FALSE for unique identifiers like IDs or names.
Absolute Reference	Press F4 to lock your table array and prevent shifting.
Error Handling	Wrap your formula in IFERROR to catch #N/A errors.
Modern Alternative	XLOOKUP offers more flexibility and does not require counting columns.

What I've learned from teaching Excel formulas

I have taught Excel for many years. I have seen thousands of students struggle with VLOOKUP. The struggle usually comes from one place. Students do not lock their table arrays. They write a perfect formula in the first cell. They drag it down. Every cell below returns an error. They think their logic is wrong. They spend hours debugging. The fix is always the same. Press F4.

I also see students try to look up values to the left. They have a name in column B. They have an ID in column A. They want to use the name to find the ID. VLOOKUP cannot do this. I teach them to rearrange their data. I teach them to put the ID in column A. Or I teach them XLOOKUP.

VLOOKUP is a great teaching tool. It forces you to think about data structure. It forces you to understand relative and absolute references. Once you master VLOOKUP, every other lookup function becomes easier. You can test your skills in our learning hub.

> , Nkosi

LogicExcel helps you master data lookups

LogicExcel is a free interactive Excel practice site. You type formulas directly into our browser based simulator. You get instant feedback. You do not need to download any files. You do not need to worry about breaking a spreadsheet. You can learn at your own pace.

working on vlookup in excel step by step tutorial

You can start with our Excel learning guides. You can take structured Excel courses. You can read more tutorials on our blog. When you feel ready, you can earn a verified badge with our certification program.

FAQ

What does VLOOKUP do in Excel?

VLOOKUP searches for a specific value in the first column of a table. It returns a value in the same row from a column you specify. You use it to merge data from two different lists.

Why does my VLOOKUP say #N/A?

Your VLOOKUP says #N/A because it cannot find your lookup value in the first column of your table array. This can happen if the value is missing, if there are hidden spaces, or if one value is text and the other is a number.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches vertically down columns. HLOOKUP searches horizontally across rows. You use VLOOKUP when your data is organized in columns. You use HLOOKUP when your data is organized in rows.

Can VLOOKUP look to the left?

No, VLOOKUP cannot look to the left. It only searches the first column of your table array and returns values to the right. You must rearrange your data or use the XLOOKUP function to look to the left.

Should I use TRUE or FALSE in VLOOKUP?

You should use FALSE for an exact match. You should use TRUE for an approximate match. You use TRUE when you want to find the closest match in a sorted list, like tax brackets or grading scales.