VLOOKUP Formula Excel – Step-by-Step Tutorial for Beginners 2026

It was 11 PM on a Thursday. Arjun, a 26-year-old accounts executive in Surat, had just received an urgent WhatsApp from his manager: “I need the salary details for all 340 employees matched with their department codes – first thing tomorrow morning.” Arjun had two Excel files open. One had employee names and IDs.

The other had department codes and salaries. Matching them manually would take until 3 AM. A colleague sitting nearby typed one formula, pressed Enter, and dragged it down. In 40 seconds, the work was done. That formula was VLOOKUP. This guide is what Arjun learned the next day – and what you are about to learn right now.

VLOOKUP is one of the most searched Excel formulas in the world – and for good reason. It solves a real problem that almost every professional faces: you have two lists of data and you need to match them together quickly, without manual copy-pasting.

In this 2026 guide, you will learn VLOOKUP from the very beginning. We will use real stories, real office scenarios, clear tables, and step-by-step walkthroughs. By the time you finish reading, you will be able to use VLOOKUP confidently in your own spreadsheets – whether you work in HR, Sales, Finance, Operations, or any other field.

Chapter 1: What Is VLOOKUP? – A Story That Makes It Crystal Clear

Imagine your school had two registers. Register A had Roll Numbers and Student Names. Register B had Roll Numbers and their Marks. To find Priya’s marks, you would first look up Priya’s Roll Number in Register A, then take that Roll Number to Register B and find her marks. That is VLOOKUP. You give it one value, it searches a table for that value, then returns a related piece of information from the same row – from whichever column you specify.

VLOOKUP stands for Vertical Lookup. The ‘V’ means it searches vertically – top to bottom – down a column. When it finds a match, it moves horizontally to the right and returns the value from a column you choose.

Think of it as your personal data assistant. You say: ‘Find Employee ID 1042 in this table and tell me their Salary.’ VLOOKUP does exactly that – for every row in your sheet – in less than a second.

Arjun Meets VLOOKUP

Back to Arjun. His two files had one thing in common: Employee ID. File 1 had Employee ID and Name. File 2 had Employee ID, Department, and Salary. VLOOKUP used Employee ID as the bridge. It searched File 2 for each ID from File 1 and pulled back the Salary. What would have taken Arjun 4 hours was done in under a minute. This is the power of VLOOKUP – it connects two datasets using a common column.

Chapter 2: VLOOKUP Syntax – Every Part Explained Simply

Here is the VLOOKUP formula:

VLOOKUP Full Syntax:
=VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

Four parts. Let us break every single one down in plain English:

Parameter	Plain English Meaning	Simple Example	Common Mistake
lookup_value	The value you want to search for – like an ID, name, or code	A2 or “EMP1042” or 1001	Typing the value directly instead of referencing a cell (breaks when data changes)
table_array	The range/table where VLOOKUP should search – must include the lookup column as its FIRST column	B2:E500 or $B$2:$E$500	Not locking with $ signs, so the range shifts when formula is copied down
col_index_num	The column number to return – counted from the LEFT edge of your table_array (not from column A)	3 means the 3rd column of your table range	Counting from column A of the sheet instead of from the start of table_array
[range_lookup]	FALSE = find exact match only (almost always what you want). TRUE = approximate match (for sorted ranges only)	FALSE or 0 for exact match	Omitting this – Excel defaults to TRUE which can return completely wrong results on unsorted data

Always Use FALSE as Your 4th Argument: Until you fully understand approximate match (TRUE), always type FALSE or 0 as your 4th argument. This ensures VLOOKUP only returns a result when it finds an exact match. Leaving this argument empty or using TRUE on unsorted data is the leading cause of wrong VLOOKUP results for beginners.

Understanding col_index_num – The Most Confusing Part

This is where most beginners get confused. The column index number is NOT counted from column A of your spreadsheet. It is counted from the LEFT EDGE of your table_array.

