VLOOKUP의 한계를 넘다: INDEX와 MATCH 함수의 환상 조합

지난 4편에서 실무의 꽃이라 불리는 VLOOKUP 함수를 마스터하셨습니다. 이제 당당하게 엑셀을 켜고 VLOOKUP을 쓰다 보면, 곧바로 커다란 벽에 부딪히는 순간이 찾아옵니다.

"선배님, 찾으려는 사번이 이름보다 오른쪽에 있는데 VLOOKUP이 안 먹혀요!" "본부장님 지시로 표 중간에 '직급' 열을 하나 추가했더니, VLOOKUP 수식이 다 깨져버렸어요!"

VLOOKUP에게는 아주 치명적인 두 가지 단점이 있습니다. 첫째, 무조건 기준값의 '오른쪽'으로만 검색이 가능합니다. 둘째, 가져올 데이터를 '숫자(3번째 열, 4번째 열 등)'로 고정해 두기 때문에 중간에 셀이나 열이 하나라도 추가되면 엉뚱한 값을 가져오게 됩니다. 저 역시 과거에 VLOOKUP만 믿고 수천 줄짜리 취합 파일을 만들었다가, 상사의 변덕으로 원본 표 양식이 살짝 바뀌는 바람에 수백 개의 에러를 밤새워 수정한 뼈아픈 기억이 있습니다.

이런 VLOOKUP의 멱살을 잡고 하드캐리 해주는 완벽한 대체재가 바로 'INDEX와 MATCH' 함수의 조합입니다. 처음엔 영어 단어가 두 개나 들어가서 겁먹기 쉽지만, 원리만 알면 왼쪽, 오른쪽 가리지 않고 데이터를 쏙쏙 뽑아내는 진짜 엑셀 마스터로 거듭날 수 있습니다.

1. MATCH 함수: 데이터의 '위치(좌표)'를 찾아주는 내비게이션

두 함수를 결합하기 전에, 먼저 MATCH 함수의 역할을 이해해야 합니다. MATCH는 아주 단순합니다. "내가 찾는 값이 위에서부터 몇 번째 줄에 있어?"라는 질문에 순서(숫자)로 답해주는 내비게이션입니다.

예를 들어, 100명의 직원 명단에서 '홍길동'이 위에서부터 몇 번째에 있는지 찾고 싶을 때 사용합니다.

  • 공식: =MATCH(찾을 값, 찾을 범위, 0)

VLOOKUP과 아주 비슷하죠? 끝에 0을 붙이는 것(정확히 일치하는 값 찾기)도 똑같습니다. 홍길동이 명단의 5번째에 있다면, MATCH 함수는 '5'라는 숫자를 뱉어냅니다. 이 '5'라는 숫자가 바로 보물의 위치를 알려주는 핵심 열쇠가 됩니다.

2. INDEX 함수: 원하는 좌표의 데이터를 꺼내주는 '자판기'

그렇다면 INDEX 함수는 무엇일까요? INDEX는 지정된 범위에서 우리가 입력한 '행(세로줄)'과 '열(가로칸)'이 교차하는 지점의 값을 꺼내주는 자판기입니다.

  • 공식: =INDEX(가져올 데이터 전체 범위, 행 번호, 열 번호)

"사원 명부 전체에서, 5번째 줄, 2번째 칸에 있는 값을 가져와!"라고 명령하면, 정확히 그 자리에 있는 전화번호나 이메일을 툭 떨어뜨려 주는 방식입니다.

3. 크로스! INDEX와 MATCH의 환상 조합 공식

이제 이 둘을 합체할 시간입니다. INDEX 함수의 '행 번호'를 적는 자리에, 직접 숫자를 적는 대신 아까 배운 MATCH 함수를 통째로 쏙 집어넣는 것입니다.

  • 완성된 실무 공식: =INDEX(가져올 데이터 범위, MATCH(찾을 값, 찾을 범위, 0))

