지난 포스팅에서 다뤘던 VLOOKUP 함수는 매우 유용하지만, 결정적인 단점이 하나 있습니다. 바로 '기준 열의 왼쪽에 있는 데이터는 가져올 수 없다'는 점입니다. 또한, 데이터가 방대해지면 계산 속도가 느려지기도 하죠.
이러한 VLOOKUP의 치명적인 한계를 완벽하게 해결해 주는 것이 바로 INDEX와 MATCH 함수의 조합입니다. 오늘은 실무 고수들만 안다는 이 두 함수의 협업 원리와 활용법을 상세히 파헤쳐 보겠습니다.
1. INDEX와 MATCH, 각각 어떤 역할을 하나요?
이 두 함수를 합치기 전에, 각각의 기능을 먼저 이해하는 것이 중요합니다.
INDEX 함수: "좌표로 값 찾기"
INDEX 함수는 지정한 범위 내에서 특정 행과 열이 교차하는 지점의 값을 가져옵니다.
구문:
=INDEX(범위, 행_번호, [열_번호])역할: "이 표에서 5번째 줄, 2번째 칸에 있는 값을 가져와!"라고 명령하는 것과 같습니다.
MATCH 함수: "값의 위치 번호 찾기"
MATCH 함수는 내가 찾는 값이 특정 범위 내에서 몇 번째에 위치하는지 '숫자'로 반환합니다.
구문:
=MATCH(검색_값, 범위, [일치_유형])역할: "이 명단에서 '홍길동'이 몇 번째 줄에 있어?"라고 물어보고 그 줄 번호를 알려줍니다.
2. 왜 INDEX와 MATCH를 같이 써야 할까요? (VLOOKUP과의 차이)
VLOOKUP보다 이 조합이 강력한 이유는 크게 세 가지입니다.
왼쪽 방향 검색 가능: VLOOKUP은 기준 열 기준 오른쪽 데이터만 찾을 수 있지만, INDEX MATCH는 데이터 위치와 상관없이 어디든 찾을 수 있습니다.
유지보수의 편리함: 중간에 열을 삽입하거나 삭제해도 수식이 깨지지 않습니다. VLOOKUP은 열 번호를 숫자로 직접 입력하기 때문에 표 구조가 바뀌면 오류가 발생합니다.
대용량 데이터 최적화: 수만 행의 데이터에서 VLOOKUP보다 훨씬 빠르게 결과값을 계산합니다.
3. 실전! INDEX MATCH 합체 공식
이제 두 함수를 하나로 합쳐보겠습니다. 기본 구조는 다음과 같습니다.
=INDEX(가져올_값이_있는_열, MATCH(찾을_기준_값, 기준_열, 0))
사례: 사원번호로 사원명 찾기 (사원명이 사원번호보다 왼쪽에 있을 때)
A열: 사원명
B열: 사원번호
C열: 부서
사원번호(B열)를 기준으로 왼쪽에 있는 사원명(A열)을 찾고 싶다면?
=INDEX(A:A, MATCH("S2026", B:B, 0))
MATCH: B열에서 "S2026"이라는 사원번호가 몇 번째 행에 있는지 찾습니다. (예: 5행)
INDEX: A열(사원명 열)에서 MATCH가 찾아준 5번째 행의 값을 가져옵니다.
4. 자주 발생하는 오류 및 해결 팁
#N/A 오류
MATCH 함수에서 찾는 값이 범위 내에 없을 때 발생합니다. 정확한 값을 찾으려면 MATCH의 마지막 인자인 '일치_유형'을 반드시 0으로 설정했는지 확인하세요.
#REF! 오류
INDEX 범위로 지정한 영역보다 더 큰 행/열 번호를 참조하려고 할 때 발생합니다. 범위 지정이 제대로 되었는지, 혹은 전체 열(A:A 등)을 선택했는지 체크해 보세요.
5. [심화] 다중 조건으로 데이터 찾기
VLOOKUP으로는 불가능에 가까운 '두 가지 이상의 조건'을 만족하는 값 찾기도 가능합니다.
예를 들어, '학년'과 '반'이라는 두 가지 조건을 모두 만족하는 학생의 이름을 찾을 때 INDEX MATCH는 수식 하나로 깔끔하게 해결할 수 있습니다. (이 부분은 다음 심화 가이드에서 더 자세히 다루겠습니다.)
결론 및 요약
처음에는 INDEX와 MATCH를 따로 쓰는 것이 복잡해 보일 수 있습니다. 하지만 "MATCH로 줄 번호를 찾고, INDEX로 그 줄의 값을 집어온다"는 개념만 잡으면 VLOOKUP보다 훨씬 안전하고 강력한 데이터 관리가 가능해집니다.
스프레드시트 실력을 한 단계 업그레이드하고 싶다면, 오늘부터 VLOOKUP 대신 INDEX MATCH를 연습해 보시는 건 어떨까요?
댓글 없음:
댓글 쓰기