Example - Your table is in columns B to E (B=Name, C=Dept, D=City, E=Salary):
=VLOOKUP(A2, B2:E100, 1, FALSE)   --> Returns: Name (1st col of B:E = B)
=VLOOKUP(A2, B2:E100, 2, FALSE)   --> Returns: Department (2nd col = C)
=VLOOKUP(A2, B2:E100, 3, FALSE)   --> Returns: City (3rd col = D)
=VLOOKUP(A2, B2:E100, 4, FALSE)   --> Returns: Salary (4th col = E)

Arjun Counts Wrong – And Fixes It: Arjun’s first formula returned Department names in the Salary column. He had typed col_index_num = 2, but Salary was actually in the 3rd column of his table range. He changed 2 to 3 – and the formula worked perfectly. The lesson: always count columns from the left edge of YOUR table range, not from column A of the spreadsheet.

Chapter 3: Your First VLOOKUP – Built Step by Step

Sunita’s Employee Salary Lookup: Sunita is an HR executive at a manufacturing company in Pune. She has an Employee Master sheet with columns: A=EmpID, B=Name, C=Department, D=Designation, E=Salary. A manager emailed a list of 50 employee IDs and asked Sunita to attach each employee’s Department and Salary to the list. Instead of manually searching for each ID, Sunita used VLOOKUP twice. Let us build both formulas together.

Step 1 – Understand Your Data Layout

Column	Header	Contains	Example Data
A	EmpID	Unique employee ID	EMP001, EMP002…
B	Name	Employee full name	Sunita Sharma
C	Department	Department name	HR, Finance, Sales
D	Designation	Job title	Executive, Manager
E	Salary	Monthly salary in Rs.	35000, 52000, 78000

Step 2 – Identify the Three Things VLOOKUP Needs

• What am I searching FOR? The EmpID from the manager’s list – let us say it is in cell H2.
• WHERE do I search? The Employee Master table – columns A to E, rows 2 to 500.
• WHICH column should it return? Department is column 3. Salary is column 5.

Step 3 – Write the Formula for Department

Cell I2 - Get Department for EmpID in H2:
=VLOOKUP(H2, $A$2:$E$500, 3, FALSE)

H2          = The EmpID we are looking for
$A$2:$E$500 = The full Employee Master table (locked with $)
3           = Department is the 3rd column (A=1, B=2, C=3)
FALSE       = Exact match only

Step 4 – Write the Formula for Salary

Cell J2 - Get Salary for EmpID in H2:
=VLOOKUP(H2, $A$2:$E$500, 5, FALSE)

Everything is the same - only col_index_num changes from 3 to 5
E = Salary = 5th column of the table range

Step 5 – Copy the Formulas Down

• Click cell I2.
• Move your mouse to the small green square at the bottom-right corner of the cell.
• Double-click it – Excel automatically copies the formula to every row below.
• Do the same for cell J2.
• All 50 employee IDs now show their Department and Salary instantly.

Step 6 – See the Results

Cell	Lookup Value	Table Range	Formula	Excel Result
I2	EMP001	$A$2:$E$500	=VLOOKUP(H2,$A$2:$E$500,3,FALSE)	HR
I3	EMP007	$A$2:$E$500	=VLOOKUP(H3,$A$2:$E$500,3,FALSE)	Finance
J2	EMP001	$A$2:$E$500	=VLOOKUP(H2,$A$2:$E$500,5,FALSE)	Rs. 35,000
J3	EMP007	$A$2:$E$500	=VLOOKUP(H3,$A$2:$E$500,5,FALSE)	Rs. 78,000
I8	EMP999	$A$2:$E$500	=VLOOKUP(H8,$A$2:$E$500,3,FALSE)	#N/A

Key Concept: Why #N/A?: The last row shows #N/A because EMP999 does not exist in the Employee Master table. #N/A is Excel’s way of saying ‘I searched everywhere but could not find this value.’ This is not a mistake in your formula – it means no match was found. We will learn how to handle this gracefully in Chapter 5.

Chapter 4: Exact Match vs Approximate Match – When to Use Each

The 4th argument of VLOOKUP – range_lookup – is one of the most important and most misunderstood parts of the formula. It has two settings:

