VLOOKUP in Excel explained simply — syntax, step-by-step examples, common errors, and tips for beginners and professionals.
If you work with data in Excel, learning VLOOKUP in Excel is one of the most important skills you can develop. VLOOKUP — short for Vertical Lookup — is a powerful built-in function that lets you search for a value in one column and automatically return related data from the same row.
Whether you are a student, a data analyst, or an office professional, the VLOOKUP formula in Excel will save you hours of manual work every week. In this guide, you will learn what VLOOKUP means, understand its syntax, and see real step-by-step examples — including how to use VLOOKUP from another sheet and how to fix common errors.
VLOOKUP meaning: VLOOKUP stands for Vertical Lookup. It is a built-in Excel function that searches for a specific value in the leftmost column of a table and returns a value from a specified column in the same row.
Think of it like a telephone directory. You search for a person's name (the lookup value) in the first column, and once found, Excel returns their phone number (the value from another column) automatically.
The VLOOKUP function in Excel is useful when you want to:
In short, any time you need to look up data in Excel without manually searching through thousands of rows, VLOOKUP is the answer.
The VLOOKUP formula in Excel follows this syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Let us understand each of the 4 arguments one by one:
This is the value you want to search for in the first column of the table. It can be a number, text, or a cell reference.
Example: If you want to find the salary of employee with ID E003, then E003 is your lookup_value.
This is the range of cells (the table) where Excel will search. The first column of this range must contain the value you are looking for (lookup_value).
Example: A2:D100 — the table that contains Employee ID, Name, Department, and Salary.
Tip: Always use an absolute reference (with $ signs like $A$2:$D$100) so the range does not shift when you copy the formula down.
This is the column number (counting from the left of your table_array) from which you want to return the value.
Example: If your table is A:D and Salary is in column D (the 4th column), then col_index_num = 4.
This argument tells Excel whether to find an exact match or approximate match:
FALSE (or 0) → Exact Match — Use this in most cases. Returns an error if no exact match is found.TRUE (or 1) → Approximate Match — Used for ranges like tax slabs or grade calculations. Requires the first column to be sorted in ascending order.For beginners: Always use FALSE. It gives accurate results and is the most common usage.
Let us understand the VLOOKUP formula with a practical Indian example.
Suppose you are an HR manager at a company in Mumbai. You have an employee database in Excel. You want to find the salary of an employee by entering their Employee ID.
| A – Employee ID | B – Name | C – Department | D – Salary (INR) |
|---|---|---|---|
| E001 | Rahul Sharma | Sales | 45,000 |
| E002 | Priya Mehta | IT | 62,000 |
| E003 | Ankit Gupta | Finance | 55,000 |
| E004 | Neha Singh | HR | 48,000 |
| E005 | Vijay Patil | Marketing | 52,000 |
If the Employee ID you want to look up is in cell G2, the formula will be:
=VLOOKUP(G2, A2:D6, 4, FALSE)Breaking it down:
G2 → lookup_value (the Employee ID you entered, e.g., E003)A2:D6 → table_array (the full employee data table)4 → col_index_num (Salary is in the 4th column)FALSE → exact matchResult: When you type E003 in cell G2, Excel will return 55,000 — Ankit Gupta's salary.
To find the Name of an employee (2nd column), change col_index_num to 2:
=VLOOKUP(G2, A2:D6, 2, FALSE)To find the Department (3rd column):
=VLOOKUP(G2, A2:D6, 3, FALSE)Here is how to apply VLOOKUP in Excel from scratch:
=VLOOKUP( — Excel will show the formula tooltip.$A$2:$D$100). Type a comma.FALSE for exact match. Close the bracket with ).Final formula looks like this:
=VLOOKUP(G2, $A$2:$D$100, 4, FALSE)Pro Tip: After writing the formula in one cell, copy it down to the other cells. Because you used $ signs (absolute reference) in the table_array, the range will stay fixed for all rows.
In real-world Excel files, your data is often split across multiple sheets. The good news is that VLOOKUP can look up data from a different sheet in the same workbook very easily.
=VLOOKUP(lookup_value, SheetName!table_array, col_index_num, FALSE)Notice the SheetName! before the table range — this tells Excel to look in that specific sheet.
Suppose your employee database is on a sheet named EmployeeDB (columns A to D), and you are writing your formula on a different sheet called Report.
In the Report sheet, your formula will be:
=VLOOKUP(A2, EmployeeDB!$A$2:$D$100, 4, FALSE)What this does: It looks for the Employee ID in cell A2 of the Report sheet, searches the EmployeeDB sheet's A to D columns, and returns the value from column 4 (Salary).
=VLOOKUP(A2,A2:D100 and press F4 to lock it, 4, FALSE)Here are the most common VLOOKUP errors and their solutions:
Meaning: The lookup value was not found in the first column of the table.
Causes and Fixes:
TRIM() to clean both the lookup value and the table. Example: =VLOOKUP(TRIM(G2), $A$2:$D$100, 4, FALSE)VALUE() to convert text to numbers.Suppress the error with IFERROR:
=IFERROR(VLOOKUP(G2, $A$2:$D$100, 4, FALSE), "Not Found")
Meaning: The col_index_num is greater than the number of columns in your table_array.
Fix: Count your columns again. If your table has 4 columns (A to D), col_index_num cannot be 5 or more.
Meaning: The col_index_num is less than 1, or the lookup_value is invalid.
Fix: Make sure col_index_num is a positive number (1 or greater).
This usually happens when you use TRUE (approximate match) instead of FALSE (exact match). Always use FALSE unless you specifically need approximate matching for ranges.
VLOOKUP always returns the first matching result it finds. If you have duplicate values and need to return multiple matches, you will need to use other functions like XLOOKUP with arrays or Power Query.
Both VLOOKUP and HLOOKUP are lookup functions in Excel, but they search data in different directions.
| Feature | VLOOKUP | HLOOKUP |
|---|---|---|
| Full Name | Vertical Lookup | Horizontal Lookup |
| Search Direction | Top to bottom (column) | Left to right (row) |
| Data Layout | Data in rows, headers in columns | Data in columns, headers in rows |
| When to Use | Most common – vertical tables | For horizontal tables (rare) |
| Syntax | VLOOKUP(value, range, col_num, match) | HLOOKUP(value, range, row_num, match) |
In practice, VLOOKUP is used 95% of the time because most Excel data is organized in vertical tables (rows of records with column headers). HLOOKUP is rarely needed.
XLOOKUP is a newer function introduced in Excel 365 and Excel 2021. It is considered a modern replacement for VLOOKUP. Here is how they compare:
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search Column Position | Must be the first (leftmost) column | Can be any column |
| Search Direction | Only left to right | Any direction |
| Default Match Type | Approximate (needs FALSE for exact) | Exact match by default |
| Error Handling | Needs IFERROR separately | Built-in if_not_found argument |
| Multiple Returns | Returns only one column | Can return multiple columns |
| Availability | All Excel versions | Excel 365 and Excel 2021 only |
Recommendation: If you are using Excel 365 or 2021, learn XLOOKUP — it is more powerful and flexible. If you are on an older version (Excel 2016, 2019), VLOOKUP is your go-to function.
That said, VLOOKUP is still heavily used in interviews and workplaces in India. Knowing it well is essential for any data analyst or Excel professional.
Always lock your table_array with $ signs so it does not shift when you copy the formula down. Press F4 after selecting the range to add $ signs automatically.
=VLOOKUP(A2, $B$2:$E$100, 3, FALSE)Wrap your VLOOKUP in IFERROR to show a friendly message instead of a #N/A error:
=IFERROR(VLOOKUP(G2, $A$2:$D$100, 4, FALSE), "Employee Not Found")VLOOKUP natively supports only one condition. To use it with two conditions, you can use a helper column that combines two values:
Helper Column: =A2&"-"&B2 (combines Employee ID and Month)Then VLOOKUP on the combined helper column value.
VLOOKUP returns only one column at a time. To return multiple columns, write separate VLOOKUP formulas for each column, changing only the col_index_num. Alternatively, use XLOOKUP which can return an array.
VLOOKUP does not distinguish between uppercase and lowercase. "rahul" and "RAHUL" are treated as the same. If you need case-sensitive lookup, use INDEX MATCH with EXACT function.
VLOOKUP stands for Vertical Lookup. It is a built-in Excel function that searches for a value in the leftmost column of a table and returns a corresponding value from another column in the same row. It is one of the most widely used functions in Excel for data lookup and analysis.
The 4 arguments are: (1) lookup_value — the value to search for, (2) table_array — the table range to search in, (3) col_index_num — the column number from which to return the result, and (4) range_lookup — TRUE for approximate match or FALSE for exact match.
VLOOKUP scans the first column of the table_array from top to bottom looking for the lookup_value. When it finds a match, it moves across to the column number you specified and returns that value. For example, searching for Employee ID E003 in a table can return the employee's name, salary, or department.
The most common reasons are: extra spaces in cells (fix with TRIM), number-text mismatch (fix with VALUE), the lookup value not being in the first column of the table, or using TRUE instead of FALSE for exact matching.
XLOOKUP is more flexible — it can search in any direction, the lookup column does not need to be the first column, and it has built-in error handling. VLOOKUP requires the search column to be the leftmost column. XLOOKUP is available only in Excel 365 and 2021.
Yes. Use the format SheetName!range in the table_array argument. For example: =VLOOKUP(A2, EmployeeDB!$A$2:$D$100, 4, FALSE).
Yes, VLOOKUP is still one of the most asked Excel functions in job interviews and is widely used in offices across India. While XLOOKUP is more powerful, VLOOKUP remains essential knowledge for anyone working with Excel.
The best alternatives to VLOOKUP are XLOOKUP (Excel 365/2021) and INDEX MATCH. INDEX MATCH is available in all Excel versions and is more flexible than VLOOKUP as it allows searching in any column, not just the leftmost one.
VLOOKUP in Excel is a must-know skill for anyone who works with data. Once you understand its 4 arguments — lookup_value, table_array, col_index_num, and range_lookup — you can use it to solve a wide range of real-world data problems quickly and efficiently.
Practice the examples in this guide using your own Excel files. Start with a simple employee table, and then try using VLOOKUP from another sheet. As you get comfortable, explore IFERROR wrapping and absolute references to make your formulas more robust.
If you want to work even faster in Excel, check out our complete guide on MS Excel Shortcut Keys — knowing the right keyboard shortcuts will save you hours every week.
Sign in to join the discussion and post comments.
Sign in