🎬 프롤로그 : 회사에서 가장 조용한 배신자, VLOOKUP

엑셀을 어느 정도 다룬다는 사람이라면 VLOOKUP은 다 씁니다. 사원번호 넣으면 이름 나오고, 제품코드 넣으면 단가 나오는 그 마법. 처음 배울 땐 정말 짜릿하죠.

그런데 어느 월요일 아침, 팀장님이 이렇게 말합니다.
"거기 '부서' 열 앞에 '직급' 열 하나만 끼워 넣어줘."

열 하나 삽입. 저장. 그리고 보고서 전체가 엉뚱한 값으로 도배됩니다. 이름이 나와야 할 칸에 부서명이 들어가 있고, 단가 칸에는 재고 수량이 박혀 있습니다. 누구를 원망해야 할까요?

범인은 VLOOKUP의 세 번째 인자, 그 숫자 하나입니다. 열 번호를 사람이 손으로 세서 하드코딩하는 구조이기 때문입니다. 표가 움직이면 숫자는 그대로 남아 있고, 결과만 조용히 틀어집니다. 오류 메시지도 안 뜹니다. 그래서 더 무섭습니다.

1️⃣ INDEX : 좌표를 말하면 값을 꺼내오는 사서(司書)

INDEX를 제대로 이해하는 가장 좋은 비유는 도서관 사서입니다. 사서에게 "3번 서가, 5번째 칸"이라고 말하면 사서는 그 자리에 있는 책을 꺼내 줍니다. 책 제목을 몰라도 됩니다. 위치만 알면 값을 가져옵니다.

📌 구문

INDEX(array, row_num, [column_num])

array      : 값이 들어있는 범위 (1차원 또는 2차원)
row_num    : 몇 번째 행인가
column_num : 몇 번째 열인가 (1차원 범위면 생략 가능)

🧪 실습 데이터

=INDEX(B2:B6, 3)          → "박도현"   (B열 범위의 3번째)
=INDEX(A2:D6, 4, 3)       → "마케팅"   (4번째 행, 3번째 열)
=INDEX(D2:D6, 2)          → 5600

🎩 INDEX의 숨겨진 기능 3가지

① 행 전체 / 열 전체 반환
row_num을 0으로 하면 해당 열 전체를, column_num을 0으로 하면 행 전체를 배열로 반환합니다.

=SUM(INDEX(A2:D6, 0, 4))      → D열 전체 합계 (26,000)
=SUM(INDEX(A2:D6, 3, 0))      → 3행 전체(숫자만) 합계

② 참조를 반환한다 (값이 아니라!)
이게 INDEX의 진짜 무기입니다. INDEX는 값이 아니라 '셀 참조' 자체를 반환하기 때문에 범위 연산자 콜론(:)과 결합할 수 있습니다.

=SUM(D2:INDEX(D2:D100, 5))
→ D2부터 "D열 범위의 5번째 셀"까지 합계 = D2:D6 합계

=SUM(INDEX(D:D, 2):INDEX(D:D, 6))
→ 동적으로 시작과 끝을 계산하는 누적 합계 구조

OFFSET 함수로도 비슷한 걸 할 수 있지만, OFFSET은 휘발성(volatile) 함수라 시트의 어떤 셀만 고쳐도 전부 재계산됩니다. INDEX는 비휘발성입니다. 대용량 파일에서 체감 속도 차이가 큽니다.

③ 배열 상수도 받는다

=INDEX({"불합격","합격","우수"}, 2)   → "합격"

2️⃣ MATCH : 값을 던지면 위치를 알려주는 탐정

INDEX가 "좌표를 주면 값을 주는" 사서라면, MATCH는 정확히 반대입니다. 값을 주면 그 값이 몇 번째에 있는지를 알려주는 탐정입니다.

📌 구문

MATCH(lookup_value, lookup_array, [match_type])

match_type =  0 : 정확히 일치 (정렬 불필요) ★실무 95%
match_type =  1 : 이하 최대값 (오름차순 정렬 필요, 기본값)
match_type = -1 : 이상 최소값 (내림차순 정렬 필요)

=MATCH("E1003", A2:A6, 0)     → 3
=MATCH("연봉", A1:D1, 0)       → 4
=MATCH("개발", C2:C6, 0)       → 2   (첫 번째 일치 위치만 반환)

🔎 MATCH의 활용 포인트

와일드카드 지원 (match_type이 0일 때, 텍스트 대상)

