VLOOKUP OFFSET 함수 조합은 언제 사용하는 것이 좋나요?

VLOOKUP OFFSET 함수 조합은 연도별, 버전별, 구역별로 같은 구조의 테이블이 반복될 때 유용합니다. 사용자가 선택한 연도나 버전에 따라 OFFSET으로 table_array 범위를 자동으로 이동시키고, VLOOKUP이 그 범위에서 값을 조회하도록 만들 수 있습니다.

VLOOKUP OFFSET 함수 조합의 기본 공식 구조가 어떻게 되나요?

기본 패턴은 VLOOKUP(lookup_value, OFFSET(기준셀, 이동행, 이동열, 높이, 너비), col_index_num, FALSE)입니다. OFFSET이 연도나 버전에 따라 움직이는 범위를 만들어 주고, VLOOKUP이 그 범위를 table_array로 사용해 값을 찾아옵니다.

OFFSET 대신 INDEX를 사용하면 어떤 장점이 있나요?

OFFSET 함수는 휘발성 함수라 재계산이 많을 때 파일이 느려질 수 있습니다. INDEX 함수는 휘발성이 아니므로 대용량 데이터에서 계산 부담이 적고, MATCH와 조합하면 행 번호만 바꿔 주는 형태로 안정적인 동적 조회가 가능합니다.

VLOOKUP OFFSET 함수 조합에서 자주 발생하는 오류는 무엇인가요?

#N/A 오류는 조회값이 해당 블록에 없거나 FALSE 옵션일 때 자주 발생하고, #REF! 오류는 OFFSET의 높이와 너비가 실제 데이터 범위를 벗어날 때 나타납니다. 또한 연도별 이동행 계산식((연도-첫연도)*블록행수)이 잘못되면 엉뚱한 연도 데이터가 조회될 수 있습니다.

연도별로 행 개수가 다를 때도 VLOOKUP OFFSET 조합을 사용할 수 있나요?

연도별 블록의 행 개수가 다르면 단순히 (연도-첫연도)*고정행수 방식은 쓰기 어렵습니다. 이 경우에는 각 연도 블록의 시작행을 별도 표로 관리하거나, 동적 이름 정의와 INDEX/MATCH, 구조화 참조(표) 등을 함께 사용하여 보다 유연한 구조로 설계하는 것이 좋습니다.

CONTENTS

VLOOKUP OFFSET 함수 조합으로 움직이는 범위에서 값 찾는 법 (실무 예제 포함)

엑셀에서 VLOOKUP을 쓰다 보면 범위가 고정돼 있어서 답답할 때가 많습니다. 연도·월·버전이 바뀔 때마다 table_array를 계속 고쳐야 하죠. 이 글에서는 VLOOKUP OFFSET 함수 조합을 이용해서 범위를 위·아래(또는 블록 단위)로 움직이며 원하는 값을 자동으로 찾는 방법을 정리합니다.

실무에서 많이 쓰는 연도 선택 → 해당 연도 블록에서 값 조회 예제를 기준으로, 바로 쓸 수 있는 공식을 만들어 보겠습니다.

Quick Fix: OFFSET+VLOOKUP으로 원하는 구역에서 바로 값 찾기

예제 시나리오: 연도별 제품 단가 조회

아래처럼 연도별로 같은 구조의 테이블 블록이 세로로 이어져 있다고 가정합니다.

2023년 데이터: B6:D15 (10행 × 3열)
2024년 데이터: B16:D25 (10행 × 3열)
2025년 데이터: B26:D35 (10행 × 3열)

각 블록의 구조는 같습니다.

열	의미
B	제품코드
C	제품명
D	단가(해당 연도)

사용자 입력 영역은 이렇게 둡니다.

E2: 조회할 연도 (2023, 2024, 2025 중 하나)
E3: 조회할 제품코드
E5: 결과(단가)를 표시할 셀

핵심 공식 한 줄 요약

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

이 공식의 핵심은 table_array 부분에 있는 다음 구문입니다.

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

