Formulas - 2026-05-12

VLOOKUP in Excel: Complete Tutorial

Learn VLOOKUP in Excel with exact match examples, common #N/A fixes, column index tips, and when to switch to XLOOKUP.

Quick answer: vlookup tutorial

  • To learn VLOOKUP, build a four-column table (ID, Name, Category, Price), put the lookup ID in G2, and use =VLOOKUP(G2,$A$2:$D$100,4,FALSE).
  • Practice three mini tables: products, employees, invoices.
  • Next: [VLOOKUP function](/functions/vlookup/) · [#N/A fixes](/problems/vlookup-returning-na/).

Overview

  • This tutorial walks from blank sheet to a working VLOOKUP in about 15 minutes. You will build three mini tables and practice exact match before touching XLOOKUP.

Practice workbook layout

  • Sheet1 Products: columns A=ProductID, B=Name, C=Category, D=Price (rows 2–100).
  • Put test ID in G2; formula =VLOOKUP(G2,$A$2:$D$100,4,FALSE) in H2.
  • Repeat for Employees (department) and Invoices (tax rate).

Five steps every beginner should follow

  • 1. Put the lookup value in one cell (G2).
  • 2. Select table_array including headers only if you intend to.
  • 3. Count columns to the return field for col_index_num.
  • 4. Type FALSE for exact match.
  • 5. Copy down and verify three known IDs.

When you finish this tutorial

  • Deep dive: [VLOOKUP function](/functions/vlookup/).
  • If you see #N/A: [VLOOKUP returning #N/A](/problems/vlookup-returning-na/).
  • Modern path: [XLOOKUP tutorial](/blog/xlookup-excel-tutorial/).

What VLOOKUP Does

  • VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. Basic syntax: \\\ =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) \\\ Use FALSE for exact match in most business spreadsheets.

VLOOKUP Example

  • If product IDs are in A2:A100 and prices are in C2:C100: \\\ =VLOOKUP(E2,A2:C100,3,FALSE) \\\ Excel searches for E2 in the first column of A2:C100 and returns the value from the third column.

Common VLOOKUP Problems

  • Lookup value is not in the first column Exact match is missing Hidden spaces in the lookup value Numbers stored as text Wrong column index number Inserted columns changing the return result

VLOOKUP vs XLOOKUP

  • VLOOKUP is still common in older workbooks, but XLOOKUP is easier and more flexible. XLOOKUP can search left, return custom not-found messages, and does not require counting columns.

Related Guides

  • VLOOKUP function VLOOKUP Returning #N/A? XLOOKUP vs VLOOKUP XLOOKUP tutorial

Real Workbook Example

  • Imagine a pricing table where product IDs are in column A and prices are in column D. \\\ =VLOOKUP(G2,$A$2:$D$500,4,FALSE) \\\ The locked table range keeps the lookup stable when copied down. The final FALSE forces exact match, which is usually required for product IDs, employee IDs, invoice numbers, and customer codes. Argument Example What it controls lookup_value G2 The value to find table_array $A$2:$D$500 The lookup table col_index_num 4 The return column range_lookup FALSE Exact match behavior

When VLOOKUP Is Still the Right Choice

  • Use VLOOKUP when maintaining older workbooks, training users who already know it, or keeping compatibility with Excel versions that do not support XLOOKUP. For new Microsoft 365 models, use XLOOKUP for cleaner formulas.

Common Audit Checks

  • Confirm the lookup column is the first column in the table_array Use absolute references for lookup tables Use FALSE for exact match Replace hardcoded column numbers when the table structure changes Use IFERROR only after confirming the lookup logic is correct

Downloadable practice structure

  • Recreate three mini tables in one sheet: Products (ID, Name, Price), Employees (ID, Department), Invoices (CustomerCode, TaxRate). Practice exact-match VLOOKUP on each before moving to XLOOKUP. Next: VLOOKUP function · #N/A fixes · XLOOKUP vs VLOOKUP.

Frequently asked questions

  • What does VLOOKUP do in Excel? VLOOKUP searches the first column of a table and returns a value from another column in the same row.
  • Why is VLOOKUP returning #N/A? The lookup value may be missing, stored as a different data type, affected by hidden spaces, or the formula may not be using exact match.
  • What is the fourth argument in VLOOKUP? The fourth argument controls match behavior. Use FALSE for exact match in most business spreadsheets.
  • Can VLOOKUP look to the left? No. VLOOKUP can only return values to the right of the lookup column. Use XLOOKUP or INDEX MATCH for left lookups.