SheetDo
English
Home / Formulas / VLOOKUP function
Lookup & Reference Excel + Google Sheets

VLOOKUP function

VLOOKUP is the most famous lookup formula in spreadsheets — and the easiest to break. Learn the 4 arguments, how approximate match differs from exact match, how to avoid fragile column indexes, and test your skills live.

VLOOKUP Syntax & The 4 Arguments

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP (Vertical Lookup) scans the leftmost column of your table vertically, finds the match, and returns the corresponding value from the column you designate.

1 lookup_value
Required

The value to search for (e.g. SKU in E2, an employee name, or a number). This value MUST exist in the leftmost column of table_array.

2 table_array
Required

The range of cells containing your lookup data (e.g. A2:D6). Always lock this range with $ ($A$2:$D$6) when filling across rows or columns.

3 col_index_num
Required

The column number in the table_array to retrieve data from (starting at 1 for the first column). For A2:C6, column A is 1 and C is 3.

4 range_lookup
Optional

Match type: FALSE (or 0) for an exact match (essential for IDs, names, SKUs). TRUE (or 1, or omitted) for approximate match (tax tiers, graded ranges).

Excel VLOOKUP example

Column A lists product SKUs, column B lists names, and column C lists unit prices. Cell E2 holds the SKU to find: SKU-102. This Excel VLOOKUP example uses an exact match.

SKUProductUnit price
SKU-101Notebook12
SKU-102Pen set8
SKU-103Binder15
SKU-104Stapler9
=VLOOKUP(E2, A2:C5, 3, FALSE)

VLOOKUP searches the first column of A2:C5 for SKU-102 and returns 8 from column 3 of the same row. FALSE (or 0) forces an exact match. Leave the fourth argument out and Excel VLOOKUP switches to approximate match, which can return the wrong row on unsorted data without raising an error. A missing SKU, a number stored as text, or a stray space returns #N/A.

4 Critical VLOOKUP Gotchas & Pitfalls

1. Approximate match by default (The silent disaster)

If you omit range_lookup, VLOOKUP defaults to TRUE. On unsorted text data, it won't throw an error — it silently returns an incorrect row! Always explicitly type FALSE or 0 for exact lookups.

2. Left-to-right only (Cannot look backwards)

The lookup value must reside in the first column of table_array. If your search key is in column C and you need data from column A, VLOOKUP cannot do it. Use INDEX+MATCH or XLOOKUP instead.

3. Hardcoded column index numbers break easily

col_index_num is static (e.g. 3). If someone inserts a column into your table, VLOOKUP still reads column 3, returning wrong data or #REF! without any syntax warning.

4. #N/A from format mismatch (Text vs Number)

An ID stored as text ('1001') will never match a numeric 1001. Trailing or leading spaces will also cause lookup misses. Wrap with TRIM() or VALUE() if data is dirty.

Lookup Showdown: VLOOKUP vs XLOOKUP vs INDEX+MATCH

VLOOKUP

The classic workhorse. Universal compatibility across all legacy workbooks and the #1 most tested formula in job interviews. Drawbacks: left-to-right only and fragile column indexes.

XLOOKUP

The modern standard. Looks left or right, includes built-in not_found fallback, defaults to exact match, and doesn't break when columns are inserted.

INDEX + MATCH

The bulletproof veteran. =INDEX(A2:A6, MATCH(E2, C2:C6, 0)). Unaffected by column insertions, handles left lookups, and works in any version of Excel or Sheets.

Excel vs Google Sheets Features

Microsoft Excel (365 / Desktop)

In Microsoft 365, VLOOKUP supports array constants to return multiple columns at once: =VLOOKUP(F2, A2:D6, {2, 3, 4}, FALSE). Legacy Excel requires separate column formulas.

Google Sheets

Google Sheets supports full-column expansion via =ARRAYFORMULA(VLOOKUP(E2:E, A2:D, 3, FALSE)), calculating thousands of rows from a single top formula.