기준 위치: B6 (2023년 데이터 블록의 첫 셀)
연도에 따라 몇 행 아래로 내려갈지 계산: ($E$2-2023)*10
높이(행 수): 10줄
너비(열 수): 3열

즉,

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

으로 범위를 자동으로 이동해 줍니다. 그 범위를 VLOOKUP이 사용해서, 같은 공식으로도 연도만 바꾸면 자동으로 다른 블록에서 값을 가져오는 구조가 됩니다.

왜 VLOOKUP OFFSET 함수 조합이 필요한가?

“범위가 고정된 VLOOKUP”의 한계

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

문제는 조회할 범위가 바뀔 때마다 $B$6:$D$15를 직접 수정해야 한다는 점입니다.

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

연도가 늘어나거나 구조가 자주 바뀌면 수식 유지보수 비용이 급격히 증가합니다.

연도·버전·시트가 바뀌는 실무 패턴

매년 같은 포맷으로 연도별 실적을 쌓아두는 경우
프로모션 버전별(1차, 2차, 3차) 가격이 같은 구조로 나열된 경우
같은 레이아웃의 테이블이 시트 안에서 여러 구역에 나뉘어 있는 경우

이럴 때 OFFSET으로 “기준 위치 + 이동량”을 계산하게 하고, 그 결과를 VLOOKUP의 table_array에 넘기면 하나의 공식으로 여러 구역을 커버할 수 있습니다.

OFFSET 함수와 VLOOKUP 기본 구조 이해하기

OFFSET 인수 구조 (참조, rows, cols, height, width)

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

reference: 기준 셀(또는 범위)의 왼쪽 위
rows: 기준 위치에서 몇 행 위/아래로 이동할지
cols: 기준 위치에서 몇 열 오른쪽/왼쪽으로 이동할지
height: 반환할 범위의 행 수
width: 반환할 범위의 열 수

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

→ B6에서 아래로 10행 내려간 B16을 시작점으로, 10행 × 3열 범위를 반환합니다. 즉, B16:D25 범위를 의미합니다.

VLOOKUP 인수 구조와 table_array의 의미

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

lookup_value: 찾고 싶은 값 (예: 제품코드)
table_array: lookup_value를 찾을 범위
col_index_num: table_array 안에서 몇 번째 열의 값을 가져올지
range_lookup: 정확히 일치(FALSE) / 근사값(TRUE)

보통 table_array에 $B$6:$D$15처럼 고정 범위를 넣지만, 사실 여기에 함수를 써서 동적으로 범위를 만들어 넣어도 됩니다. 그 대표적인 함수가 바로 OFFSET입니다.

“table_array 자리에 OFFSET을 넣는다”는 개념

우리가 만들고 싶은 것은 아래 같은 형태입니다.

=VLOOKUP(
  lookup_value,
  OFFSET(기준범위, 이동할_행, 이동할_열, 범위_높이, 범위_너비),
  col_index_num,
  FALSE
)

즉, 먼저 OFFSET으로 “이동된 테이블 범위”를 만들고, 그 결과를 VLOOKUP이 그대로 table_array로 사용하게 만드는 방식입니다.

실무 예제 1: 연도 선택에 따라 다른 범위에서 제품 단가 조회하기

1단계 – 샘플 데이터 구조 만들기

아래와 같이 시트에 데이터를 준비합니다.

A2: 연도
B2: 제품코드
C2: 제품명
D2: 단가

데이터 영역(예시):

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

각 영역의 첫 열(B열)에 제품코드, C열에 제품명, D열에 단가를 입력합니다.

B(제품코드)	C(제품명)	D(단가_2023)
P001	티셔츠	15000
P002	셔츠	25000
P003	팬츠	30000

2단계 – 연도 선택 셀과 조회 코드 입력

오른쪽에 조회 영역을 만듭니다.

E2: 연도 (라벨)
F2: 연도 입력 셀 (예: 2023, 2024, 2025)
E3: 제품코드 (라벨)
F3: 제품코드 입력 셀
E5: 단가 (라벨)
F5: 결과(조회된 단가)를 표시할 셀

