들어가기 전에
많은 사람들이 Excel에서 INDEX-XMATCH 조합을 데이터세트에서 값을 검색하는 데 자주 사용합니다. 이 동적 조합을 사용하여 양방향 조회를 수행하여 지정된 행과 열의 교차점에서 값을 반환할 수도 있으며, 그 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How to Perform a Two-Way Lookup in Excel With INDEX and XMATC
- URL: https://www.howtogeek.com/microsoft-excel-index-xmatch-perform-two-way-lookup/
INDEX 및 XMATCH 함수 작동 방식
두 함수를 하나의 공식에서 함께 사용하는 방법을 살펴보기 전에, 두 함수가 따로따로 어떻게 작동하는지 잠깐 알아보겠습니다.
INDEX 함수
INDEX 함수는 지정한 행 번호와 열 번호에 따라 범위 내의 값을 반환합니다. 구문은 다음과 같습니다.
=INDEX(a, b, c)
- a: 범위
- b: 행 번호
- c: 열 번호
예를 들어, 다음과 같이 입력합니다.
=INDEX(T_Profit, 3, 6)
H2 셀에 T_Profit 표에서 지정한 행과 열이 교차하는 셀의 값을 반환합니다.
행과 열 번호를 직접 입력하는 대신 , 해당 행과 열 번호가 포함된 셀을 참조할 수 있어 수식을 더욱 유연하게 사용할 수 있습니다.
=INDEX(T_Profit, I2, I3)
I5 셀에 I2 셀의 행 번호와 I3 셀의 열 번호를 가져옵니다.
XMATCH 함수
XMATCH 함수는 범위에서 항목을 검색하여 해당 위치를 반환합니다. XMATCH 함수는 Excel 2021 이상(Microsoft 365용 Excel 포함)을 사용해야 합니다. 웹용 Excel , 태블릿 및 모바일용 Excel 앱에서도 사용할 수 있습니다.
구문은 다음과 같습니다.
=XMATCH(a, b, c, d)
- a: 찾을 항목
- b: 검색할 범위
- c: 일치 유형
- 0: 정확한 일치(기본값)
- -1: 정확한 일치 또는 다음으로 가장 작은 항목
- 1: 정확한 일치 또는 다음으로 가장 큰 항목
- 2: 와일드카드 일치
- d: 검색 모드
- 1: 처음부터 끝까지(기본값)
- -1: 마지막에서 처음으로
- 2: 이진 검색(b는 오름차순 정렬)
- -2: 이진 검색(b는 내림차순 정렬)
MATCH 함수에 대해 잘 알고 계실 겁니다. 이 함수는 XMATCH 함수의 이전 버전입니다. 두 함수는 작동 방식이 유사하지만, XMATCH 구문의 기본 인수는 MATCH 구문보다 더 직관적이어서 근사치 일치보다 정확한 일치를 선호합니다. 또한 XMATCH는 양방향 검색을 지원하고 부분 일치에는 와일드카드 문자를 사용할 수 있지만 , MATCH에서는 이 두 가지 기능을 모두 사용할 수 없습니다.
이 예에서는 다음을 입력합니다.
=XMATCH(1927, T_Profit[Employee])
H2 셀에 입력하면 3이 반환됩니다. 직원 ID 번호 1927이 T_Profit 테이블의 직원 열에 있는 세 번째 값이기 때문입니다.
이 시나리오에서는 인수 c 와 d가 필요하지 않은 점에 유의하세요. 테이블의 맨 위에서 맨 아래까지 검색을 실행하여 정확한 일치를 찾고 싶기 때문입니다. 이것이 이 함수의 기본 설정입니다.
마찬가지로 인수 a는 조회 값이 포함된 셀에 대한 참조가 될 수 있으므로 수식을 편집하지 않고도 조회 값을 쉽게 변경할 수 있습니다.
=XMATCH(I2, T_Profit[Employee])
단방향 조회를 위해 XMATCH와 함께 INDEX 사용
INDEX 함수와 XMATCH 함수는 각각 독립적으로도 유용하지만, 함께 사용할 때 진정한 잠재력을 발휘합니다. INDEX-XMATCH 조합을 사용하여 양방향 조회를 수행하는 방법을 이해하려면 먼저 1차원 상황에서 이 함수가 어떻게 작동하는지 이해하는 것이 중요합니다.
직원의 ID를 I2 셀에 입력했을 때 발생하는 총 이익을 보고 싶다고 가정해 보겠습니다.
이렇게 하려면 I2 셀에 다음 수식을 입력합니다.
=INDEX(T_Profit, XMATCH(I2, T_Profit[Employee]), 6)
- T_Profit: 값을 찾을 테이블 이름
- XMATCH(I2,T_Profit[Employee]): I2 셀 값을 기반으로 INDEX 함수에 Employee 열의 어느 행을 찾아야 하는지 지정
- 6: INDEX 함수에게 해당 행의 여섯 번째 열에 있는 값을 반환하라고 지시
참고로, 구조화된 테이블 참조를 수동으로 입력하는 대신 , 열 머리글을 클릭하여 전체 열을 선택하세요.
이 시나리오에서는 수식의 XMATCH 부분에 대한 일치 유형이나 검색 모드 인수를 입력할 필요가 없습니다. 기본 설정은 정확한 일치를 반환하고 위에서 아래로 검색하기 때문입니다.
하지만 특정 연도의 직원 이익과 같이 다른 열의 값을 반환하고 싶다면 어떻게 해야 할까요? 이럴 때 양방향 조회가 유용합니다.
양방향 조회를 위해 XMATCH와 함께 INDEX 사용
양방향 조회에 INDEX와 XMATCH를 함께 사용하면 수식을 편집하지 않고도 검색 매개변수를 변경할 수 있다는 장점이 있습니다. XMATCH는 행 번호와 열 번호를 모두 식별하므로 수식에 직접 코드를 입력할 필요가 없습니다.
직원 1191이 2021년에 얼마의 이익을 냈는지 빠르게 알아보고 싶다고 가정해 보겠습니다.
이렇게 하려면 I4 셀에 다음 수식을 입력합니다.
=INDEX(T_Profit, XMATCH(I2, T_Profit[Employee]), XMATCH(I3, T_Profit[#Headers]))
- T_Profit: 값이 발견될 테이블의 이름
- XMATCH(I2,T_Profit[Employee]): I2 셀 값을 기반으로 INDEX 함수에 Employee 열의 어느 행을 찾아야 하는지 알려줌
- XMATCH(I3,T_Profit[#Headers]): I3 셀 값을 기준으로 INDEX 함수에 T_Profit 테이블의 어느 열을 찾아야 하는지 알려줌
열 머리글에 날짜와 같은 숫자 값이 포함되어 있는 경우 Enter 키를 누르면 #N/A 오류가 표시될 수 있습니다.
Excel에서는 열 머리글이 숫자로 표시되더라도 실제로는 텍스트로 저장되기 때문입니다. 따라서 조회 값과 조회 배열이 동일한 방식으로 일치하도록 하려면 해당 조회 값이 포함된 셀(이 예에서는 I3 셀)을 선택하고 리본의 홈 탭에서 숫자 서식 드롭다운 메뉴에서 "텍스트"를 클릭합니다.
그런 다음 열 조회 값(I3)이 포함된 셀을 선택하고 F2 키를 눌러 셀 편집 모드를 활성화한 후 Enter 키를 누릅니다. 이제 Excel에서 조회 값과 조회 배열의 숫자 형식이 모두 동일한 것으로 인식되므로 수식이 예상 값을 올바르게 반환합니다.
이제 I2와 I3 셀에 다양한 조회 매개변수를 입력하고 수식이 해당 결과를 반환하는지 확인하세요.
마치며
이제 INDEX와 XMATCH를 사용하여 양방향 조회를 수행하는 방법을 알았으므로, 한 단계 더 나아가 데이터 유효성 검사를 사용하여 조회 값이 포함된 셀에 드롭다운 메뉴를 만들 수 있습니다. 이렇게 하면 조회 프로세스가 더욱 빨라지고 실수로 잘못된 값을 입력하는 것도 방지할 수 있습니다.
'Excel' 카테고리의 다른 글
| 시간 낭비를 멈추게 해 주는 3가지 Excel 함수 (0) | 2025.12.11 |
|---|---|
| 꼭 알아야 할 4가지 Excel 함수 조합 (0) | 2025.12.10 |
| 12개 기능 키 단축키로 스프레드시트 작업 빠르게 처리하기 (0) | 2025.12.08 |
| [강의 예고] 오타 내고 싶어도 못 냅니다. 엑셀 입력 실수 '원천 봉쇄' 수식 (0) | 2025.12.07 |
| 번개처럼 빠른 조회를 할 수 있는 4가지 Excel 함수(또는 수식) (0) | 2025.12.04 |
