VLOOKUP MATCH 함수로 열 번호 자동 찾기: 동적 VLOOKUP 완전 정리

엑셀에서 VLOOKUP을 쓰다 보면 한 번쯤 이런 고민을 하게 됩니다.

• 열이 하나만 추가됐는데 보고서가 전부 깨지는 문제
• col_index_num 숫자 바꾸다가 또 틀리는 문제
• 헤더 이름만 바꿔서 보고서 열을 바꾸고 싶은데 매번 수식을 수정해야 하는 문제

이 문제를 한 번에 해결해 주는 조합이 바로 VLOOKUP MATCH 함수입니다. VLOOKUP이 값을 가져오는 역할을, MATCH가 열 번호를 자동으로 찾아주는 역할을 맡으면, 열이 추가·삭제돼도 수식은 그대로 두고 헤더 이름만으로 열을 선택하는 동적 VLOOKUP을 만들 수 있습니다.

왜 이제는 “VLOOKUP MATCH 함수” 조합을 알아야 할까?

먼저 구조를 간단히 정리해 보겠습니다.

• VLOOKUP: 테이블의 첫 번째 열에서 기준 값을 찾은 뒤, 오른쪽 N번째 열의 값을 가져오는 함수
• MATCH: 특정 값이 범위 안에서 몇 번째 위치에 있는지 숫자로 알려주는 함수

VLOOKUP의 세 번째 인수인 col_index_num에 MATCH를 넣으면, 열 번호를 직접 숫자로 쓰지 않아도 되고, 헤더 이름만으로 열을 선택할 수 있습니다.

Quick Fix: 정적 VLOOKUP을 동적 VLOOKUP으로 바꾸는 3단계

먼저 이 섹션만 따라 하면 기존에 쓰던 VLOOKUP 수식을 열 선택형 VLOOKUP으로 바꿀 수 있습니다.

Step 1 – 기존 정적 VLOOKUP 구조 이해하기

다음과 같은 매출 표가 있다고 가정해 보겠습니다.

고객코드	2023매출	2024매출	2025매출
C001	10,000	12,000	15,000
C002	8,000	9,500	11,000
C003	7,500	8,000	9,000

이 표가 $F$1:$I$4 범위에 있고, A2 셀에 고객코드(C001 등)가 있다고 할 때, 2024매출(테이블의 3번째 열)을 가져오는 정적 VLOOKUP 수식은 다음과 같습니다.

=VLOOKUP(A2, $F$1:$I$4, 3, FALSE)

여기서 3이 바로 문제의 고정 열 번호입니다.

Step 2 – MATCH로 열 번호 자동 찾기

B1 셀에 “2024매출”이라는 텍스트가 적혀 있다고 가정하고, F1:I1 범위에는 “2023매출”, “2024매출”, “2025매출” 헤더가 있다고 해 보겠습니다. 이때 MATCH 함수만 먼저 테스트해 봅니다.

=MATCH($B$1, $F$1:$I$1, 0)

B1이 “2024매출”이라면 결과는 2가 나오는데, 이는 F=1, G=2, H=3, I=4 중에서 G:I 범위 기준으로 두 번째 열이기 때문입니다. 이 값이 VLOOKUP이 필요로 하는 열 번호가 됩니다.

Step 3 – MATCH를 VLOOKUP 안에 넣어 “열 선택형” 수식 완성

이제 MATCH를 VLOOKUP의 세 번째 인수 자리에 그대로 넣으면 됩니다.

=VLOOKUP(
    $A2,
    $F$1:$I$4,
    MATCH($B$1, $F$1:$I$1, 0),
    FALSE
)

B1을 “2023매출”, “2024매출”, “2025매출”로 바꿀 때마다 MATCH가 열 번호를 다시 계산하고, VLOOKUP은 그 열의 값을 가져옵니다. 이렇게 하면 헤더 셀만 바꿔도 열이 자동으로 바뀌는 동적 VLOOKUP을 만들 수 있습니다.

MATCH 함수 기본기: 문법, match_type, 자주 쓰는 패턴

MATCH 함수의 기본 문법은 다음과 같습니다.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: 찾을 값 (예: “2024매출”)
• lookup_array: 이 값을 찾을 범위 (예: $F$1:$I$1)
• match_type:
  • 0: 정확히 일치하는 값 찾기 (VLOOKUP MATCH 함수 조합에서 가장 자주 사용)
  • 1: 오름차순 정렬된 범위에서 작거나 같은 최대값 찾기
  • -1: 내림차순 정렬된 범위에서 크거나 같은 최소값 찾기

VLOOKUP MATCH 함수 조합에서는 헤더를 찾는 용도이므로 대부분 match_type=0을 사용하는 것이 안전합니다.

VLOOKUP MATCH 함수 실무 예제 1: 연도별 매출 열 자동 선택

이번에는 연도별 매출을 열로 가지고 있는 표를 예로 들어 보겠습니다.

고객코드	2022매출	2023매출	2024매출	2025매출
C001	8,000	10,000	12,000	14,000
C002	7,500	9,000	9,800	11,500
C003	5,000	6,500	7,200	8,000

이 표는 $F$1:$J$4 범위에 있고, A열과 B열에는 다음과 같은 조회 영역이 있다고 하겠습니다.

고객코드 (A열)	기준연도 (B열)	선택 연도 매출 (C열)
C001	2023매출	?
C002	2024매출	?
C003	2022매출	?

MATCH로 열 번호 계산

C2 셀에 다음 수식을 입력해 MATCH만 먼저 테스트합니다.

=MATCH($B2, $F$1:$J$1, 0)

B2가 “2023매출”이라면 결과는 3이 됩니다.