Setting	Value	How It Works	When to Use It	Warning
Exact Match	FALSE or 0	Returns a result ONLY if it finds the exact value you searched for	99% of everyday lookups – IDs, names, codes, invoice numbers	Safe to use on any data, sorted or unsorted
Approximate Match	TRUE or 1 (or omitted)	Returns the largest value that is LESS THAN OR EQUAL to the lookup value	Salary bands, tax slabs, grade tables, commission tiers – with SORTED data only	NEVER use on unsorted data – will return random wrong results

Approximate Match – A Real Example (Commission Tiers)

Rohan’s Commission Table: Rohan is a sales manager. His commission structure is: Sales below Rs. 50,000 = 0% commission. Rs. 50,000 to 99,999 = 5% commission. Rs. 1,00,000 to 1,99,999 = 8% commission. Rs. 2,00,000 and above = 12% commission. He wants VLOOKUP to automatically find the right commission rate for each salesperson’s monthly sales figure. This is a perfect use case for Approximate Match – TRUE.

The commission tier table in his sheet (columns G and H) looks like this:

G – Min Sales (Sorted Ascending)	H – Commission Rate
0	0%
50000	5%
100000	8%
200000	12%

Cell C2 - Commission Rate for Sales Amount in B2:
=VLOOKUP(B2, $G$2:$H$5, 2, TRUE)

B2          = Salesperson's actual sales amount
$G$2:$H$5   = Commission tier table (MUST be sorted ascending by column G)
2           = Return the 2nd column = Commission Rate
TRUE        = Approximate match - finds the tier the sales amount falls into

Cell	Lookup Value	Table Range	Formula	Excel Result
C2	Rs. 35,000	$G$2:$H$5	=VLOOKUP(B2,$G$2:$H$5,2,TRUE)	0%
C3	Rs. 75,000	$G$2:$H$5	=VLOOKUP(B3,$G$2:$H$5,2,TRUE)	5%
C4	Rs. 1,45,000	$G$2:$H$5	=VLOOKUP(B4,$G$2:$H$5,2,TRUE)	8%
C5	Rs. 2,50,000	$G$2:$H$5	=VLOOKUP(B5,$G$2:$H$5,2,TRUE)	12%

Approximate Match REQUIRES Sorted Data: For TRUE (approximate match) to work correctly, your lookup column – the first column of your table_array – MUST be sorted in ascending order (smallest to largest). If it is not sorted, VLOOKUP will return completely wrong results with no error message. You will not even know the answers are wrong. Always sort your tier table before using TRUE.

Chapter 5: 12 Real-World VLOOKUP Examples – Office, School & Business

Here are twelve practical VLOOKUP examples from real work situations. Each one shows the scenario, the formula, and what Excel returns.

Example 1 – HR: Find Employee Department by ID

Cell C2 - Department for EmpID in A2:
=VLOOKUP(A2, EmployeeMaster!$A:$E, 3, FALSE)

Searches the EmployeeMaster sheet for the ID in A2 and returns the 3rd column – Department. The ! operator lets VLOOKUP look in a different sheet.

Example 2 – HR: Get Salary from Payroll Sheet

Cell D2 - Salary for EmpID in A2:
=VLOOKUP(A2, $A$2:$F$500, 6, FALSE)

Returns the 6th column value – Salary – for each employee ID. Lock the table range with $ so it does not shift when copied down.

Example 3 – Sales: Find Customer Account Manager

Cell C2 - Account Manager for Customer in A2:
=VLOOKUP(A2, CustomerDB!$A:$D, 4, FALSE)

Pulls the Account Manager name (4th column) from a Customer Database on another sheet using the customer name in A2 as the lookup key.

Example 4 – Finance: Match Invoice Number to Amount

Cell C2 - Invoice Amount for Invoice No. in A2:
=VLOOKUP(A2, InvoiceLog!$A:$C, 3, FALSE)

Cross-references an invoice number against an invoice log sheet and returns the amount. Essential for account reconciliation.

Example 5 – School: Find Student Grade by Roll Number

Cell C2 - Grade for Roll No. in A2:
=VLOOKUP(A2, $F$2:$H$200, 3, FALSE)

Searches a results table using Roll Number and returns the student’s Grade from the 3rd column of that table.