3단계 – OFFSET으로 연도별 범위 이동

연도별로 데이터가 10행씩 있다고 가정하면,

2023년 블록은 B6:D15
2024년은 그 아래 10행
2025년은 다시 그 아래 10행

“이동할 행 수”를 연도에서 계산하면 다음과 같습니다.

(F2 - 2023) * 10

따라서, 연도에 따라 자동으로 변하는 OFFSET 범위는 아래와 같습니다.

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

4단계 – VLOOKUP으로 최종 단가 조회

이제 F5에 최종 공식을 입력합니다.

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

이제 F2(연도)와 F3(제품코드)만 바꾸면, 같은 공식으로도 다른 연도 블록에서 해당 값을 자동으로 찾아올 수 있습니다.

5단계 – 이름 정의로 수식 가독성 높이기

OFFSET 부분을 이름 정의로 만들면 훨씬 보기 좋습니다.

수식 탭 → 이름 관리자 → 새로 만들기
이름: tblYearBlock
참조 대상: =OFFSET($B$6, ($F$2-2023)*10, 0, 10, 3)

이제 VLOOKUP 공식은 아래처럼 깔끔해집니다.

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

실무 예제 2: 기준일에서 n일 뒤(또는 위·아래 행)의 값을 찾기

일자별 재고 테이블 구조

다음과 같은 일자별 재고 테이블을 가정합니다.

A(일자)	B(품목코드)	C(재고수량)
2025-01-01	P001	100
2025-01-02	P001	90
2025-01-03	P001	85

OFFSET으로 기준일에서 n행 이동

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

기준일 위치를 MATCH로 찾고, 그 위치에서 n행 아래로 이동한 재고 값을 읽어옵니다.

VLOOKUP 대신 INDEX와 비교

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

INDEX는 휘발성이 아니므로, 대용량 데이터에서는 INDEX/MATCH 조합이 더 유리할 수 있습니다.

OFFSET+VLOOKUP 조합에서 자주 발생하는 오류와 해결법

증상	원인	해결법
#N/A 오류 발생	조회값이 해당 블록에 없음	제품코드 오타 확인, 정확히 일치(FALSE) 사용 여부 점검
모든 연도에서 값이 같게 나오거나 틀림	OFFSET의 행 이동 계산식이 잘못됨	(연도-첫연도)*블록행수 부분 재확인
엉뚱한 연도 데이터가 조회됨	기준 셀 위치가 실제 첫 블록 시작점과 다름	OFFSET 기준이 되는 B6 좌표 다시 점검
일부 연도에서만 #REF! 오류	OFFSET height/width가 실제 데이터 범위를 벗어남	블록 행 수/열 수가 실제 데이터와 일치하는지 확인
파일이 느려짐	OFFSET는 휘발성 함수라 재계산이 많을 때 부담 발생	대용량은 INDEX/MATCH로 전환, 동적 배열/테이블 활용

INDEX/MATCH로 대체하는 방법(옵션)

OFFSET은 매우 유용하지만, 휘발성(volatile) 함수라서 모든 계산 시점마다 다시 평가됩니다. 데이터가 수만 행 이상으로 커지면 체감 성능 저하가 발생할 수 있습니다.

이때는 OFFSET 대신 INDEX/MATCH + 구조화 참조(표)를 사용하는 방식도 고민해 볼 수 있습니다.

정리 및 다음 단계

VLOOKUP OFFSET 함수 조합을 이용하면 table_array 범위를 연도/버전/블록 단위로 자동 이동시킬 수 있습니다.
핵심 패턴은 VLOOKUP(조회값, OFFSET(기준셀, 이동행, 이동열, 높이, 너비), 열번호, FALSE)입니다.
행 단위 이동, 대용량 파일, 성능 이슈가 걱정된다면 INDEX/MATCH, 구조화 참조, Power Query 등을 함께 고려해 보세요.

VLOOKUP OFFSET 함수 조합으로 움직이는 범위에서 값 찾기