엑셀365 INDEX와 MATCH 함수란?
엑셀365에서 INDEX 함수와 MATCH 함수를 결합하면, 데이터를 매우 정밀하고 유연하게 검색할 수 있습니다. 특히 VLOOKUP 함수가 가진 방향 제한(왼쪽 열 참조 불가)이나 열 번호 고정 문제를 완전히 극복할 수 있어, 실무에서 데이터 조회 정확도를 높이고, 유지보수를 쉽게 만들 수 있는 조합입니다.
INDEX함수: 지정한 범위에서 행과 열 번호를 기준으로 값을 반환MATCH함수: 특정 값이 범위 내 몇 번째 위치에 있는지를 반환
두 함수를 결합하면 “어디에서 무슨 값을 찾고, 결과를 몇 번째에서 가져올지”를 유연하게 제어할 수 있습니다. 특히 데이터의 구조가 바뀌더라도 수식 오류 없이 작동하므로 표 구조 변경에 강한 검색 공식으로 널리 사용됩니다.
INDEX 함수 기본 구조와 예시
기본 문법:
=INDEX(array, row_num, [column_num])
array: 참조할 범위row_num: 가져올 행 번호column_num: (선택 사항) 가져올 열 번호
예시:
=INDEX(A2:C5, 2, 3)
→ A2:C5 범위에서 2번째 행, 3번째 열의 값을 반환
결과:
| A | B | C |
|---|---|---|
| 홍길동 | 서울 | 1000 |
| 김영희 | 부산 | 1200 |
즉, INDEX는 “몇 번째 행, 몇 번째 열”의 교차점 값을 추출합니다.
MATCH 함수 기본 구조와 예시
기본 문법:
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value: 찾을 값lookup_array: 검색 범위(한 행 또는 한 열)match_type: 일치 방법 (0은 정확히 일치)
예시:
=MATCH("부산", B2:B5, 0)
→ B열에서 ‘부산’이 있는 행 번호 2 반환 (두 번째 위치)
MATCH 함수는 INDEX 함수의 행 또는 열 번호를 동적으로 계산하는 데 최적입니다. 이 둘을 결합하면 데이터 구조가 바뀌어도 자동 추적 가능해집니다.
INDEX + MATCH 조합 실무 예시
예시 1: 이름으로 급여 조회
| A | B | C |
|---|---|---|
| 사번 | 이름 | 급여 |
| A1001 | 김철수 | 3200 |
| A1002 | 이영희 | 3000 |
=INDEX(C2:C4, MATCH("이영희", B2:B4, 0))
→ B열에서 ‘이영희’를 찾아 같은 행의 C열(급여) 값을 반환 → 결과: 3000
예시 2: 상품명으로 단가 검색 (열 번호 자동 매칭)
| A | B | C |
|---|---|---|
| 코드 | 상품명 | 단가 |
| P001 | 모니터 | 150000 |
| P002 | 키보드 | 30000 |
=INDEX(A2:C3, MATCH("키보드", B2:B3, 0), MATCH("단가", A1:C1, 0))
→ 행 위치는 상품명 기준, 열 위치는 제목 ‘단가’ 기준으로 자동 매칭 → 결과: 30000
장점: 열 위치를 숫자로 고정하지 않으므로, 표 구조가 바뀌어도 수식 수정 필요 없음
INDEX + MATCH 함수의 실무 장점
| VLOOKUP | INDEX + MATCH |
|---|---|
| 검색 기준은 첫 번째 열로 고정 | 어떤 열에서도 검색 가능 |
| 열 번호를 숫자로 입력해야 함 | MATCH로 동적 계산 가능 |
| 왼쪽 열은 참조 불가 | 왼쪽/오른쪽 자유롭게 참조 가능 |
| 구조 변경 시 오류 발생 | 구조 변경에 유연하게 대응 |
실무에서 제품 코드로 제품명 찾기, 직원명으로 사번 찾기, 고객명으로 등급 확인 등 다양한 상황에 적용할 수 있으며, 특히 표가 크거나 복잡할수록 INDEX + MATCH의 진가가 발휘됩니다.
동적 범위 + INDEX + MATCH 조합
엑셀365의 동적 배열이나 OFFSET, COUNTA를 활용해 INDEX + MATCH 수식을 동적으로 확장할 수도 있습니다.
예시: 데이터가 계속 늘어날 때
=INDEX(매출!C2:C1000, MATCH(A2, 매출!A2:A1000, 0))
→ A2에 입력된 고객명을 기준으로 매출 시트에서 매칭된 금액 가져오기
여기서 C열 매출 데이터가 늘어나면 OFFSET이나 테이블 기능(Ctrl + T)을 통해 범위가 자동 확장되도록 설정하면 유지보수가 쉬워집니다.
Q&A
Q. INDEX + MATCH가 VLOOKUP보다 느리다는데 사실인가요?
실제로는 VLOOKUP보다 INDEX + MATCH가 더 빠릅니다, 특히 열 수가 많거나 큰 범위를 참조할 경우에 더욱 그렇습니다.
이유는 VLOOKUP은 전체 테이블을 읽고 왼쪽부터 오른쪽으로 값을 찾지만, INDEX + MATCH는 열 단위로 검색하고 반환하므로 불필요한 데이터 접근을 줄일 수 있습니다.
단, 너무 많은 수식을 사용하는 경우는 처리 속도에 영향을 줄 수 있으므로 동적 범위와 결합해 필요한 부분만 참조하는 방식이 효율적입니다.
Q. INDEX + MATCH로 다중 조건 검색이 가능한가요?
네, INDEX + MATCH 조합을 배열 수식 형태로 확장하면 여러 조건을 동시에 만족하는 값도 검색할 수 있습니다.
예시: 이름이 ‘홍길동’이고, 부서가 ‘영업’인 사람의 급여 찾기
=INDEX(D2:D100, MATCH(1, (B2:B100="홍길동")*(C2:C100="영업"), 0))
※ 배열 수식으로 Ctrl + Shift + Enter 입력 필요 (또는 엑셀365는 자동 배열)
이 방식은 실무에서 복합 키로 데이터 찾을 때 매우 유용하며, VLOOKUP으로는 처리할 수 없는 고급 조건도 손쉽게 구현할 수 있습니다.
Q. MATCH가 잘못된 위치를 반환하는 경우는 왜 그런가요?
MATCH 함수에서 match_type을 잘못 설정하면 예상치 못한 결과가 발생할 수 있습니다.
0: 정확히 일치 (가장 일반적이고 안전)1: 근사치, 오름차순 정렬 필요-1: 근사치, 내림차순 정렬 필요
대부분의 실무에서는 0을 사용하는 것이 안전합니다. 특히 데이터가 정렬되지 않았을 경우 1이나 -1은 잘못된 결과를 유발할 수 있습니다.
엑셀365의 INDEX와 MATCH 함수는 복잡한 데이터에서도 정확하게 필요한 값을 찾아내는 강력한 검색 도구입니다. 수식의 유연성, 방향 자유도, 유지보수 편의성 등 VLOOKUP보다 훨씬 정밀한 제어가 가능해지므로, 실무에서 자주 사용하는 조회 작업에는 반드시 익혀야 할 필수 공식입니다. INDEX + MATCH로 데이터 검색 실력을 한 단계 업그레이드해보세요.