When is it best to use VLOOKUP and OFFSET together?

A VLOOKUP and OFFSET combination is useful when tables with the same structure are repeated by year, version, or section. OFFSET can automatically move the table_array range based on the year or version selected by the user, and VLOOKUP can retrieve a value from that range.

What is the basic formula structure for VLOOKUP and OFFSET?

The basic pattern is VLOOKUP(lookup_value, OFFSET(reference_cell, row_offset, column_offset, height, width), col_index_num, FALSE). OFFSET creates a range that moves based on the year or version, and VLOOKUP uses that range as its table_array to find a value.

What are the benefits of using INDEX instead of OFFSET?

OFFSET is a volatile function, so a workbook can become slow when there are many recalculations. INDEX is non-volatile, so it places less calculation burden on large datasets. When combined with MATCH, it can provide stable dynamic lookups by changing only the row number.

What errors commonly occur when using VLOOKUP and OFFSET together?

A #N/A error often occurs when the lookup value is not in the applicable block or when using the FALSE option. A #REF! error occurs when OFFSET's height and width extend beyond the actual data range. Also, an incorrect year-based row-offset calculation ((year-first year)*number of block rows) can retrieve data from the wrong year.

Can I use VLOOKUP and OFFSET if the number of rows differs by year?

If each year's block has a different number of rows, it is difficult to use a simple (year-first year)*fixed number of rows approach. In this case, it is better to manage the starting row of each year block in a separate table or create a more flexible design using dynamic named ranges, INDEX/MATCH, and structured references (tables).

CONTENTS

How to Use VLOOKUP and OFFSET to Find Values in Moving Ranges (With Practical Examples)

When using VLOOKUP in Excel, it can be frustrating when the range is fixed. You have to keep updating the table_array whenever the year, month, or version changes. This article explains how to use a VLOOKUP and OFFSET combination to automatically find the value you need by moving the range up or down (or by table block).

Using the common real-world example of selecting a year → looking up a value in that year’s block, we will create a formula you can use right away.

Quick Fix: Find a Value Immediately in the Right Section with OFFSET+VLOOKUP

Example Scenario: Look Up Product Unit Prices by Year

Assume that table blocks with the same structure are stacked vertically by year, as shown below.

2023 data: B6:D15 (10 rows × 3 columns)
2024 data: B16:D25 (10 rows × 3 columns)
2025 data: B26:D35 (10 rows × 3 columns)

Each block has the same structure.

Column	Meaning
B	Product code
C	Product name
D	Unit price (for that year)

Set up the user input area as follows.

E2: Year to look up (2023, 2024, or 2025)
E3: Product code to look up
E5: Cell that displays the result (unit price)

Core Formula in One Line

=VLOOKUP(
  $E$3,
  OFFSET($B$6, ($E$2-2023)*10, 0, 10, 3),
  3,
  FALSE
)

The key part of this formula is the following expression in table_array.

OFFSET($B$6, ($E$2-2023)*10, 0, 10, 3)

Starting position: B6 (the first cell of the 2023 data block)
Calculates how many rows to move down based on the year: ($E$2-2023)*10
Height (number of rows): 10
Width (number of columns): 3

In other words:

E2=2023 → OFFSET returns B6:D15
E2=2024 → OFFSET returns B16:D25
E2=2025 → OFFSET returns B26:D35

This automatically moves the range. VLOOKUP then uses that range, so changing only the year lets the same formula automatically retrieve a value from a different block.

Why Use VLOOKUP and OFFSET Together?

The Limitation of a “Fixed-Range VLOOKUP”

=VLOOKUP($E$3, $B$6:$D$15, 3, FALSE)

The problem is that you must manually update $B$6:$D$15 every time the lookup range changes.

2023 → $B$6:$D$15
2024 → $B$16:$D$25
2025 → $B$26:$D$35

As more years are added or the structure changes frequently, formula maintenance costs increase rapidly.

Common Real-World Patterns Where Years, Versions, or Sheets Change

When annual results are accumulated in the same format each year
When prices by promotion version (first, second, third) are listed in the same structure
When tables with the same layout are divided into multiple sections within a worksheet

In these situations, you can have OFFSET calculate the “starting position + offset” and pass the result to VLOOKUP’s table_array. This lets one formula cover multiple sections.

Understanding the Basic Structure of OFFSET and VLOOKUP

OFFSET Argument Structure (reference, rows, cols, height, width)

OFFSET(reference, rows, cols, [height], [width])

reference: The upper-left cell of the reference cell or range
rows: How many rows to move up or down from the reference
cols: How many columns to move right or left from the reference
height: Number of rows in the returned range
width: Number of columns in the returned range

=OFFSET($B$6, 10, 0, 10, 3)

→ Starting with B16, which is 10 rows below B6, this returns a range that is 10 rows × 3 columns. In other words, it refers to B16:D25.

VLOOKUP Argument Structure and the Meaning of table_array

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

lookup_value: The value you want to find (for example, a product code)
table_array: The range where VLOOKUP searches for lookup_value
col_index_num: Which column in table_array to return a value from
range_lookup: Exact match (FALSE) / approximate match (TRUE)