Example 6 – Operations: Find Product Price from Price List

Cell D2 - Price for Product Code in A2:
=VLOOKUP(A2, PriceList!$A:$C, 3, FALSE)

Pulls the price of a product from a price list on another sheet. As prices update on the PriceList sheet, every VLOOKUP referencing it updates automatically.

Example 7 – Finance: Apply Tax Rate Based on Income Slab

Cell C2 - Tax Rate for Income in B2 (approx match):
=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

Uses approximate match (TRUE) to assign a tax rate from a sorted income-slab table. This is one of the best real uses of approximate match.

Example 8 – HR: Attendance Status Lookup

Cell D2 - Status for EmpID in A2:
=VLOOKUP(A2, AttendanceSheet!$A:$D, 4, FALSE)

Pulls the attendance status – Present, Absent, or On Leave – from a separate attendance tracking sheet for each employee ID.

Example 9 – Handle #N/A Errors Cleanly with IFERROR

One of the most important VLOOKUP combinations: wrapping with IFERROR to replace ugly #N/A errors with a clean, readable message.

Cell C2 - Clean Error Handling:
=IFERROR(VLOOKUP(A2, $B$2:$E$500, 3, FALSE), "Not Found")

If VLOOKUP finds a match  --> Returns the result normally
If VLOOKUP returns any error --> Shows "Not Found" instead of #N/A

Example 10 – Combine Two VLOOKUPs for a Dashboard

You can use VLOOKUP inside other formulas. Here, a manager’s dashboard shows a custom message based on the department returned by VLOOKUP:

Cell D2 - Dynamic Department Message:
=IF(VLOOKUP(A2,$B$2:$E$500,3,FALSE)="Sales", "Review Q4 Target", "Standard Review")

Example 11 – Logistics: Find Warehouse Location by SKU

Cell C2 - Warehouse for SKU in A2:
=VLOOKUP(A2, InventoryMaster!$A:$D, 4, FALSE)

Operations teams use this daily to locate where each product SKU is stored across multiple warehouses. The formula saves hours of manual searching.

Example 12 – VLOOKUP Across Two Workbooks

Cell C2 - Data from a separate workbook:
=VLOOKUP(A2, [MasterData2026.xlsx]Sheet1!$A:$E, 3, FALSE)

Note: Keep both workbooks open when using cross-workbook VLOOKUP.
The path updates automatically if both files are in the same folder.

Chapter 6: VLOOKUP Errors – Every Error Explained and Fixed

Why 80% of VLOOKUP Problems Are the Same 4 Errors: After 10 years of working with Excel and helping teams across industries, almost every VLOOKUP ‘not working’ complaint comes down to four errors. Once you know these four, you can diagnose and fix any VLOOKUP problem in under 60 seconds.

Error	What It Looks Like	Root Cause	How to Fix It Right Now
#N/A	=VLOOKUP returns #N/A	Lookup value not found in the first column of table_array	1. Check for extra spaces (use TRIM). 2. Check data types match (both text or both numbers). 3. Wrap in IFERROR for clean output.
#REF!	=VLOOKUP returns #REF!	col_index_num is larger than the number of columns in your table_array	Count your columns again. If table is A:D (4 columns), col_index cannot be 5 or more.
#VALUE!	=VLOOKUP returns #VALUE!	col_index_num is 0 or negative, or table_array is invalid	col_index must be 1 or greater. Check you did not type 0 by mistake.
Wrong results (no error)	Shows a wrong value with no error	range_lookup is TRUE (or omitted) on unsorted data, or column shifts when formula is copied without $ locks	Add FALSE as 4th argument. Lock table_array with $ signs: $A$2:$E$500.
#N/A on numbers	ID 1001 not found even though it exists	One column has numbers stored as text, the other has real numbers	Select the problem column, go to Data > Text to Columns > Finish to convert text-numbers to real numbers.
First match only (duplicate IDs)	VLOOKUP finds wrong record	Duplicate lookup values – VLOOKUP always returns the FIRST match it finds	Remove duplicates first, or use a different approach like INDEX-MATCH or Power Query for multi-match scenarios.