해석하자면 이렇습니다. "MATCH야, 홍길동이 몇 번째 줄에 있는지 찾아봐! (결과: 5번째 줄이요!) 그럼 INDEX야, 네가 그 5번째 줄에 있는 전화번호를 가져와!" 이 조합의 핵심은 내가 가져올 데이터 범위(전화번호 열)와 찾을 범위(이름 열)를 각각 '1개의 세로줄'로만 따로따로 지정한다는 것입니다.

4. VLOOKUP을 버리고 INDEX/MATCH를 써야 하는 진짜 이유 3가지

처음엔 VLOOKUP 하나 쓰는 것보다 타자 칠 것이 많아 귀찮아 보일 수 있습니다. 하지만 실무자들이 결국 이 조합으로 넘어오게 되는 강력한 이유가 있습니다.

  1. 역방향(왼쪽) 검색이 가능하다 VLOOKUP은 기준값이 무조건 표의 맨 왼쪽에 있어야 하지만, INDEX/MATCH는 기준값이 표의 맨 오른쪽 구석에 처박혀 있어도 그보다 왼쪽에 있는 데이터를 자유자재로 불러올 수 있습니다. 엑셀을 계산하기 위해 억지로 원본 데이터의 열 위치를 뒤바꿀 필요가 없습니다.

  2. 열을 삽입하거나 삭제해도 수식이 절대 깨지지 않는다 VLOOKUP은 '3번째 열'이라고 숫자를 박아두기 때문에 표 중간에 새로운 열이 삽입되면 엉망이 됩니다. 하지만 INDEX/MATCH는 가져올 열 전체를 통째로 범위 지정하기 때문에 표가 어떻게 늘어나든 쪼그라들든, 데이터를 끝까지 추적해서 정확하게 가져옵니다.

  3. 무거운 엑셀 파일이 가벼워지고 빨라진다 수십만 줄의 빅데이터를 다룰 때 VLOOKUP은 표 전체 영역을 스캔하느라 컴퓨터를 멈추게 만들기도 합니다. 반면 INDEX/MATCH는 딱 필요한 두 개의 열(이름 열, 전화번호 열)만 스캔하므로 처리 속도가 훨씬 빠르고 파일 용량 부담도 줄어듭니다.

복잡한 수식에 거부감이 들더라도, 빈 엑셀 창을 띄워놓고 딱 3번만 연습해 보세요. VLOOKUP의 굴레에서 벗어나는 순간, 여러분의 엑셀 자유도는 200% 상승할 것입니다.

  • 핵심 요약

  1. VLOOKUP은 오른쪽 방향으로만 검색할 수 있고 표 중간에 열이 추가되면 수식이 깨지는 치명적인 단점이 있습니다.

  2. MATCH 함수로 데이터의 위치(몇 번째 줄인지)를 찾고, INDEX 함수로 그 위치의 실제 값을 불러오는 조합을 통해 이 한계를 돌파할 수 있습니다.

  3. INDEX/MATCH 조합은 왼쪽 역방향 검색이 가능하며, 원본 표가 변형되어도 수식이 깨지지 않고 데이터 처리 속도도 훨씬 빠릅니다.

  • 다음 편 예고 복잡한 함수 산을 두 개나 넘으셨으니 이제 데이터 가공이 한결 수월해지셨을 겁니다. 다음 6편에서는 복잡한 실적표에서 내가 원하는 부서, 특정 조건의 실적만 쏙쏙 골라내어 합계를 내주는 '조건부 계산의 핵심: SUMIF와 COUNTIF'에 대해 알아보겠습니다.

  • 독자님을 위한 질문 과거에 VLOOKUP을 열심히 걸어두었는데, 누군가 원본 표 중간에 셀이나 열을 추가해 버려서 수식이 다 깨져버린 끔찍한 경험, 혹시 있으신가요?

댓글

댓글 쓰기

이 블로그의 인기 게시물

식재료 보관의 신: 대파부터 고기까지, 오래 보관하는 소분법