Although table_array typically contains a fixed range such as $B$6:$D$15, you can also use a function to create a dynamic range there. One of the most representative functions for this is OFFSET.

The Concept of “Putting OFFSET in table_array”

The formula we want to create has the following form.

=VLOOKUP(
  lookup_value,
  OFFSET(starting_range, rows_to_move, columns_to_move, range_height, range_width),
  col_index_num,
  FALSE
)

In other words, OFFSET first creates a “moved table range,” and VLOOKUP then uses that result directly as its table_array.

Practical Example 1: Look Up Product Unit Prices from Different Ranges Based on the Selected Year

Step 1 – Create the Sample Data Structure

Prepare the data on the worksheet as follows.

A2: Year
B2: Product code
C2: Product name
D2: Unit price

Data areas (example):

B6:D15 → 2023
B16:D25 → 2024
B26:D35 → 2025

Enter product codes in the first column of each area (column B), product names in column C, and unit prices in column D.

B(Product Code)	C(Product Name)	D(Unit Price_2023)
P001	T-shirt	15000
P002	Shirt	25000
P003	Pants	30000

Step 2 – Enter the Year Selection Cell and Lookup Code

Create a lookup area on the right.

E2: Year (label)
F2: Year input cell (for example, 2023, 2024, or 2025)
E3: Product code (label)
F3: Product code input cell
E5: Unit price (label)
F5: Cell that displays the result (the retrieved unit price)

Step 3 – Move the Range by Year with OFFSET

Assuming each year’s data contains 10 rows:

The 2023 block is B6:D15
2024 is 10 rows below it
2025 is another 10 rows below that

You can calculate the “number of rows to move” from the year as follows.

(F2 - 2023) * 10

Therefore, the OFFSET range that changes automatically based on the year is as follows.

OFFSET($B$6, (F2-2023)*10, 0, 10, 3)

Step 4 – Look Up the Final Unit Price with VLOOKUP

Now enter the final formula in F5.

=VLOOKUP(
  $F$3,
  OFFSET($B$6, ($F$2-2023)*10, 0, 10, 3),
  3,
  FALSE
)

Now, simply changing F2 (year) and F3 (product code) lets the same formula automatically retrieve the applicable value from a different year’s block.

Step 5 – Improve Formula Readability with a Defined Name

Creating a defined name for the OFFSET portion makes the formula much easier to read.

Formulas tab → Name Manager → New
Name: tblYearBlock
Refers to: =OFFSET($B$6, ($F$2-2023)*10, 0, 10, 3)

The VLOOKUP formula now becomes much cleaner.

=VLOOKUP($F$3, tblYearBlock, 3, FALSE)

Practical Example 2: Find a Value n Days After a Reference Date (or n Rows Above or Below)

Daily Inventory Table Structure

Assume the following daily inventory table.

A(Date)	B(Item Code)	C(Inventory Quantity)
2025-01-01	P001	100
2025-01-02	P001	90
2025-01-03	P001	85

Move n Rows from the Reference Date with OFFSET

=OFFSET(
  $C$2,
  MATCH($F$2, $A$2:$A$100, 0) - 1 + $F$3,
  0
)

Use MATCH to find the reference date’s position, then return the inventory value n rows below that position.

Comparison with INDEX Instead of VLOOKUP

=INDEX($C$2:$C$100, MATCH($F$2, $A$2:$A$100, 0) + $F$3)

Because INDEX is non-volatile, the INDEX/MATCH combination may be more suitable for large datasets.

Common Errors with OFFSET+VLOOKUP and How to Fix Them

Symptom	Cause	Solution
#N/A error	The lookup value is not in the applicable block	Check for typos in the product code and verify that exact match (FALSE) is being used
The same or incorrect value appears for every year	The OFFSET row-offset calculation is incorrect	Recheck the (year-first year)*number of block rows portion
Data from the wrong year is retrieved	The reference cell does not match the actual start of the first block	Recheck the B6 coordinate used as the OFFSET reference
#REF! error occurs only for some years	The OFFSET height/width extends beyond the actual data range	Verify that the number of rows and columns in each block matches the actual data
The file becomes slow	OFFSET is a volatile function, which can create overhead when there are many recalculations	For large datasets, switch to INDEX/MATCH or use dynamic arrays/tables

How to Use INDEX/MATCH Instead (Optional)

OFFSET is very useful, but it is a volatile function, so it is recalculated every time Excel calculates. When data grows to tens of thousands of rows or more, this can cause noticeable performance slowdowns.

In that case, consider using INDEX/MATCH + structured references (tables) instead of OFFSET.

Summary and Next Steps

Using a VLOOKUP and OFFSET combination, you can automatically move the table_array range by year, version, or block.
The core pattern is VLOOKUP(lookup_value, OFFSET(reference_cell, row_offset, column_offset, height, width), column_number, FALSE).
If you are concerned about row-based movement, large files, or performance issues, also consider INDEX/MATCH, structured references, and Power Query.

Official documentation:

VLOOKUP 다중 조건 고급 응용 — 중복 제거·OR·부분일치까지