VLOOKUP MATCH 함수 조합 완성

=VLOOKUP(
    $A2,
    $F$1:$J$4,
    MATCH($B2, $F$1:$J$1, 0),
    FALSE
)

이 수식을 C2~C4까지 복사하면 각 행의 기준연도에 맞춰 해당 연도의 매출을 자동으로 가져오는 동적 VLOOKUP이 완성됩니다.

VLOOKUP MATCH 함수 실무 예제 2: 지점별 재고·안전재고 열 전환

이번에는 지점별 재고와 안전재고를 열로 가지고 있는 예제를 보겠습니다.

상품코드	서울_재고	부산_재고	대구_재고	서울_안전재고
P001	100	50	30	80
P002	60	40	20	50
P003	30	20	10	25

보고서 영역은 다음과 같습니다.

상품코드 (A열)	지점 선택 (B열)	지표 선택 (C열)	결과 값 (D열)
P001	서울	재고	?
P001	서울	안전재고	?
P002	부산	재고	?

지점+지표를 결합한 헤더 만들기

D2 셀에 다음과 같이 결합 키를 만들어 봅니다.

=B2 & "_" & C2

이 값은 “서울_재고”, “서울_안전재고”처럼 실제 헤더와 동일한 규칙입니다.

VLOOKUP MATCH 함수 조합

이제 결과 셀에 다음 수식을 사용합니다.

=VLOOKUP(
    $A2,
    $F$1:$J$4,
    MATCH($B2 & "_" & $C2, $F$1:$J$1, 0),
    FALSE
)

지점과 지표를 바꿔 입력하는 것만으로도 재고/안전재고 값이 자동으로 전환되는 보고서를 만들 수 있습니다.

MATCH + VLOOKUP vs INDEX + MATCH: 언제까지 VLOOKUP을 써도 될까?

MATCH 함수는 종종 INDEX와 함께 소개되지만, 기존에 VLOOKUP을 많이 사용하고 있는 환경이라면 먼저 VLOOKUP MATCH 함수 조합으로 안정화하는 것도 좋은 전략입니다.

• lookup_value가 항상 첫 번째 열에 있고 그 구조를 바꿀 계획이 없을 때 → VLOOKUP MATCH로 충분
• 왼쪽 또는 오른쪽 어느 열이라도 기준이 될 수 있는 구조일 때 → INDEX MATCH 또는 XLOOKUP 고려

새 프로젝트이면서 구조가 자주 바뀔 것 같다면 INDEX MATCH로 설계하는 것이 낫고, 기존 파일을 빠르게 개선하는 단계라면 VLOOKUP MATCH 함수 조합만으로도 업무 효율을 크게 올릴 수 있습니다.

자주 발생하는 오류와 Troubleshooting (VLOOKUP MATCH 함수 전용)

증상	원인(추정)	해결 포인트
#N/A 오류가 뜨고 MATCH가 열 번호를 못 찾음	선택한 헤더 텍스트와 실제 헤더가 다름, 숨은 공백	TRIM으로 공백 제거, 데이터 유효성 검사로 선택값 제한
MATCH 결과가 엉뚱한 숫자나 #N/A가 자주 나옴	match_type이 1 또는 -1로 설정되어 정렬 전제가 맞지 않음	헤더 찾을 때는 항상 match_type=0 사용
새 열을 추가했더니 결과가 이상하게 나옴	MATCH 범위에 새 헤더가 포함되지 않음	MATCH의 lookup_array 범위를 충분히 넉넉하게 확장
VLOOKUP 결과가 항상 한 열씩 밀려 보임	table_array와 헤더 범위의 시작/끝 열이 맞지 않음	VLOOKUP의 table_array와 MATCH의 lookup_array 정렬
MATCH는 맞는데 VLOOKUP에서만 오류 발생	VLOOKUP의 range_lookup이 TRUE로 되어 근사값 모드 실행	네 번째 인수를 FALSE로 강제 지정
헤더가 병합 셀로 되어 있어서 MATCH가 잘 작동하지 않음	병합 셀 구조로 인해 기준 텍스트 위치가 불명확	헤더는 병합 없이 단일 셀 구조로 재설계

마무리: VLOOKUP MATCH 함수 조합을 자기 것으로 만드는 연습 루틴

VLOOKUP MATCH 함수 조합은 구조만 이해하면 어렵지 않지만, 실제로 손으로 만들어 본 예제가 몇 개 쌓일수록 감각이 빨리 올라옵니다.

• 기존 파일에서 정적 VLOOKUP 하나를 골라 MATCH를 끼워 넣어 보기
• 헤더 셀을 드롭다운으로 만들어 선택값에 따라 결과가 바뀌는지 확인
• 연도별 매출표, 지점별 재고표처럼 열이 많은 테이블에 패턴 확장

VLOOKUP MATCH 함수 조합에 익숙해지면, 앞으로는 “이 보고서는 열이 자주 바뀌니까 VLOOKUP MATCH로 짜야겠다”라는 판단이 자연스럽게 따라오게 됩니다.

시리즈 1편과 아래 글들을 함께 참고하면 이해가 훨씬 빠릅니다.

• VLOOKUP 함수 조합 완전 정리: 조회 문제 20가지 패턴 한 번에 끝내기
• VLOOKUP 다중 조건 완전 정리
• TEXT 함수와 함께 쓰는 VLOOKUP 코드 정리 팁
• INDEX MATCH로 VLOOKUP 한계 넘기기
• VLOOKUP 오류(#N/A, #VALUE!) 해결법 총정리

VLOOKUP 함수와 함께 쓰면 업무가 쉬워지는 20가지 함수 조합 완전 정리