빠른 링크
- INDEX와 XMATCH 함수의 동작 방식
- INDEX + XMATCH로 단방향(한 방향) 조회하기
- INDEX + XMATCH로 2차원(양방향) 조회하기
- 문제 해결 및 고급 팁
이 문서는 초급부터 고급 사용자까지 모두를 대상으로 합니다. 처음부터 차근차근 설명하며, 이미 INDEX와 XMATCH로 단방향 조회를 알고 있다면 “INDEX + XMATCH로 2차원(양방향) 조회하기” 섹션으로 바로 이동하세요.
INDEX와 XMATCH 함수의 동작 방식
먼저 각각의 함수가 독립적으로 어떻게 동작하는지 살펴보겠습니다. 기초를 이해하면 두 함수를 하나의 수식에서 결합할 때 훨씬 이해하기 쉬워집니다.
INDEX 함수
INDEX 함수는 지정한 범위에서 행 번호와 열 번호에 해당하는 값을 반환합니다. 기본 구문은 다음과 같습니다:
=INDEX(a,b,c)여기서
- a: 값이 들어 있는 범위(또는 테이블)
- b: 반환할 행 번호
- c: 반환할 열 번호
예: 셀 H2에 다음 수식을 입력하면:
=INDEX(T_Profit,3,6)T_Profit 표의 3행, 6열에 있는 값을 반환합니다.
행과 열 번호를 하드코딩할 필요는 없습니다. 번호가 들어 있는 셀을 참조하면 수식이 더 유연해집니다. 예를 들어:
=INDEX(T_Profit,I2,I3)이라고 입력하면 I2에서 행 번호를, I3에서 열 번호를 가져옵니다.
정의: INDEX — 범위와 인덱스(행/열)를 받아 해당 위치의 값을 반환하는 함수.
XMATCH 함수
XMATCH는 범위에서 항목을 검색하고 그 위치(순번)를 반환합니다. 구문은 다음과 같습니다:
=XMATCH(a,b,c,d)여기서
- a: 찾을 값(또는 셀 참조)
- b: 검색할 범위
- c: 일치 유형(0=정확히 일치(기본),-1=정확히 일치 또는 바로 작은 값,1=정확히 일치 또는 바로 큰 값,2=와일드카드)
- d: 검색 방향(1=첫~마지막(기본),-1=마지막~첫,2=이진(오름차순),-2=이진(내림차순))
XMATCH는 MATCH의 최신 대체 함수로, 기본값이 정확 일치이며 검색 방향 지정과 와일드카드가 가능합니다.
예: 셀 H2에 다음을 입력하면:
=XMATCH(1927,T_Profit[Employee])T_Profit 표의 Employee 열에서 1927이 있는 위치(세 번째 항목)이므로 3을 반환합니다.
XMATCH의 c와 d 인수는 옵션입니다. 정확 일치(0)와 위→아래(1)가 기본이므로 대부분의 단순 조회에서 생략합니다.
셀 참조를 사용하면 동일한 수식을 다양한 조회값에 재사용할 수 있습니다. 예:
=XMATCH(I2,T_Profit[Employee])여기서 I2에 조회할 직원 ID를 입력하면 됩니다.
정의: XMATCH — 범위에서 항목의 위치(순번)를 찾아 반환하는 함수.
INDEX + XMATCH로 단방향(한 방향) 조회하기
두 함수의 결합이 유용한 이유는 하나의 수식으로 위치 검색과 값 반환을 모두 처리할 수 있기 때문입니다. 먼저 1차원(행 또는 열) 단독 조회를 알아보겠습니다.
예: 직원 ID를 I2에 입력하면 해당 직원의 총 이익(Total Profit)을 보여주고 싶다면, 다음과 같이 할 수 있습니다:
=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)- T_Profit: 검색할 테이블
- XMATCH(I2,T_Profit[Employee]): INDEX에 전달할 행 번호(직원 ID의 위치)
- 6: 해당 행의 6번째 열(예: Total Profit)을 반환
표 구조를 직접 클릭해 구조화 참조(예: T_Profit[Employee])를 사용하면 수식이 더 읽기 쉽습니다.
장점
- VLOOKUP보다 유연합니다. 열 순서에 영향을 받지 않습니다.
- 테이블 범위를 변경해도 구조화 참조는 자동으로 업데이트됩니다.
언제 단방향이 충분한가?
- 조회하려는 값이 항상 특정 열(또는 행)에 고정되어 있는 경우.
- 단일 키(예: 직원 ID)로 고유한 행을 찾을 때.
INDEX + XMATCH로 2차원(양방향) 조회하기
두 가지 변수를 조합해 교차점의 값을 찾으려면 XMATCH를 두 번 사용해 행 번호와 열 번호를 각각 구합니다. 이 방식은 예를 들어 특정 직원이 특정 연도에 벌어들인 이익 같은 정보를 조회할 때 유용합니다.
예: 직원 1191이 2021년에 벌어들인 이익을 셀 I4에 표시하려면:
=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))- XMATCH(I2,T_Profit[Employee]): 행 번호
- XMATCH(I3,T_Profit[#Headers]): 테이블 헤더에서 해당 연도(또는 컬럼명)의 위치(열 번호)
이 방법의 핵심은 열 헤더(또는 행 헤더)를 조회 배열로 삼아 XMATCH로 열 번호를 구하는 것입니다.
텍스트/숫자 서식 불일치 문제와 해결
때때로 컬럼 헤더가 숫자(예: 연도)처럼 보이지만 Excel은 이를 텍스트로 저장할 수 있습니다. 이 경우 XMATCH가 #N/A를 반환할 수 있습니다.
문제 해결 단계:
- 조회 값(I3 등)과 헤더 배열(T_Profit[#Headers])의 데이터 형식을 일치시킵니다.
- 셀을 선택한 뒤 리본의 “홈” 탭에서 숫자 형식을 “텍스트”로 설정합니다.
- 셀을 편집 모드(F2)로 들어간 뒤 Enter를 눌러 형식 적용을 강제합니다.
이 과정을 거치면 XMATCH가 헤더와 조회값을 정상적으로 비교하여 정확한 열 번호를 반환합니다.
동적 드롭다운(데이터 유효성 검사)로 조회 안정성 향상
조회 입력값(I2, I3 등)에 데이터 유효성 검사를 설정해 드롭다운을 사용하면 오타를 막고 조회 속도를 높일 수 있습니다. 단, 표의 헤더를 바로 소스로 쓸 수 없는 경우가 있으므로 아래 방법을 사용하세요.
권장 방법:
- 헤더 범위를 직접 셀 참조로 입력하거나(예: =$B$1:$G$1),
- 헤더 범위를 이름 정의(수식 → 이름 관리자)한 뒤 데이터 유효성 검사 소스에 해당 이름을 입력합니다.
이제 드롭다운을 선택해 빠르게 조회할 수 있습니다.
고급 팁과 활용법
아래는 실무에서 자주 마주치는 상황과 권장 대처법입니다.
1) 대체 방법: XLOOKUP, INDEX+MATCH, FILTER 비교
- XLOOKUP: 한 축(행 또는 열)만 고정된 경우 간단하고 직관적입니다. 그러나 2차원 교차값을 바로 찾으려면 추가 조합이 필요합니다.
- INDEX + MATCH(구 MATCH): 호환성(구버전 Excel) 때문에 아직 사용됩니다. XMATCH가 가능하면 XMATCH가 더 직관적입니다.
- FILTER: 특정 조건(예: 행 조건과 열 조건 모두)으로 결과를 필터링할 때 유용합니다. 배열 수식을 반환하므로 동적 배열 환경에서 강력합니다.
언제 어떤 방법을 선택하나?
- 최신 Excel(Office 365 등): XMATCH + INDEX 권장.
- 호환성(Excel 2016 이하): MATCH + INDEX 또는 VLOOKUP 대안 고려.
- 다중 조건 필터링: FILTER 또는 SUMPRODUCT 등 고려.
2) 엣지 케이스(문제가 생기는 경우)
- 중복 키: XMATCH는 첫 번째(또는 마지막) 항목을 반환합니다. 고유 식별자(Primary Key)를 사용하세요.
- 공백 셀: 검색범위에 공백이 있으면 위치 결정이 의도와 다른 결과를 줄 수 있습니다.
- 정렬 의존 코드: XMATCH의 이진 검색 모드를 잘못 사용하면 엉뚱한 값을 반환할 수 있습니다.
3) 성능 고려사항
- 작은 표: 차이를 느끼기 어렵습니다.
- 매우 큰 범위(수만 행): 구조화 테이블과 구조화 참조, 불필요한 전체 열 참조(A:A) 사용을 피하고 필요한 범위만 지정하세요.
4) 보안/프라이버시
조회 수식 자체는 민감한 데이터를 노출하지 않지만, 드롭다운 목록이나 숨김 열에 민감 정보가 포함되어 있으면 주의하세요. 파일 공유 시 해당 시트/열을 숨기거나 보호하세요.
실무용 체크리스트(역할별)
데이터 관리자
- 모든 키(예: Employee ID)가 고유한지 확인하세요.
- 헤더의 데이터 유형(텍스트/숫자)을 표준화하세요.
- 범위를 테이블(Table)로 변환해 자동 확장을 활용하세요.
보고서 작성자
- INDEX+XMATCH 수식에 셀 참조를 사용해 재사용 가능하게 만드세요.
- 데이터 유효성 검사를 적용해 입력 오류를 줄이세요.
개발자/자동화 담당자
- 큰 범위에서 이진 검색(옵션 2/-2)을 사용할 때는 명확히 정렬 상태를 보장하세요.
- 가능한 경우 구조화 참조를 사용해 코드 유지보수를 쉽게 하세요.
간단한 SOP(표준 작업 절차): 2차원 조회 템플릿 만들기
- 원본 데이터를 선택하고 Ctrl+T로 표(Table)로 변환합니다. 이름을 예: T_Profit로 지정.
- 조회 입력 셀(예: I2=Employee ID, I3=Year)을 준비합니다.
- I4에 다음 수식을 입력합니다:
=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))- I2, I3에 데이터 유효성 검사(Drop-down)를 설정합니다. 소스는 명시적 셀 범위(또는 이름 범위)를 사용합니다.
- 테스트: 존재하지 않는 ID 또는 헤더를 입력했을 때 #N/A 또는 사용자 정의 오류 메시지를 확인합니다.
- 문서화: 수식의 목적과 입력 셀 설명을 셀 주석으로 남깁니다.
테스트 케이스(수식 검증)
- 정상 케이스: 존재하는 ID와 존재하는 연도 → 정확한 값 반환.
- 키 오류: 존재하지 않는 ID → #N/A 발생.
- 헤더 형식 오류: 헤더는 텍스트인데 I3가 숫자 형식이면 → #N/A 발생. 해결: 형식 일치.
- 중복 키: 동일한 ID가 여러 행에 존재하면 → 첫 번째(위에서부터) 위치 반환.
의사결정 흐름(간단한 도식)
flowchart TD
A[조회 요구] --> B{키'고유' 존재?}
B -- 예 --> C{열'또는 행' 고정?}
B -- 아니오 --> D[데이터 정규화 후 재시도]
C -- 예 --> E[INDEX + XMATCH 사용]
C -- 아니오 --> F[XLOOKUP 또는 FILTER 고려]
E --> G[결과 검증 및 드롭다운 추가]
F --> G예시 시나리오: 단계별 실습
- 표 준비: A1:G100 범위를 T_Profit로 변환.
- I2에 조회할 Employee ID 입력. I3에 연도(또는 헤더명) 입력.
- I4에 INDEX+XMATCH 수식 입력.
- 헤더와 I3의 형식 불일치 시 홈 → 숫자 형식 → 텍스트로 맞춤.
- 드롭다운(데이터 유효성) 적용으로 입력 실수 제거.
언제 이 방식이 실패하는가(반례)
- 조회하려는 값이 행과 열 모두에서 중복될 때(예: 여러 행의 동일 직원ID가 있고, 열 헤더도 중복되는 경우) 이 방법은 적절한 값을 보장하지 못합니다.
- 대규모 데이터에서 전체 열 참조(A:A)를 사용하면 성능 저하가 발생합니다.
- 헤더가 동적으로 생성되거나 외부 연결로 변경될 때 구조화 참조를 관리하지 않으면 참조 오류가 발생할 수 있습니다.
호환성 및 마이그레이션 팁
- XMATCH는 Excel 2021, Microsoft 365, Excel for the web, 모바일/태블릿 버전에서 사용 가능합니다. 이전 버전(예: Excel 2016 이하)에서는 XMATCH가 없을 수 있습니다.
- 이전 버전과의 호환성이 필요하면 XMATCH 대신 MATCH를 사용하세요. MATCH의 기본 인수는 XMATCH와 다르므로 주의가 필요합니다.
팁 요약(핵심)
- INDEX는 위치 기반 값 반환 함수입니다.
- XMATCH는 위치(순번)를 반환하는 최신 MATCH 대체 함수입니다.
- 두 함수를 결합하면 동적이고 유연한 2차원 조회가 가능합니다.
- 데이터 형식(텍스트/숫자)을 항상 일치시켜 오류(#N/A)를 예방하세요.
- 데이터 유효성 검사로 입력을 제한하면 실무에서 오류를 크게 줄일 수 있습니다.
자주 묻는 질문(FAQ)
Q1: INDEX와 XMATCH를 항상 함께 써야 하나요?
A1: 반드시 그렇지는 않습니다. 단일 열에서 간단히 값을 찾을 때는 XMATCH만으로도 충분합니다. 다만 교차형 조회(행·열 둘 다 필요)에서는 함께 사용하는 것이 가장 유리합니다.
Q2: XMATCH가 내 Excel에서 작동하지 않아요. 대안은?
A2: XMATCH가 없다면 MATCH 함수를 사용하세요. 구문과 기본 동작이 다르므로 MATCH의 세 번째 인수(정렬 옵션)를 확인하고 조정해야 합니다.
Q3: 중복 키가 있으면 어떻게 해야 하나요?
A3: 기본적으로 XMATCH는 첫 번째(또는 마지막) 항목만 반환합니다. 중복을 허용하지 않도록 데이터 정제(중복 제거 또는 고유 키 부여)를 권장합니다.
Q4: 수식이 느려요. 성능을 개선하려면?
A4: 전체 열 참조(A:A) 대신 필요한 범위만 지정하고, 가능한 경우 표(Table) 구조를 사용하세요. 또한 배열 수식을 과도하게 사용하면 느려질 수 있으니 주의하세요.
끝으로, INDEX와 XMATCH 조합은 유연성과 유지보수성 면에서 매우 강력합니다. 기본을 잘 다지고 데이터 품질(고유 키, 서식 일치)을 확보하면 실무 보고서와 대시보드에서 빠르고 정확한 조회를 구현할 수 있습니다.