The Hidden Spaces Problem – The Most Sneaky VLOOKUP Trap

Meera’s Invisible Enemy: Meera spent an entire afternoon debugging a VLOOKUP that kept returning #N/A. The IDs looked identical on screen. She compared them letter by letter. They matched. Then a colleague suggested wrapping the lookup value in TRIM.

Instantly – all 200 results appeared. The IDs in her source file had invisible trailing spaces at the end. The eye cannot see them. But VLOOKUP cannot match them. TRIM removes those invisible spaces. This single tip has saved hundreds of hours of debugging.

Fix Hidden Spaces with TRIM:
=VLOOKUP(TRIM(A2), $B$2:$E$500, 3, FALSE)

TRIM removes leading spaces, trailing spaces, and extra spaces between words.
Use it on the lookup_value AND consider cleaning your table_array with TRIM too.

Chapter 7: VLOOKUP Limitations – What It Cannot Do

VLOOKUP is powerful, but it was designed in an era when Excel was simpler. In 2026, it is important to know its limitations – because knowing when NOT to use it is just as valuable as knowing how to use it.

Limitation	What This Means in Practice	Better Alternative
Cannot look LEFT	VLOOKUP only returns values from columns to the RIGHT of the lookup column. If your lookup column is in column C and the data you need is in column A, VLOOKUP cannot reach it.	XLOOKUP or INDEX-MATCH
Breaks when columns are inserted	If you insert a new column inside your table_array, the col_index_num no longer points to the right column and returns wrong data silently.	XLOOKUP uses direct range reference – never breaks
Returns only the FIRST match	If your lookup value appears multiple times, VLOOKUP always returns the first one – even if that is not the one you need.	INDEX-MATCH with MATCH type, or Power Query
Cannot return multiple columns	You need one VLOOKUP formula per column you want to retrieve – doubling or tripling your formula count.	XLOOKUP can return an entire row in one formula
Cannot search horizontally	VLOOKUP only searches columns (vertical). For row-based searches, you need HLOOKUP – a separate function.	XLOOKUP handles both directions
Slower on very large datasets	VLOOKUP scans from top to bottom. On 500,000+ row datasets, it becomes noticeably slow.	XLOOKUP with binary search mode is significantly faster

Chapter 8: VLOOKUP vs XLOOKUP – 2026 Full Comparison

XLOOKUP was introduced by Microsoft as the modern replacement for VLOOKUP. Here is how they compare across every important dimension:

Feature	VLOOKUP	XLOOKUP
Available in	Excel 2003 and later – all versions	Excel 2019 / 2021 / Microsoft 365 only
Lookup direction	Left-to-right only (searches DOWN a column)	Any direction – left, right, up, down
Column reference method	Column index number (breaks when columns shift)	Direct range reference (never breaks)
Default match type	Approximate (dangerous for beginners)	Exact match (safe by default)
Return multiple columns	No – one formula per column	Yes – one formula returns entire row
Built-in error handling	No – needs IFERROR wrapper	Yes – [if_not_found] argument built in
Horizontal search	No (use HLOOKUP)	Yes – same function, any direction
Search from bottom-up	No	Yes – search_mode = -1
Wildcard matching	Yes (approximate match only)	Yes – dedicated match_mode = 2
Formula complexity	Simpler (fewer arguments)	More powerful (6 arguments)
Best for	Compatibility with older Excel	New workbooks on modern Excel

The 2026 Rule of Thumb: If you are creating a new workbook and everyone on your team uses Microsoft 365 or Excel 2021, use XLOOKUP – it is safer, cleaner, and more powerful. If your workbook needs to work on older Excel versions, or you are editing a file made by someone else that already uses VLOOKUP, stick with VLOOKUP. Both skills are valuable in 2026.

Chapter 9: 10 Pro Tips for Writing Better VLOOKUP Formulas

