엑셀 INDEX MATCH 함수 조합 사용법 및 VLOOKUP 한계 극복하기

얼마 전 기획팀의 한 대리님이 대용량 자재 관리 테이블을 보며 깊은 한숨을 쉬고 계시더라고요. 사유를 보니, 제품명을 기준으로 왼쪽에 있는 '부품 번호'를 찾아와야 하는데 VLOOKUP 함수가 자꾸 에러를 뿜어낸다는 것이었습니다. 아시다시피 VLOOKUP은 기준이 되는 값이 무조건 찾으려는 범위의 '첫 번째 열(가장 왼쪽)'에 있어야만 작동하잖아요? 

기준값보다 왼쪽에 있는 데이터는 죽었다 깨어나도 읽어오지 못하는 치명적인 약점이 있죠. 대리님은 결국 이 값을 찾으려고 수천 줄짜리 원본 데이터의 열 위치를 강제로 바꾸려다, 다른 셀에 걸려있던 수식들까지 줄줄이 깨져서 멘붕이 온 상태였습니다.

게다가 VLOOKUP은 데이터를 찾을 열의 위치를 '3', '4' 같은 고정된 숫자로 지정하다 보니, 중간에 새로운 열을 하나라도 삽입하면 순식간에 참조가 밀려 엉뚱한 값을 가져오는 대참사가 일어나기도 합니다. 

10년 동안 이런 상황 때문에 수많은 동료가 밤새워 데이터를 복구하는 모습을 봐왔기에, 저는 대리님의 마우스를 잠시 멈추게 하고 VLOOKUP의 모든 한계를 단번에 날려버릴 INDEX MATCH 조합을 처방해 주었습니다. 이 조합은 열의 위치에 구애받지 않고, 데이터가 왼쪽에 있든 오른쪽에 있든, 심지어 행과 열이 복잡하게 얽혀있든 상관없이 원하는 값을 정확하게 끄집어내는 만능 열쇠거든요.

처음에는 두 개의 함수가 합쳐져서 수식이 복잡해 보이지만, 원리만 이해하면 아주 직관적이에요. 역할을 분담하는 것이죠. MATCH 함수는 "내가 찾고 싶은 값이 이 눈금판에서 몇 번째 자리에 있어?" 하고 위치(번지수)를 알려주는 역할을 합니다. 그리고 INDEX 함수는 "그럼 내가 지정한 범위에서 방금 찾은 그 번지수에 있는 실제 값을 쏙 꺼내올게!" 하고 최종 결과물을 배달해 주죠. 쉽게 말해 MATCH가 주소를 찾고, INDEX가 그 주소에 있는 택배를 가져오는 찰떡궁합 구조랍니다.

실무 적용 방법도 차근차근 따라 해보면 정말 쉬워요. 만약 제품명으로 왼쪽에 있는 부품 번호를 찾고 싶다면, =INDEX(부품번호 열 범위, MATCH(내가 찾을 제품명, 전체 제품명 열 범위, 0)) 이렇게 수식을 짜주면 됩니다. MATCH 함수가 제품명의 정확한 위치(몇 번째 행인지)를 숫자로 반환해 주면, INDEX 함수가 부품 번호 열에서 정확히 그 행에 있는 값을 찾아오는 원리죠. 이렇게 수식을 짜두면 중간에 새로운 열을 아무리 추가하거나 삭제해도 수식이 절대 깨지지 않고 완벽하게 유지됩니다. 1초 만에 오류 없이 깔끔하게 채워지는 데이터를 보며 대리님도 십 년 묵은 체증이 내려간 표정을 지으셨답니다.

지금 VLOOKUP 함수를 쓰면서 "왜 왼쪽 값은 못 찾지?", "왜 열을 추가하면 에러가 나지?" 하며 답답해하고 계셨다면, 이번 기회에 INDEX MATCH 조합을 꼭 연습해 보세요. 처음에는 수식이 조금 길어 보여서 낯설 수 있지만, 딱 세 번만 직접 입력해 보면 손에 익어 VLOOKUP은 더 이상 쓰지 않게 될지도 모릅니다. 데이터 구조가 아무리 바뀌어도 흔들리지 않는 견고한 수식을 만드는 것, 그것이 바로 일 잘하는 전문가의 디테일이랍니다. 오늘 배운 치트키로 더 이상 엑셀 때문에 스트레스받지 마시고, 매끄럽고 여유로운 칼퇴길을 걸으시길 바랄게요!