=MATCH("김*", B2:B6, 0)       → 1  ('김'으로 시작하는 첫 항목
=MATCH("?서윤", B2:B6, 0)      → 2  (? 는 한 글자)

구간(등급) 판정 — match_type 1의 정석 용법입니다. IF를 7번 중첩하던 수식이 한 줄로 끝납니다.

기준표(오름차순) : 0 / 60 / 70 / 80 / 90
등급표           : F / D / C / B / A

=INDEX({"F","D","C","B","A"}, MATCH(점수, {0,60,70,80,90}, 1))
→ 87점 입력 시 → MATCH가 4 반환 → "B"

정렬 여부 체크 — MATCH로 중복 여부, 존재 여부를 논리값으로 뽑는 것도 실전에서 자주 씁니다.

=ISNUMBER(MATCH(A2, 기준목록, 0))   → TRUE면 목록에 존재
=IF(COUNTIF(기준목록,A2)=0,"신규","기존")   (COUNTIF 대안)

3️⃣ 합체 : INDEX(가져올 곳, MATCH(찾을 값, 찾을 곳, 0))

이제 두 함수를 붙입니다. 논리는 놀랍도록 단순합니다.

🧩 기본형

사원번호 E1004의 연봉을 찾아라

=INDEX(D2:D6, MATCH("E1004", A2:A6, 0))

풀어보면 :
  MATCH("E1004", A2:A6, 0)  →  4
  INDEX(D2:D6, 4)           →  5100

여기서 가장 중요한 문법적 사실이 하나 있습니다. INDEX의 array(가져올 열)와 MATCH의 lookup_array(찾을 열)는 서로 완전히 독립적인 범위입니다. 붙어 있을 필요도, 순서가 맞을 필요도 없습니다.

이 독립성이 VLOOKUP과의 결정적인 차이를 만듭니다.

4️⃣ VLOOKUP이 못 하는 것, INDEX+MATCH가 해내는 것

① 왼쪽 방향 조회 (Left Lookup)

VLOOKUP은 키 열이 범위의 맨 왼쪽이어야 하고, 결과는 그 오른쪽에서만 가져올 수 있습니다. 실무 데이터는 그렇게 착하게 생기지 않았습니다.

이름으로 사원번호를 찾고 싶다? VLOOKUP으로는 못 합니다. (억지로 하려면 CHOOSE나 IF로 배열을 뒤집는 괴상한 수식이 필요합니다.)

=INDEX(A2:A6, MATCH("최지우", B2:B6, 0))   →  "E1004"

끝입니다. INDEX+MATCH에게는 방향 개념이 아예 없습니다.

② 열 삽입·삭제에 강하다

VLOOKUP의 col_index_num은 고정 숫자입니다. 열을 하나 끼워 넣으면 수식은 아무 경고 없이 잘못된 열을 가리킵니다.

INDEX+MATCH는 열 자체를 참조합니다. D열을 참조하고 있었다면 열이 삽입되어 E열로 밀려도 엑셀이 참조를 자동 갱신합니다. 구조 변경에 대한 내구성이 근본적으로 다릅니다.

③ 성능 : 필요한 만큼만 읽는다

수만 행 × 수십 열 테이블에서 조회 수식이 수천 개 있는 파일이라면, 이 차이는 "파일 열 때 커피 한 잔"과 "즉시 계산"의 차이로 나타납니다.

④ MATCH 1회, INDEX 여러 개 — 재사용 구조

고수들의 시그니처 패턴입니다. 한 행에 이름·부서·연봉·입사일을 모두 가져와야 할 때, VLOOKUP은 4번 각각 검색합니다. INDEX+MATCH는 MATCH를 한 번만 계산해 보조 셀에 저장하고 INDEX만 4번 씁니다.

H2 (행 위치 캐시)  : =MATCH($G2, $A$2:$A$1000, 0)

I2 : =INDEX($B$2:$B$1000, $H2)   ' 이름
J2 : =INDEX($C$2:$C$1000, $H2)   ' 부서
K2 : =INDEX($D$2:$D$1000, $H2)   ' 연봉
L2 : =INDEX($E$2:$E$1000, $H2)   ' 입사일

→ 검색 연산 1회로 4개 값 확보. 검색이 전체 비용의 90%다.

5️⃣ 2차원 조회 : INDEX + MATCH + MATCH

행과 열을 동시에 찾는 교차 조회입니다. 단가표, 요율표, 운임표, 할인율 매트릭스 — 실무에서 이런 표는 끝없이 나옵니다.

🧪 예시 : 지역별 × 무게별 배송 요율표

범위가 A1:E4라고 할 때, "부산 / 5kg"의 요율을 찾는 수식은 이렇습니다.

=INDEX($A$1:$E$4,
       MATCH("부산", $A$1:$A$4, 0),
       MATCH("5kg",  $A$1:$E$1, 0))

→ MATCH 행 = 3, MATCH 열 = 4  →  INDEX(범위, 3, 4)  →  4,900

🎯 응용 : 사용자가 항목명을 고르는 동적 보고서

드롭다운(데이터 유효성 검사)으로 항목명을 선택하면, 해당 열의 값이 알아서 따라오는 대시보드를 만들 수 있습니다.

G1 : 드롭다운 (사원번호)
H1 : 드롭다운 (이름 / 부서 / 연봉 / 입사일)

=INDEX($A$1:$E$1000,
       MATCH($G$1, $A$1:$A$1000, 0),
       MATCH($H$1, $A$1:$E$1,    0))

VLOOKUP + MATCH 조합으로도 비슷하게 만들 수 있지만(VLOOKUP(값, 범위, MATCH(...), 0)), 여전히 "키가 첫 열"이라는 제약은 남습니다. INDEX+MATCH+MATCH는 그 제약이 없습니다.

6️⃣ 다중 조건 조회 : 실무의 진짜 난관

"사원번호"처럼 유일한 키가 있으면 인생이 편합니다. 하지만 현실은 "지점 + 월 + 제품" 세 개가 합쳐져야 겨우 한 행이 특정되는 경우가 태반입니다.

방법 A : 연결 키(Concatenation) — 가장 안전한 정석

보조 열을 만들어 조건들을 붙여버립니다.

F2 : =A2 & "|" & B2 & "|" & C2       ' 아래로 채우기

조회 :
=INDEX($D$2:$D$1000,
       MATCH(조건1 & "|" & 조건2 & "|" & 조건3, $F$2:$F$1000, 0))

방법 B : 곱셈 배열식 — 보조 열 없이 해결

논리값(TRUE=1, FALSE=0)을 곱해 조건을 모두 만족하는 행만 1로 남기는 기법입니다.

=INDEX($D$2:$D$1000,
       MATCH(1, ($A$2:$A$1000=조건1)
              * ($B$2:$B$1000=조건2)
              * ($C$2:$C$1000=조건3), 0))

구버전 엑셀(2019 이하)에서는 Ctrl+Shift+Enter로 배열 수식으로 확정해야 합니다. Microsoft 365 / 2021은 동적 배열 엔진 덕분에 그냥 Enter면 됩니다.

*는 AND, +는 OR로 동작합니다. 다만 +를 쓸 때는 결과가 2 이상이 될 수 있으니 MATCH(1, --(조건합>0), 0) 형태로 정규화하세요.

방법 C : MATCH의 배열 비교 축약형

=INDEX($D$2:$D$1000,
       MATCH(조건1 & "|" & 조건2,
             $A$2:$A$1000 & "|" & $B$2:$B$1000, 0))

보조 열 없이 즉석에서 연결 키를 만드는 방식입니다. 간결하지만 매 계산마다 전체 범위를 문자열로 이어붙이므로 성능 부담이 큽니다. 수백 행 수준에서는 편하고, 수만 행에서는 방법 A(보조 열)가 압도적으로 빠릅니다.

7️⃣ 오류 핸들링 : #N/A를 다루는 프로의 자세

조회 수식의 절반은 오류 처리입니다. 그런데 여기서 초보와 고수가 갈립니다.

❌ 흔한 실수 : IFERROR로 모든 걸 덮기

=IFERROR(INDEX(D:D, MATCH(G2, A:A, 0)), "")

깔끔해 보이지만 위험합니다. IFERROR는 모든 오류를 삼킵니다. 참조 오류(#REF!), 값 오류(#VALUE!), 심지어 내가 범위를 잘못 지정한 진짜 버그까지 조용히 공백으로 만들어 버립니다.

✅ 권장 : IFNA로 "못 찾음"만 처리

=IFNA(INDEX(D:D, MATCH(G2, A:A, 0)), "미등록")

IFNA(엑셀 2013+)는 #N/A만 처리합니다. "값을 못 찾음"은 정상적인 상황이고, 다른 오류는 그대로 드러나야 고칠 수 있습니다. 오류를 숨기는 것과 처리하는 것은 다릅니다.

🔬 #N/A가 뜰 때 점검 체크리스트

특히 마지막 항목은 알아두면 좋습니다. INDEX가 빈 셀을 가져오면 결과는 공백이 아니라 0입니다. 보고서에서 0이 깔려 보이는 이유가 대부분 이것입니다.

8️⃣ 표(Table)와 이름 정의로 수식을 '읽히게' 만들기

기술적으로 완벽한 수식보다 동료가 6개월 뒤에 읽을 수 있는 수식이 좋은 수식입니다. 여기서 엑셀 표(Ctrl+T)와 이름 정의(Ctrl+F3)가 등장합니다.

Before

=INDEX($D$2:$D$5000, MATCH($G2, $A$2:$A$5000, 0))

After (표 구조적 참조)

=INDEX(사원명부[연봉], MATCH([@사원번호], 사원명부[사원번호], 0))

차이가 보이시나요? 두 번째 수식은 주석이 필요 없습니다. 수식 자체가 설명입니다. 게다가 표는 데이터가 추가되면 범위가 자동 확장되므로 "어제까진 됐는데 오늘 새 데이터가 안 잡혀요" 사고가 사라집니다.

이름 정의로 의미 부여하기

키_사원번호  =  사원명부[사원번호]
값_연봉      =  사원명부[연봉]

=IFNA(INDEX(값_연봉, MATCH($G2, 키_사원번호, 0)), "미등록")

재능넷 '지식인의 숲'에 올라오는 엑셀 자동화 의뢰서를 보면, 실제로 작업 시간을 잡아먹는 건 어려운 함수가 아니라 남이 만든 해독 불가능한 수식을 역추적하는 시간입니다. 이름 정의는 그 시간을 절반으로 줄여줍니다.

9️⃣ 고급 응용 패턴 6선

① 마지막 값 찾기 (최신 데이터 추출)

같은 키가 여러 번 등록되어 있을 때, 가장 마지막 기록을 가져오는 클래식 트릭입니다.

=INDEX($D$2:$D$1000,
       MATCH(2, 1/($A$2:$A$1000=$G2)))

원리 : 조건 일치 → 1/1 = 1, 불일치 → 1/0 = #DIV/0!
       match_type 생략(=1)이므로 "2 이하 최대값"을 찾다가
       배열 끝까지 스캔 → 마지막 1의 위치를 반환

정렬이 안 되어 있어도 동작하는, 엑셀 함수 설계의 허점을 이용한 아름다운 해킹입니다. (365에서는 XLOOKUP(..., -1)이 더 직관적입니다.)

② 조건에 맞는 값 중 최댓값이 있는 행 찾기

부서가 "개발"인 사람 중 최고 연봉자 이름

=INDEX($B$2:$B$1000,
       MATCH(MAX(IF($C$2:$C$1000="개발", $D$2:$D$1000)),
             IF($C$2:$C$1000="개발", $D$2:$D$1000), 0))
             
(구버전은 Ctrl+Shift+Enter)

③ 근사치 조회 — 구간별 수수료 계산

거래금액 기준표(오름차순) : 0 / 100 / 500 / 1000 / 5000
수수료율               : 3% / 2.5% / 2% / 1.5% / 1%

=INDEX($B$2:$B$6, MATCH($G2, $A$2:$A$6, 1))

match_type 1을 쓰는 정당한 케이스입니다. 기준표가 오름차순 정렬이라는 전제를 반드시 문서화해 두세요.

④ 여러 시트에서 찾기 (INDIRECT 조합)

=INDEX(INDIRECT("'" & $F2 & "'!D:D"),
       MATCH($G2, INDIRECT("'" & $F2 & "'!A:A"), 0))

F열에 시트명을 두면 시트를 넘나드는 조회가 됩니다. 단 INDIRECT는 휘발성 함수이고 닫힌 파일을 참조할 수 없습니다. 대량 사용은 금물입니다.

⑤ 동적 범위 합계 (INDEX의 참조 반환 활용)

1월부터 선택한 월까지 누적 합계

=SUM(INDEX($B$2:$M$2, 1) : INDEX($B$2:$M$2, MATCH($G$1, $B$1:$M$1, 0)))

OFFSET 없이, 휘발성 없이 동적 구간 합계를 구현하는 프로 패턴입니다.

⑥ n번째 일치 항목 찾기

=INDEX($B$2:$B$1000,
       SMALL(IF($C$2:$C$1000=$G$1, ROW($C$2:$C$1000)-ROW($C$2)+1), n))

MATCH는 첫 번째 일치만 반환하므로, 두 번째·세 번째를 원하면 SMALL+IF로 위치 배열을 만들어야 합니다. 365라면 FILTER가 훨씬 간결합니다.

🔟 XLOOKUP 시대, 그래도 INDEX+MATCH를 배워야 하는 이유

Microsoft 365와 Excel 2021에는 XLOOKUP이 있습니다. 솔직히 말하면, 새 파일을 만들 수 있는 상황이라면 XLOOKUP이 더 좋습니다.

=XLOOKUP($G2, $A$2:$A$1000, $D$2:$D$1000, "미등록")

• 왼쪽 조회 가능
• 기본이 정확히 일치
• 없을 때 반환값 내장 (IFNA 불필요)
• 역방향 검색, 와일드카드, 이진 검색 모드 지원
• 배열 반환으로 여러 열 동시 조회 가능

그런데 왜 INDEX+MATCH인가

🧰 성능 최적화 실전 수칙

수식이 맞게 작동하는 것과 빠르게 작동하는 것은 다른 문제입니다. 조회 수식이 만 개쯤 쌓이면 이 차이가 업무 시간을 결정합니다.

① 전체 열 참조(A:A)를 습관적으로 쓰지 말기

최신 엑셀은 "사용된 범위"만 계산하는 최적화가 있지만, 배열 연산이 섞이면 104만 행 전체를 실제로 계산하는 경우가 생깁니다. 특히 방법 B의 곱셈 배열식에 A:A를 쓰면 치명적입니다. 표(Table) 또는 명시적 범위를 쓰세요.

② MATCH 결과를 캐시하기

앞서 본 패턴입니다. 같은 행에서 여러 값을 가져올 때 MATCH는 단 한 번만. 검색 연산이 전체 비용의 대부분을 차지합니다.

③ 대용량이면 정렬 + 이진 검색 고려

정확 일치(0) : 선형 검색 — O(n), 첫 행부터 순차 스캔
근사 일치(1) : 이진 검색 — O(log n), 정렬 필수, 압도적으로 빠름

10만 행에서 : 선형 평균 5만 회 비교 vs 이진 약 17회 비교

데이터를 정렬해두고 match_type 1을 쓰면 극적으로 빨라집니다. 단 없는 값이 들어오면 엉뚱한 근사값을 주므로 이중 검증(2단 MATCH) 패턴이 필요합니다.

=IF(INDEX($A$2:$A$99999, MATCH($G2,$A$2:$A$99999,1)) = $G2,
    INDEX($D$2:$D$99999, MATCH($G2,$A$2:$A$99999,1)),
    "미등록")

④ 수식 대신 도구를 쓸 타이밍을 알기

수십만 행 × 여러 테이블 결합이라면 이제 수식의 영역이 아닙니다. Power Query(가져오기 및 변환)의 병합 쿼리, 또는 Power Pivot의 RELATED가 정답입니다. 진짜 고수는 "함수로 다 되는데요"라고 우기지 않고, 도구를 바꿀 시점을 압니다.

📋 체크리스트 : 이 수식, 배포해도 되는가

특히 "키 열에 중복이 없는가"는 반드시 확인하세요. MATCH는 첫 번째 일치만 반환하기 때문에, 중복이 있으면 수식은 오류 없이 절반만 맞는 보고서를 만들어 냅니다. 이런 오류는 몇 달 뒤에 발견되고, 그때는 수습이 불가능합니다.

중복 검증 :
=IF(COUNTIF($A$2:$A$1000, $A2)>1, "중복!", "")

또는 조건부 서식 → 중복 값 강조

🎓 에필로그 : 함수를 외우는 사람 vs 구조를 보는 사람

INDEX와 MATCH는 각각 따로 보면 아주 단순한 함수입니다. 하나는 좌표를 받아 값을 주고, 하나는 값을 받아 좌표를 줍니다. 그런데 이 둘을 합치는 순간, "찾기"라는 작업의 본질이 드러납니다.

데이터베이스의 JOIN, 프로그래밍의 배열 인덱싱, 파이썬 pandas의 .loc[] — 전부 같은 원리입니다. "위치를 특정하고, 그 위치의 값을 꺼낸다." 엑셀에서 이 사고를 익힌 사람은 다른 도구로 옮겨가도 헤매지 않습니다.

반대로 VLOOKUP만 외운 사람은, 열이 하나 삽입되는 순간 무너집니다. 도구를 외웠을 뿐 구조를 이해하지 않았기 때문입니다.

엑셀 실력은 함수 개수가 아니라 깨지지 않는 구조를 설계하는 능력으로 증명됩니다. 그 첫 관문이 바로 INDEX와 MATCH입니다.

더 깊은 엑셀 자동화, 수식 리팩터링, Power Query 데이터 모델링 노하우는 재능넷의 '지식인의 숲'에서 계속 다룹니다. 여러분의 스프레드시트가 어제보다 조금 더 튼튼해지기를. 🧱