지난 2편에서 우리는 마트의 유혹을 뿌리치고 꼭 필요한 만큼만 장을 보는 데 성공했습니다. 자, 이제 양손 무겁게 집에 돌아왔습니다. 여러분은 지금 사 온 식재료를 어떻게 하시나요? 아마 열에 아홉은 장바구니에 있던 비닐봉지 그대로 냉장고 빈칸에 쑤셔 넣고 피곤한 몸을 뉘이실 겁니다. 하지만 이 사소한 행동이 식비 낭비의 가장 큰 주범이라는 사실을 아시나요? 장을 보고 온 직후의 30분, 귀찮음을 이겨내는 이 짧은 시간이 식재료의 수명을 3일에서 3주로 늘려주는 골든타임입니다. 오늘은 1인 가구가 가장 자주 버리게 되는 대표 식재료들의 수명을 극적으로 늘려주는 실전 소분법을 공유합니다. 1. 마트 포장 용기는 세균의 온상입니다 가장 먼저 해야 할 일은 마트에서 담아준 포장재를 모두 벗겨내는 것입니다. 특히 고기가 담겨 있는 검은색 스티로폼 랩 포장은 겉보기엔 깔끔해 보이지만, 유통 과정에서 수많은 사람의 손을 거치며 세균에 노출되었을 확률이 높습니다. 게다가 공기가 드나들기 쉬워 식재료의 수분을 빼앗고 산화를 촉진합니다. 집에 도착하자마자 모든 식재료는 비닐과 스티로폼에서 탈출시켜야 합니다. 이때 필요한 무기는 딱 두 가지입니다. 공기를 차단해 줄 '지퍼백(또는 밀폐용기)'과 수분을 조절해 줄 '키친타월'입니다. 2. 1인 가구의 웬수, 대파와 마늘 완벽 보관법 한국인 밥상에 빠지지 않지만 1인 가구가 다 먹기엔 항상 양이 많은 대파와 마늘. 이 녀석들의 최대 적은 바로 '수분'입니다. 대파: 흙을 깨끗이 씻어낸 후, 물기가 완벽하게 마를 때까지 건조하는 것이 핵심입니다. (물기가 남으면 금방 짓무릅니다.) 그 후 용도에 맞게 송송 썰거나 길게 썰어 밀폐용기에 담습니다. 이때 바닥과 뚜껑 쪽에 키친타월을 한 장씩 깔아두면 타월이 습기를 흡수해 한 달 가까이 싱싱하게 보관할 수 있습니다. 오래 두고 먹을 절반은 지퍼백에 넓게 펴서 냉동실로 보냅니다. 깐 마늘: 밀폐용기 바닥에 설탕을 1cm 두께로 깔고 키친타월을 덮은 ...
자세한 내용 보기

장보기의 기술: 버려지는 식재료를 줄이는 스마트한 소량 구매 전략

