VLOOKUP과 INDEX-MATCH로 데이터 찾기
여러 시트의 데이터를 연결하는 고급 함수 기술을 배웁니다.
지금까지 한 시트 안에서만 데이터를 정리했어요. 하지만 현실은 다르더라고요.
한국 회사에서는 데이터가 여러 곳에 흩어져 있어요:
- Sheet1: 판매 데이터 (주문번호, 판매량, 판매일)
- Sheet2: 고객 정보 (고객ID, 고객명, 주소)
- Sheet3: 상품 정보 (상품코드, 상품명, 가격)
그럼 이 데이터들을 어떻게 합칠까요? 손으로 하나하나 찾으면서 입력할 건가요? 아니에요. VLOOKUP이라는 함수가 있어요.
VLOOKUP의 마법
VLOOKUP은 “세로로 찾는다"는 뜻이에요. (V = Vertical)
기본 형태:
=VLOOKUP(찾는 값, 데이터 범위, 반환할 열 번호, [정확한 일치 여부])
예시를 봐봐요.
예시 1: 고객ID로 고객명 찾기
고객 데이터 시트:
고객ID 고객명 주소
001 김철수 서울
002 이영희 부산
003 박준호 대구
판매 데이터 시트:
주문번호 고객ID 판매량
A001 002 100
A002 001 200
A003 003 150
판매 데이터에 고객명을 추가하고 싶어요. 어떻게 할까요?
=VLOOKUP(B2, '고객 데이터'!A:C, 2, FALSE)
이 수식이:
- B2에 있는 고객ID(002)를 찾아서
- ‘고객 데이터’ 시트의 A:C 범위에서
- 2번째 열(고객명)의 값을 가져온다
고 거든요.
결과:
주문번호 고객ID 판매량 고객명
A001 002 100 이영희
A002 001 200 김철수
A003 003 150 박준호
완성이에요! 마법이죠?
예시 2: 상품 코드로 가격 찾기
상품 정보:
상품코드 상품명 가격
P001 마우스 30000
P002 키보드 80000
P003 모니터 250000
판매 기록:
판매일 상품코드 수량
2026-01-15 P001 5
2026-01-16 P002 2
2026-01-17 P003 1
판매 기록에 각 상품의 가격을 추가하면:
=VLOOKUP(B2, '상품정보'!A:C, 3, FALSE)
결과:
판매일 상품코드 수량 가격
2026-01-15 P001 5 30000
2026-01-16 P002 2 80000
2026-01-17 P003 1 250000
이제 각 판매의 총액도 쉽게 구할 수 있어요. 수량 × 가격이니까요.
VLOOKUP의 변형들
HLOOKUP (가로로 찾기)
데이터가 가로로 배열되어 있다면?
1월 2월 3월
영업1 100 120 150
영업2 80 90 95
이럴 때는 HLOOKUP을 써요. (H = Horizontal)
=HLOOKUP("2월", A1:D3, 2, FALSE)
이렇게 하면 영업1의 2월 매출(120)을 가져와요.
정확한 일치 vs 근사 일치
VLOOKUP의 마지막 부분(FALSE나 TRUE)은 정확한 일치를 할 건지 결정해요.
FALSE(또는 0): 정확한 일치만 찾아요. 없으면 #N/A 오류TRUE(또는 1): 없으면 그 아래의 가장 큰 값을 반환해요
한국 회사에서는 대부분 FALSE를 써요. 고객ID는 정확해야 하거든요.
INDEX-MATCH: VLOOKUP의 상위 호환
VLOOKUP에는 문제가 있어요. 찾으려는 값이 왼쪽에 있고, 반환하려는 값이 오른쪽에 있어야 한다는 거예요.
만약 반대라면? 예를 들어:
A(고객명) B(주소) C(고객ID)
김철수 서울 001
여기서 고객명으로 고객ID를 찾고 싶다면? VLOOKUP으로는 못 해요. C가 A의 오른쪽에 없거든요.
그럼 INDEX-MATCH를 써요:
=INDEX(반환할 범위, MATCH(찾는 값, 찾을 범위, 0))
예시:
=INDEX(고객정보!C:C, MATCH(A2, 고객정보!A:A, 0))
이 수식은:
- A2에 있는 고객명(예: “김철수”)을
- 고객정보 시트의 A열에서 찾아서
- 같은 행의 C열(고객ID) 값을 반환한다
거든요.
실전: 한국 회사의 분기 보고서
이제 실제 한국 회사 상황을 봐봐요.
데이터 소스들
Sheet1: 거래 데이터
거래번호 거래처ID 담당자ID 거래액 거래일
T001 C001 EMP001 50000000 2026-01-15
T002 C002 EMP002 35000000 2026-01-20
T003 C001 EMP001 42000000 2026-02-03
Sheet2: 거래처 정보
거래처ID 거래처명 산업 지역
C001 삼성전자 전자 서울
C002 SK하이닉스 반도체 이천
Sheet3: 직원 정보
직원ID 직원명 직급 부서
EMP001 김철수 과장 영업1
EMP002 이영희 대리 영업2
분기 보고서 만들기
목표: 거래 데이터에 거래처명, 담당자명, 직급을 추가해요.
=VLOOKUP(B2, '거래처정보'!A:B, 2, FALSE) → 거래처명
=VLOOKUP(C2, '직원정보'!A:B, 2, FALSE) → 담당자명
=VLOOKUP(C2, '직원정보'!A:C, 3, FALSE) → 직급
결과:
거래번호 거래처명 담당자명 직급 거래액
T001 삼성전자 김철수 과장 50000000
T002 SK하이닉스 이영희 대리 35000000
T003 삼성전자 김철수 과장 42000000
이제 한눈에 어떤 거래처인지, 누가 담당하는지 알 수 있어요!
실수하기 쉬운 부분
1. #N/A 오류
찾는 값이 없으면 이 오류가 나와요. 보통:
- 값의 형식이 다름 (숫자 vs 텍스트)
- 공백이 있음
- 오타가 있음
2. #REF! 오류
참조하는 범위가 잘못되었을 때 나와요. 시트 이름을 제대로 썼는지 확인하세요.
3. 느린 성능
너무 많은 데이터에서 VLOOKUP을 하면 엑셀이 느려져요. 그럴 땐 INDEX-MATCH를 쓰거나, 데이터를 나눠서 처리하세요.
팁
에러 처리하기
혹시 데이터가 없으면 오류 대신 특정 텍스트를 보이게 할 수 있어요:
=IFERROR(VLOOKUP(...), "데이터 없음")
연쇄 참조
VLOOKUP 결과를 다시 VLOOKUP의 입력으로 쓸 수도 있어요:
=VLOOKUP(VLOOKUP(A2, Sheet1!A:B, 2, FALSE), Sheet2!A:B, 2, FALSE)
복잡하지만 매우 강력해요.
다음으로
VLOOKUP과 INDEX-MATCH로 이제 여러 시트의 데이터를 자유자재로 조합할 수 있어요. 다음 강의에서는 이렇게 모인 데이터를 피벗 테이블로 분석하는 법을 배워봐요. 수천 건의 데이터를 순식간에 요약할 수 있거든요!
이해도 체크
먼저 위의 퀴즈를 완료하세요
레슨 완료!