Categories: 미분류

엑셀365 INDEX와 MATCH 함수로 정밀한 데이터 찾기

엑셀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로 데이터 검색 실력을 한 단계 업그레이드해보세요.

huniverse5