안녕하세요, 데이터매니저입니다. 엑셀을 조금 다룰 줄 안다는 직장인들에게 가장 자신 있는 함수를 물어보면 십중팔구 'VLOOKUP'을 외칩니다. 1편에서 다루었듯 VLOOKUP은 데이터 매칭의 강력한 무기지만, 실무를 하다 보면 이 무기가 전혀 통하지 않는 끔찍한 상황을 마주하게 됩니다.
예를 들어, 다른 부서에서 받은 파일에 직원의 '사번'을 기준으로 '이름'을 찾아와야 합니다. 그런데 맙소사, 사번은 C열(오른쪽)에 있고 이름은 A열(왼쪽)에 적혀 있습니다. VLOOKUP을 써보려 하니 에러만 뜹니다. 다급해진 마음에 C열의 사번을 복사해서 A열 왼쪽으로 강제로 끼워 넣고 표를 다 뜯어고칩니다. 하지만 이렇게 원본 데이터를 훼손하는 순간, 뒤에 연결된 피벗 테이블과 다른 수식들이 전부 깨지며 대참사가 일어납니다.
오늘은 표를 절대 건드리지 않고, VLOOKUP의 치명적인 단점인 '오른쪽만 검색 가능'이라는 한계를 완벽하게 박살 내버리는 엑셀 실무자들의 궁극기, 'INDEX와 MATCH 함수 조합'의 완벽한 원리와 실전 비법을 명쾌하게 해부해 드립니다.
VLOOKUP은 왜 왼쪽을 보지 못할까? (치명적 한계)
VLOOKUP 함수가 왼쪽 데이터를 가져오지 못하는 이유는 함수의 태생적인 문법 구조 때문입니다. VLOOKUP의 규칙은 아주 완고합니다. "네가 찾으려는 기준 값은 무조건, 예외 없이 지정한 표 범위의 '가장 첫 번째(왼쪽 끝) 열'에 있어야 한다"는 것입니다.
사번이 C열에 있고 이름이 A열에 있을 때, VLOOKUP으로 사번을 찾으려면 검색 범위를 C열부터 시작해야 합니다. 그러면 엑셀은 C열을 1번 기둥으로 인식하고 그 오른쪽(D, E, F열)으로만 시선을 돌립니다. 자기 뒤통수에 있는 A열(왼쪽)은 쳐다볼 기능 자체가 애초에 없는 것이죠.
이 때문에 초보자들은 억지로 원본 표의 열 순서를 바꾸는 위험한 짓을 합니다. 하지만 실무에서는 사내 시스템에서 매일 자동으로 내려받는 로우(Raw) 데이터의 열 순서를 마음대로 섞어버리면, 매일 아침 열을 다시 재배치해야 하는 지옥의 노가다가 시작됩니다.
분해해서 이해하기 1: 좌표를 찾아주는 'MATCH' 함수
이 한계를 부수려면 VLOOKUP을 버리고 두 개의 함수를 합체시켜야 합니다. 먼저 첫 번째 무기인 MATCH 함수의 역할을 이해해 봅시다.
MATCH 함수는 데이터를 가져오는 녀석이 아닙니다. 오직 "네가 찾는 그 단어가, 이 줄의 몇 번째 칸에 있어!"라고 위치(좌표)만 알려주는 네비게이션 역할만 합니다.
문법:
=MATCH(찾는값, 찾는값이_있는_범위, 0)
예를 들어 =MATCH("홍길동", A1:A100, 0)이라고 입력하면, 엑셀은 A열을 훑어보고 홍길동이 5번째 줄에 있다면 결과값으로 그냥 숫자 '5'를 뱉어냅니다. (맨 뒤의 0은 정확히 일치하는 값을 찾으라는 옵션입니다.)
분해해서 이해하기 2: 지정한 버튼을 누르면 뽑아주는 'INDEX' 함수
두 번째 무기인 INDEX 함수는 자판기 같은 역할을 합니다. 범위를 지정해주고 "여기서 5번째 버튼을 눌러줘!"라고 명령하면 그 자리에 있는 데이터를 쏙 뽑아옵니다.
문법:
=INDEX(정답을_가져올_범위, 행_번호)
예를 들어 =INDEX(B1:B100, 5)라고 입력하면, 엑셀은 B열의 5번째 칸에 있는 데이터를 무조건 가져옵니다.
합체! VLOOKUP을 졸업하는 'INDEX + MATCH' 공식
이제 이 두 개를 합체해 보겠습니다. INDEX 함수의 '행 번호'를 적어야 할 자리에, 위치를 찾아주는 MATCH 함수를 통째로 집어넣는 것입니다. 이것만 외우면 여러분은 엑셀의 마스터 클래스에 오르게 됩니다.
궁극의 공식:
=INDEX(가져올_정답_열, MATCH(찾는값, 찾는값이_있는_열, 0))
실무 상황에 적용해 볼까요? 나는 F2 셀에 적힌 '사번'을 가지고, 원본 표의 C열(사번)에서 똑같은 걸 찾은 다음, A열(이름)에 있는 데이터를 가져오고 싶습니다.
실전 수식:
=INDEX(A:A, MATCH(F2, C:C, 0))
해석해 보면 이렇습니다. "먼저 MATCH가 C열에서 F2(사번)랑 똑같은 게 몇 번째 줄에 있는지 찾아줘. 만약 15번째 줄에 있다면, INDEX가 A열의 15번째 줄에 있는 데이터를 가져와 줘!" 정답이 왼쪽에 있든, 오른쪽에 있든, 심지어 다른 시트의 구석에 처박혀 있든 아무 상관이 없습니다. '찾는 열'과 '가져올 열'을 각각 따로따로 잡아주기 때문에 방향의 제약이 0%가 되는 완벽한 자유를 얻게 됩니다.
구버전 호환성과 XLOOKUP의 등장 (주의사항)
"어? 요즘은 엑셀에 XLOOKUP 함수가 나와서 왼쪽도 쉽게 찾을 수 있던데요?"라고 질문하시는 스마트한 실무자분들이 계실 겁니다. 맞습니다. 최근 업데이트된 XLOOKUP 함수는 =XLOOKUP(찾는값, 찾는열, 가져올열)이라는 쉬운 문법으로 왼쪽 데이터를 단번에 찾아옵니다.
하지만 데이터매니저가 여전히 실무에서 INDEX+MATCH를 가장 중요하게 강조하는 치명적인 이유가 있습니다. 바로 '버전 호환성'입니다.
XLOOKUP은 엑셀 2021 버전이나 Microsoft 365(최신 버전), 그리고 구글 스프레드시트에서만 작동합니다. 만약 여러분이 XLOOKUP으로 멋지게 문서를 만들어서 거래처나 타 부서 차장님께 메일로 보냈는데, 그 회사의 컴퓨터에 설치된 엑셀이 구형(2016, 2019 버전 등)이라면? 그들이 파일을 여는 순간 모든 데이터가 #NAME? 오류로 도배되며 시트가 붕괴됩니다.
대한민국의 수많은 관공서와 보수적인 기업들은 여전히 구버전 엑셀을 널리 사용하고 있습니다. 버전에 상관없이 1990년대 엑셀부터 최신 버전까지 100% 완벽하게 호환되며 왼쪽 데이터를 찾아오는 유일한 무기는 오직 INDEX+MATCH 조합뿐입니다. 이 조합을 손에 익혀두는 것은 어떤 척박한 업무 환경에서도 살아남는 강력한 방패가 됩니다.
오늘 글의 핵심 요약
VLOOKUP은 찾는 기준값이 반드시 범위의 가장 왼쪽에 있어야 하므로, 왼쪽에 있는 정답 데이터를 가져오지 못하는 치명적 한계가 있습니다.
VLOOKUP을 쓰겠다고 원본 로우(Raw) 데이터의 열 순서를 복사해서 바꾸면 데이터베이스 구조가 망가지고 2차 오류가 발생합니다.
=INDEX(가져올 열, MATCH(찾는값, 찾는열, 0))공식을 쓰면 데이터의 위치가 왼쪽이든 오른쪽이든 상관없이 자유롭게 매칭하여 가져옵니다.최신 XLOOKUP 함수가 있지만, 타 회사나 구버전 엑셀 환경과의 완벽한 호환성을 보장하려면 INDEX+MATCH 조합이 여전히 실무의 정답입니다.
다음 8편에서는 보고서를 다이나믹하게 만들어주는 피벗 테이블(Pivot Table)을 돌릴 때, 원본 데이터를 수정하고 추가했는데도 피벗이 업데이트가 안 되어 엉터리 보고서를 만들게 되는 이유, 바로 ‘피벗 테이블(Pivot Table) 새로고침이 안 되거나 원본 데이터 추가 시 누락되는 이유’에 대해 알아보겠습니다. 새로고침을 해도 데이터가 안 들어오는 황당한 경험을 해결하는 원본 범위 자동 확장 비법을 다음 시간에 공개합니다!
여러분은 VLOOKUP을 쓰려고 원본 파일의 열 순서를 이리저리 옮기다가 엑셀이 꼬여서 고생했던 적이 있으신가요? 오늘 배운 INDEX+MATCH 조합을 당장 시트에서 테스트해 보시고 성공의 쾌감을 댓글로 남겨주세요!
0 댓글