VLOOKUP Hands-on Practice: Look up Unit Price by SKU

Type the formula directly into the locked spreadsheet below for millisecond feedback, or open the standalone full-screen challenge.

Open full challenge
Description
Hints 0/4
Solutions 2
–
Test case
1
2
3
Reset
Submit
fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
Loading interactive spreadsheet...
Preparing grid cells and formula calculation engine
○ Ready Enter formula in highlighted cells, then click Submit.

Recommended VLOOKUP Challenges

Master exact match, approximate tax tiers, multi-column retrieval, and IFERROR fallback handling in real business scenarios.

Medium Lookup & Reference

Find tax bracket using approximate match (VLOOKUP)

HR needs to compute the withholding tax rate for each employee's annual taxable income. The reference table in columns A–B lists income thresholds in ascending order (0, $30,000, $60,000, $100,000) and their corresponding tax rates. Fill the Tax rate cells F2:F4 using VLOOKUP with approximate match so each employee is assigned the rate matching their income tier in E2:E4.
Easy Lookup & Reference

Look up an employee's department (VLOOKUP)

You're in an HR screen and the interviewer slides over an employee directory: IDs, names and departments in A2:C5. Given the ID in E2, write a formula in the Department cell F2 that reports which department that employee works in.
Medium Lookup & Reference

Pull multiple columns for an employee (VLOOKUP)

You are building an employee profile card. Given an employee ID in F2, pull their Department into G2 and City into H2 from the master employee directory in columns A–D. Lock the table reference with $ so the lookup formula remains stable across columns.
Medium Lookup & Reference

Default to zero on missing discount (VLOOKUP + IFERROR)

You are processing store checkouts. Members get a discount based on their VIP tier in columns A–B (Bronze 5%, Silver 10%, Gold 15%, Platinum 20%). Non-VIP customers have standard tiers like 'Regular' or 'None' that are NOT in the VIP table, causing a naked VLOOKUP to fail with #N/A. Fill the Discount cells E2:E5 using VLOOKUP wrapped in IFERROR (or IFNA) so missing tiers safely default to 0.
Medium Data Cleaning

Handle missing SKUs in a lookup (IFERROR)

Customer service forwarded an order list, and one of the SKUs was discontinued — it is not in the price list anymore, so a plain lookup would blow up with an error in the middle of your report. Fill the Unit price cells F2:F4 so every order row either shows the matched price or the text Not found.
Easy Lookup & Reference

Flag a double-booked order (COUNTIF)

A customer swears they were charged twice, and the order log looks suspicious: order ID ORD-1042 might have been booked more than once. Before refunding anything, count how many rows in A2:A11 carry that exact ID. The disputed ID is already sitting in D1. Put the answer in the Occurrences cell D2.

FAQ

How does VLOOKUP work in Excel and Google Sheets?

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches the first column of table_array for lookup_value, then returns the value in the same row from col_index_num.

Why does VLOOKUP return #N/A?

1) The lookup value does not exist in the table's first column. 2) Leading or trailing spaces in the text. 3) Type mismatch (numbers stored as text vs real numbers). 4) Forgetting FALSE as the 4th argument on unsorted data.

What is the difference between TRUE and FALSE in VLOOKUP?

FALSE (or 0) requires an exact match. TRUE (or 1, or omitted) uses approximate match, requiring the lookup table to be sorted ascending to find the closest smaller threshold (like tax brackets).

How do I do a VLOOKUP in Google Sheets across an entire column?

Google Sheets supports =ARRAYFORMULA(VLOOKUP(E2:E, A2:C, 3, FALSE)) which calculates for every row automatically without dragging down.

Should I use XLOOKUP or VLOOKUP?

If your team uses modern Excel (Microsoft 365) or Google Sheets, XLOOKUP is safer: it looks in any direction, doesn't break when columns are inserted, and defaults to exact match. VLOOKUP remains essential for legacy workbooks (.xls / .xlsx) and interview tests.