• Always lock your table_array with $ signs – $A$2:$E$500 instead of A2:E500. Without locks, the range shifts when you copy the formula down and starts returning wrong results from the wrong rows.
• Name your lookup table using Formulas > Name Manager. Then write =VLOOKUP(A2, EmpMaster, 3, FALSE). Named ranges make formulas readable and automatically expand when data is added.
• Always add IFERROR around every VLOOKUP in a shared workbook. =IFERROR(VLOOKUP(…), “Not Found”) prevents confusing errors from appearing when someone opens the file.
• Use TRIM to fight invisible spaces. =VLOOKUP(TRIM(A2), …) solves 60% of mysterious #N/A errors where the value looks correct but the formula disagrees.
• Check data types before you debug. A number stored as text (looks like 1001 but is actually ‘1001’) will never match a real number 1001. Select the column, check the format in the Home ribbon.
• When your lookup column has numbers, format both columns as Number, not General. VLOOKUP treats them differently and mixed types cause silent mismatches.
• Reference entire columns sparingly. =VLOOKUP(A2, B:E, 3, FALSE) works but is slower because Excel scans all 1,048,576 rows. Use a specific range like $B$2:$E$5000 for better performance.
• Make col_index_num dynamic using MATCH. =VLOOKUP(A2, $B$2:$Z$500, MATCH(“Salary”, $B$1:$Z$1, 0), FALSE) – the MATCH function finds the column by header name, so your formula never breaks when columns are rearranged.
• Use VLOOKUP inside IF for conditional retrieval. =IF(A2<>””, VLOOKUP(A2,$B:$E,3,FALSE), “”) – only runs VLOOKUP when cell A2 has a value, keeping empty rows clean.
• Test on 5 rows first, then scale up. Write VLOOKUP for 5 rows, manually verify each result against your source data, then copy down to all rows. This prevents discovering a systematic error after 2,000 rows are already filled.

Frequently Asked Questions – Beginners Ask, We Answer

Summary: Your VLOOKUP Quick Reference Card

Arjun’s Monday – Three Months Later: Three months after that 11 PM panic call, Arjun has become the go-to Excel person in his office. When someone needs data matched across sheets, they come to Arjun. He types a VLOOKUP formula, copies it down, and the work is done before the kettle finishes boiling. He has not forgotten that Thursday night. He just made sure no one else in his team has to go through it.

Task	Formula Pattern	Key Setting
Find exact match (IDs, names, codes)	=VLOOKUP(A2, $B$2:$F$500, 3, FALSE)	Always FALSE
Commission / tax slab (range lookup)	=VLOOKUP(B2, $G$2:$H$6, 2, TRUE)	TRUE + sorted data
Search on another sheet	=VLOOKUP(A2, Sheet2!$A:$E, 4, FALSE)	Sheet name + !
Handle errors cleanly	=IFERROR(VLOOKUP(A2,$B:$E,3,FALSE),”Not Found”)	IFERROR wrapper
Fix space issues	=VLOOKUP(TRIM(A2), $B$2:$E$500, 3, FALSE)	TRIM on lookup value
Dynamic column reference	=VLOOKUP(A2,$B:$Z,MATCH(“Col”,B1:Z1,0),FALSE)	MATCH inside VLOOKUP
Search across workbooks	=VLOOKUP(A2,[Book2.xlsx]Sheet1!$A:$E,3,FALSE)	Both files must be open

VLOOKUP is one of those skills that pays for itself the very first time you use it. One formula. Two datasets. Matched together in seconds. Whether you use it for payroll, inventory, sales reports, school results, or invoice reconciliation – the moment it works for the first time, you will understand why millions of Excel users consider it the most valuable formula they ever learned.

Next Steps After VLOOKUP: Once VLOOKUP feels comfortable, your natural next steps are: (1) XLOOKUP – the modern upgrade, (2) INDEX-MATCH – for left lookups and advanced matching, (3) SUMIF and COUNTIF – for conditional aggregation, and (4) Pivot Tables – for summarising large datasets without any formula at all. All of these are covered at ibusinessmotivation.com.

Free Excel Tools – ibusinessmotivation.com: Need to merge, clean, or split Excel files without formulas? Visit ibusinessmotivation.com for free browser-based tools: Multiple Excel File Merger, Excel Data Cleaner, and Excel Worksheet Split Tool. No VBA, no installation, no technical knowledge needed. Works on any device, any browser.