회사에서 스프레드시트를 다룰 때 가장 머리가 지끈거리는 순간은 단연 '수식'을 짜야 할 때입니다. 서점에 가거나 유튜브 강의를 찾아보면 외워야 할 함수 종류만 수백 개에 달해 시작하기도 전에 기가 죽곤 합니다. "이 많은 걸 다 외워야 야근을 면할 수 있는 걸까?" 하는 걱정이 앞서기 마련이죠.
하지만 제가 수년간 실무에서 수많은 데이터 대시보드를 짜고 자동화를 거치며 내린 결론은 다릅니다. 직장인 실무 데이터 처리의 90%는 단 5가지 함수만 제대로 조합할 줄 알아도 전부 해결됩니다. 수백 개의 함수를 억지로 외우는 것은 뇌 용량 낭비에 가깝습니다.
오늘은 퇴근 직전 거래처 명단과 매출 표를 대조하느라 모니터에 눈을 대고 일일이 찾던 어제의 수고를 단 5분 만에 해결해 줄 '실무 치트키 5대 핵심 함수 공식(VLOOKUP, INDEX, MATCH, FILTER, IFERROR)'을 가장 직관적으로 풀어드립니다.
## 1. 데이터 매칭의 제왕: VLOOKUP과 IFERROR의 환상적인 콜라보
실무에서 가장 많이 하는 작업은 단연 '기준 데이터에 맞는 정보 찾아오기'입니다. 예컨대 '사원 번호'를 기준으로 '이름'과 '부서명'을 다른 표에서 찾아와 매칭하는 작업이죠. 이때 쓰는 부동의 1위 함수가 바로 VLOOKUP입니다.
VLOOKUP 공식 기본형:
=VLOOKUP(찾을_기준값, 참조할_범위, 가져올_열_번호, 0)맨 뒤의
0은 '정확히 일치하는 값'을 찾겠다는 약속이므로 실무에서는 무조건0또는FALSE를 적는다고 외우시는 것이 정신 건강에 좋습니다.
[초보자가 100% 범하는 치명적인 실수와 예방법]
가장 흔히 하는 실수는 수식을 아래로 드래그해 복사할 때, 참조할 범위가 한 칸씩 아래로 밀려 데이터가 누락되는 현상입니다. 수식을 작성할 때 참조 범위는 반드시 $ 기호를 붙여 고정해 주어야 합니다. (예: A1:B10 -> $A$1:$B$10). 수식 입력 중 F4 키를 누르면 자동으로 고정됩니다.
하지만 열심히 고정하고 수식을 완성해도, 찾는 데이터가 참조 범위에 없으면 무시무시한 #N/A 에러가 시트를 가득 채워 가독성을 엉망으로 만듭니다. 이때 에러 표시 대신 깔끔한 빈칸이나 "확인 필요"라는 문구를 띄우고 싶다면 IFERROR 함수로 VLOOKUP 전체를 싹 감싸주면 완벽하게 해결됩니다.
IFERROR 연동 공식:
=IFERROR(VLOOKUP(A2, $D$2:$F$10, 2, 0), "데이터 없음")이 공식 하나만 제대로 설계해 두어도, 거래처 목록에서 빠진 데이터를 식별하고 정리하는 작업이 눈 깜짝할 새 끝납니다.
## 2. VLOOKUP의 한계를 뛰어넘는 최강 콤비: INDEX & MATCH
실무에서 VLOOKUP을 쓰다 보면 큰 장벽에 가로막히는 순간이 있습니다. 바로 '내가 찾고자 하는 기준 열이 가져올 데이터보다 오른쪽에 있을 때'입니다. VLOOKUP은 무조건 기준 열이 참조 범위의 '맨 왼쪽(첫 번째 열)'에 있어야만 오른쪽 방향으로 데이터를 찾아올 수 있는 치명적인 태생적 한계가 있습니다.
이때 엑셀 고수들이 VLOOKUP을 과감히 버리고 사용하는 최강의 조합이 바로 INDEX와 MATCH의 결합입니다.
INDEX & MATCH 공식 기본형:
=INDEX(가져올_데이터_범위, MATCH(찾을_기준값, 기준값이_있는_열_범위, 0))작동 원리:
MATCH함수가 내가 찾는 값의 '위치(몇 번째 줄인지)'를 숫자로 찾아주면,INDEX함수가 해당 줄에 있는 데이터를 쏙 끄집어 배달해 주는 원리입니다.
이 조합을 쓰면 기준 열이 표의 맨 왼쪽에 있든, 중간에 있든, 맨 오른쪽에 있든 상관없이 사방팔방 역방향으로도 자유자재로 데이터를 매칭할 수 있습니다. 뿐만 아니라 대용량 데이터에서 VLOOKUP보다 연산 속도가 압도적으로 빠르기 때문에, 시트가 버벅거리는 현상을 막아주는 최고의 다이어트 공식이기도 합니다.
## 3. 조건에 맞는 행 전체를 실시간으로 긁어오기: FILTER
구글 스프레드시트가 마이크로소프트 엑셀보다 압도적으로 강력하다고 평가받는 결정적인 무기 중 하나가 바로 FILTER 함수입니다. 엑셀에서는 복잡한 매크로를 짜거나 수동으로 필터 단추를 눌러 복사해야 했던 작업을, 구글 시트에서는 함수 단 한 줄로 실시간 연동할 수 있습니다.
예를 들어, 전체 매출 표에서 '마케팅팀'이 올린 실적 데이터만 따로 골라내어 옆 영역이나 다른 시트에 표 형태로 깔끔하게 복사해 오고 싶을 때 사용합니다.
FILTER 공식 기본형:
=FILTER(가져올_전체_표_범위, 조건이_있는_열 = "조건값")만약 마케팅팀이면서 매출이 100만 원 이상인 복수 조건을 걸고 싶다면 뒤에 쉼표를 찍고 조건을 더해주면 끝입니다:
=FILTER(A2:D10, B2:B10 = "마케팅팀", C2:C10 >= 1000000)
원본 표에서 데이터가 추가되거나 수정되면, FILTER 함수가 적용된 결과 표에서도 실시간으로 행이 늘어나거나 줄어들며 자동 갱신됩니다. 이 함수를 맛보고 나면 매일 필터를 걸고 복사-붙여넣기 하던 수작업의 세상으로 절대 돌아갈 수 없게 됩니다.
[2편 핵심 요약]
VLOOKUP을 사용할 때는 수식이 밀리지 않도록 참조 범위에 반드시$(F4 키)를 붙여 고정하고,#N/A에러는IFERROR로 감싸 깔끔하게 마스킹한다.기준 열이 오른쪽에 있어 VLOOKUP이 불가능할 때는 좌우 방향 제약이 없고 연산 속도가 빠른
INDEX&MATCH공식을 사용한다.구글 스프레드시트의 독보적 사기 함수인
FILTER를 활용하면 조건에 맞는 행 전체를 실시간으로 다른 영역에 추출하고 연동할 수 있다.
[다음 편 예고]
다음 3편에서는 여러 명이 공동 작업을 할 때 오타나 잘못된 날짜 형식 입력으로 전체 수식이 와장창 깨지는 대참사를 원천 봉쇄하는 '데이터 유효성 검사(Data Validation)를 활용한 스마트 입력창 설계법'을 다룹니다.
평소에
VLOOKUP을 쓰면서 가장 자주 마주쳤던 오류 메시지는 무엇이었나요? 오늘 배운INDEX/MATCH나FILTER중 어떤 함수를 먼저 실무에 적용해보고 싶으신지 댓글로 남겨주세요!