1편에서 우리 집 냉장고를 비우고 식습관을 진단해보셨나요? 아마 유통기한이 지나 쓰레기통으로 향하는 식재료들을 보며 적지 않은 충격을 받으셨을 겁니다. 식비 절약의 핵심은 단순히 싼 재료를 사는 것이 아니라, '내가 산 식재료를 100% 소비하는 것'에 있습니다. 많은 1인 가구가 장보기 단계에서부터 실패를 경험합니다. 마트의 할인 유혹에 넘어가 '1+1' 상품을 집어 들거나, 무심코 큰 용량의 재료를 사서 결국 반 이상 버리게 되는 것이죠. 오늘은 1인 가구의 장보기 실패를 원천 차단하는 스마트한 소량 구매 전략을 정리해 드립니다. 1. '1+1'은 1인 가구에게 가장 비싼 상품입니다 마트에 가면 '2개 사면 30% 할인'이나 '1+1' 같은 문구가 우리를 유혹합니다. 1인 가구의 가장 큰 실수는 이 할인율에 속아 필요 이상의 양을 구매하는 것입니다. 생각해보세요. 대파 한 단을 할인받아 2,000원에 샀다고 칩시다. 하지만 혼자 사는 사람이 일주일에 소비할 수 있는 대파의 양은 정해져 있습니다. 다 못 먹고 절반을 버린다면, 결과적으로 나는 2,000원을 주고 1,000원어치 대파만 먹은 셈입니다. 할인받은 금액보다 버리는 식재료의 값이 더 크다면, 그것은 절대 경제적인 장보기가 아닙니다. 1인 가구에게 진정한 할인은 '내가 기한 내에 다 먹을 수 있는 적정량'을 사는 것입니다. 2. 장보기 전 '3-Day 리스트' 만들기 매주 한 번씩 몰아서 장을 보는 것이 효율적이라고 생각하시나요? 하지만 경험상 1인 가구에게 일주일 치 식단은 너무 깁니다. 갑작스러운 야근, 친구와의 약속, 배달 음식의 유혹이 변수로 작용하기 때문입니다. 이제부터는 '일주일'이 아닌 '3일 치' 리스트를 작성해보세요. 3일은 내 냉장고의 식재료가 신선하게 유지되는 기간이자, 내 식사 계획을 현실적으로 통제할 수 있는 최적의 시간 단위입니다. 3일 뒤에 무엇을...
자세한 내용 보기

계좌만 파면 끝? 방치된 IRP/ISA에 생기를 불어넣을 실전 상품 매수 가이드

연말정산 혜택을 위해, 혹은 비과세라는 말에 이끌려 큰맘 먹고 IRP와 ISA 계좌를 개설하셨나요? 매월 꼬박꼬박 자동이체까지 걸어두었다면 노후 준비의 절반은 해내신 셈입니다. 하지만 여기서 안심하기엔 이릅니다. 과거의 제가 그랬듯, 많은 직장인들이 계좌에 돈을 입금한 것만으로 모든 투자가 끝났다고 착각합니다. 1년 뒤 수익률을 확인해 보려 앱을 열었을 때, 수익률이 0%대 머물러 있어 당황했던 기억이 생생합니다. 그 이유는 제가 입금한 돈이 어떤 금융 상품도 사지 않은 채 '현금성 대기 자금(예수금)' 상태로 덩그러니 방치되어 있었기 때문입니다. ISA와 IRP는 돈을 불려주는 요술 램프가 아니라, 금융 상품을 담는 '빈 장바구니'일 뿐입니다. 오늘은 빈 장바구니에 어떤 물건을 담아야 할지 막막한 4050 세대를 위해, 복잡한 주식 공부 없이도 안전하게 계좌에 생기를 불어넣는 실전 상품 매수 가이드를 정리해 드립니다. 1. 개별 주식의 유혹을 버려라 "삼성전자 사면 되나요?", "요즘 테슬라가 좋다던데 ISA로 살 수 있나요?" 계좌를 막 개설한 분들이 가장 많이 하는 질문입니다. 결론부터 말씀드리면, 우리의 피 같은 노후 자금은 개별 기업의 흥망성쇠에 운명을 맡겨서는 안 됩니다. 특히 4050 세대는 투자 실패 시 이를 만회할 시간적 여유가 부족합니다. 한 기업의 악재로 자산이 반토막 나는 리스크를 피하려면, 수십에서 수백 개의 우량 기업을 하나의 바구니에 담아 파는 상품을 고르는 것이 정답입니다. 고민할 것 없이 아래 두 가지 선택지만 기억하시면 됩니다. 2. 귀찮은 게 싫다면: TDF (Target Date Fund) 투자 공부할 시간도 없고, 매번 앱을 들여다보며 스트레스받기 싫은 분들에게 최적화된 상품이 바로 TDF입니다. TDF는 내 은퇴 예상 연도(Target Date)에 맞춰 전문가가 알아서 주식과 채권의 비중을 조절해 주는 '자율주행 펀드'입니다. 상품명 뒤에 ...
자세한 내용 보기