몇 년 전 인사 자료를 정리하다가 한참을 헤맨 적이 있다. 사번을 기준으로 이름을 가져오는 건 문제가 없었는데 정작 필요한 부서명 열이 사번 열보다 왼쪽에 있었다. VLOOKUP으로 아무리 수식을 고쳐 써도 값이 안 나오길래 한참 뒤에야 이유를 알았다. VLOOKUP은 내가 찾는 값(예: 사번)이 검색 범위의 맨 왼쪽 열에 있어야만 작동한다. 원하는 데이터가 그보다 왼쪽 열에 있으면 VLOOKUP 입장에서는 애초에 “존재하지 않는” 데이터나 마찬가지다.
VLOOKUP의 근본적인 제약
VLOOKUP(찾을값, 범위, 열 번호, 정확도)의 구조를 보면 이유가 명확하다. ‘범위’의 첫 번째 열에서 찾을값을 검색하고 거기서 오른쪽으로 몇 번째 열인지를 ‘열 번호’로 지정해서 값을 가져오는 방식이다. 즉 검색 방향이 항상 왼쪽에서 오른쪽으로만 움직인다. 데이터가 그 순서대로 정리돼 있으면 문제없지만 실무에서 받는 엑셀 파일은 그런 순서를 지켜주지 않는 경우가 훨씬 많다.
실제로 손이 많이 가는 순간
내가 겪은 상황처럼, 가져오고 싶은 열이 검색 기준 열보다 왼쪽에 있으면 두 가지 중 하나를 해야 한다. 열 순서를 통째로 바꾸거나, 검색용 열을 통째로 복사해서 맨 왼쪽에 새로 만들어 붙이는 것이다. 데이터가 한두 번 쓰고 마는 표라면 큰 문제가 아닌데 매달 갱신되는 자료라면 이 작업을 매번 반복해야 한다. 원본 열 순서를 건드리는 것 자체도 찜찜하고 헬퍼 열을 추가하면 시트가 지저분해진다.
INDEX+MATCH로 바꾸면 달라지는 것
INDEX와 MATCH를 같이 쓰면 이 제약이 아예 사라진다. MATCH(찾을값, 검색범위, 0)는 찾을값이 검색범위에서 몇 번째 위치에 있는지만 알려준다. 열 순서와 무관하게 위치만 찾는다. 그리고 INDEX(반환범위, 위치)는 그 위치에 해당하는 값을 반환범위 어디서든 그대로 가져온다. 검색 기준 열과 가져올 열의 위치 관계가 아예 상관없어지는 셈이다.
| 구분 | VLOOKUP | INDEX+MATCH |
|---|---|---|
| 검색 방향 | 왼쪽 → 오른쪽만 가능 | 제한 없음 |
| 가져올 열이 검색 열보다 왼쪽일 때 | 열 순서 변경 또는 헬퍼 열 필요 | 수정 없이 바로 가능 |
| 열이 추가/삭제될 때 | 열 번호를 다시 세야 함 | 범위만 맞으면 영향 적음 |
| 수식 난이도 | 더 직관적 | 함수 두 개라 처음엔 낯설다 |
실제 예시로 보면
말로만 설명하면 감이 잘 안 오니 간단한 표로 보자. A열에 부서명, B열에 사번, C열에 이름이 있고, 사번을 알고 있을 때 부서명을 가져오고 싶은 상황이다.
| 1 | 부서명 (A) | 사번 (B) | 이름 (C) |
|---|---|---|---|
| 2 | 영업1팀 | 10021 | 김민준 |
| 3 | 개발팀 | 10022 | 이서연 |
| 4 | 인사팀 | 10023 | 박도윤 |
사번(10022)으로 부서명을 찾고 싶다고 해보자. VLOOKUP은 검색 범위를 B2:C4로 잡으면 그 안에 부서명 열 자체가 없어서 애초에 가져올 수 없다. 그렇다고 범위를 A2:C4로 잡으면, 이번엔 VLOOKUP이 첫 번째 열인 A열(부서명)에서 검색값 10022를 찾으려 하는데 10022는 A열이 아니라 B열에 있으니 찾지 못한다. 결국 이 구조 그대로는 VLOOKUP으로 해결이 안 된다.
INDEX+MATCH는 다음 한 줄로 끝난다.
=INDEX(A2:A4, MATCH(10022, B2:B4, 0))
MATCH(10022, B2:B4, 0)가 먼저 B열에서 10022의 위치(2번째)를 찾고, INDEX(A2:A4, 2)가 그 2번째 위치에 해당하는 A열 값, 즉 “개발팀”을 그대로 반환한다. 검색 기준 열(B)과 가져올 열(A)의 좌우 위치가 어떻게 놓여 있든 상관없다.
그럼 VLOOKUP으로는 정말 방법이 없을까
완전히 불가능한 건 아니다. 앞의 예시 표에서 VLOOKUP으로 굳이 해내려면, D열에 사번(B열)을 그대로 복사해서 붙여넣고 그 옆에 부서명(A열)까지 다시 옮겨 적어야 한다.
| 사번 (D, 복사본) | 부서명 (E, 복사본) |
|---|---|
| 10021 | 영업1팀 |
| 10022 | 개발팀 |
| 10023 | 인사팀 |
이렇게 만들어야 비로소 =VLOOKUP(10022, D2:E4, 2, 0)이 작동한다. 원본 표는 그대로 두고 옆에 복사본을 하나 더 만든 셈이니, 사번이 추가되거나 바뀔 때마다 이 복사본도 같이 손봐야 한다. 원본 하나로 끝나는 INDEX+MATCH와 달리 관리해야 할 표가 두 개로 늘어나는 것이다.
열이 하나 끼어들면 벌어지는 일
또 하나 실무에서 자주 겪는 사고가 있다. 위 예시에서 이름과 부서명 사이, 그러니까 B열과 C열 사이에 ‘입사일’ 열을 하나 새로 끼워 넣었다고 해보자. =VLOOKUP(10022, A2:D4, 3, 0)처럼 열 번호를 3으로 고정해둔 수식이 있었다면 열이 하나 밀리면서 그 3번째 자리는 이제 부서명이 아니라 입사일을 가리키게 된다. 수식은 에러 없이 조용히 엉뚱한 값을 돌려주기 때문에 눈치채지 못하고 그대로 보고서에 쓰는 경우도 봤다. =INDEX(A2:A4, MATCH(10022, D2:D4, 0))처럼 열 이름 기준으로 범위를 다시 잡아주는 습관을 들이면 열이 하나 끼어들어도 참조 범위만 맞춰 고치면 되니 이런 사고가 훨씬 줄어든다.
그래도 VLOOKUP이 편할 때
그렇다고 VLOOKUP을 완전히 버릴 필요는 없다. 검색 기준 열이 항상 맨 왼쪽에 있고, 데이터 구조가 잘 안 바뀌는 간단한 표라면 VLOOKUP이 더 짧고 읽기 편하다. 문제는 데이터를 내가 직접 설계하지 않고 남이 주는 형태 그대로 받아써야 하는 실무 상황이다. 그럴 때는 열 순서를 내 마음대로 바꿀 수 없으니 처음부터 INDEX+MATCH로 짜두는 게 나중에 손이 덜 간다.
정리
결국 선택 기준은 간단하다. 내가 데이터 구조를 통제할 수 있고 표가 단순하면 VLOOKUP으로 충분하다. 반대로 매번 다른 형태의 파일을 받아서 처리해야 하거나 열이 자주 추가되고 삭제되는 자료라면 INDEX+MATCH로 짜두는 편이 나중에 뜯어고칠 일을 줄여준다. 나는 그 인사 자료 사건 이후로는 검색 기준 열 위치를 확신할 수 없는 표는 아예 INDEX+MATCH로 먼저 짜고 시작한다.