엑셀의 XMATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환합니다.
이 함수는 용도가 비슷한 MATCH 함수 기능에 다음과 같은 유용한 기능이 추가되었습니다.
- MATCH 함수는 근사값을 찾을 때는 값이 정렬되어 있어야 하지만 XMATCH 함수는 정렬되어 있지 않아도 값을 찾을 수 있다.
- 빠른 검색을 위해 이진검색모드를 따로 지정할 수 있다. 단, 이진검색을 사용할 때는 범위가 정렬되어 있어야 한다.
- 와일드카드 검색(*,?)을 지원한다. '모나미*'로 찾으면 '모나미'로 시작하는 '모나미볼펜' , '모나미문구' 등을 찾을 수 있다.
- 역방향 검색을 지원한다. 배열 또는 범위의 마지막 항목부터 첫번째 항목으로 역방향으로 값을 찾을 수 있다.
MATCH 함수와 마찬가지지로 XMATCH 함수도 단순히 위치만 찾아 주므로 위치만 가지고 쓸 일이 많이 없습니다. INDEX 함수 등 다른 함수와 함께 사용해서 성적 등급을 계산하는 등 응용해서 많이 사용되므로 함수의 사용법을 잘 익혀 두면 복잡한 문제를 처리할 때 많은 도움이 됩니다.
※ XMATCH함수는 Microsoft 365, 엑셀 2021 이상 버전에서만 사용할 수 있습니다.
구문(Syntax)
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
지정된 범위에서 찾고자 하는 값의 위치를 반환한다.
인수 :
- lookup_value : 찾고자 하는 값
- lookup_array : 값을 찾을 배열 또는 범위
- match_mode : (생략가능) 일치 유형, 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정
0 lookup_value와 정확히 일치하는 것을 찾음(기본값) -1 lookup_value와 정확히 일치하거나 작은 것을 찾음 1 lookup_value와 정확히 일치하거나 큰 것을 찾음 2 와일드카드 검색(?는 1개 문자, *는 여러문자) - search_mode : (생략가능)검색 유형
1 첫 번째 항목부터 검색(기본값) -1 마지막 항목부터 역방향 검색 2 오름차순으로 정렬된 lookup_array를 이진 검색 , 정렬 되지 않은 경우 잘못 된 결과가 반환됨 -2 내림차순으로 정렬된 lookup_array를 이진 검색, 정렬 되지 않은 경우 잘못 된 결과가 반환됨
(Tip!) 와일드카드 검색
'*'또는 '?'를 이용하여 특정문자를 대체하여 유사한 값을 찾는 것을 말한다.
'*'은 여러 개의 문자를 의미, '모나미*'로 찾으면 '모나미볼펜', '모나미형광펜' 등 '모나미'로 시작하는 텍스트는 모두 찾을 수 있다.
'?'는 한개의 문자를 의미, '모나미???'로 찾으면 '모나미볼펜', '모나미형광펜' 에서 '모나미'로 시작하고 뒤에 3글자인 '모나미형광펜'만 찾아진다.
사용 예
1. 범위가 정렬되어 있을 경우에 위치 찾기
아래와 같이 자료에서 취득점수가 오름차순으로 정렬되어 있을 때 XMATCH함수로 값의 위치를 찾아보겠습니다.
1) 일치하는 값의 위치 찾기
세번째 인수 match_mode를 0(일치 하는 것을 찾음)으로 입력
=XMATCH(85,$B$6:$B$14,0) => 7
셀 범위 [$B$6:$B$14]에서 찾는 값 85는 7번째에 있으므로 7을 반환합니다.
(주의!) match_mode가 0(일치 하는 것을 찾음)이므로 표에서 82를 검색하면 존재하지 않으므로 #N/A오류가 발생합니다. #N/A오류는 '찾는 값이 없다'는 것을 뜻합니다.
=XMATCH(82,$B$6:$B$14,0) => #N/A
(Tip!) 취득점수가 정렬되어 있으므로 위 수식에 네 번째 인수인 search_mode를 2(오름차순으로 정렬된 lookup_array를 이진 검색)를 추가하면 반환결과는 동일하고 검색속도가 빨라집니다. 자료의 양이 많고 정렬되어 있다면 이진검색모드로 설정하면 속도면에서 유리합니다.
=XMATCH(85,$B$6:$B$14,0,2) => 7
2) 근사값의 위치 찾기(일치하거나 작은 항목)
세번째 인수 match_mode를 -1(일치하거나 작은 것을 찾음)로 입력
=XMATCH(92,$B$6:$B$14,-1) => 8
셀 범위 [$B$6:$B$14]에서 찾는 값 92는 존재하지 않으므로 92보다 작은 90을 찾아서 위치 8을 반환합니다.
3) 근사값 찾기(일치하거나 큰 항목)
세번째 인수 match_mode를 1(일치하거나 큰 것을 찾음)로 입력
=XMATCH(92,$B$6:$B$14,1) => 9
셀 범위 [$B$6:$B$14]에서 찾는 값 92는 존재하지 않으므로 92보다 큰 95를 찾아서 위치 9를 반환합니다.
4) INDEX함수와 결합하여 성적등급 찾기
점수구간별로 성적등급이 정해져 있을 경우에 MATCH함수로 근사값의 행의 위치를 찾아서 INDEX함수로 넘기면 INDEX함수는 같은 행에 있는 성적등급을 찾아 줍니다.
=INDEX(C6:C14,XMATCH(92,B6:B14,-1),1)
① 이 수식은 중첩되어 있으므로 안쪽에 있는 XMATCH 함수가 먼저 실행됩니다.
셀 범위 [B6:B14]에서 찾는 값 92는 존재하지 않으므로 92보다 작은 90을 찾아서 위치 8을 반환합니다.
XMATCH(92,B6:B14,-1) => 8
② 마지막으로 XMATCH함수의 결과 8을 받아서 INDEX함수는 셀 범위 [$C$6:$C$14]의 8번째 행, 1열에 해당하는 값 A0를 반환합니다.
=INDEX(C6:C14,8,1) => A0
2. 정렬되어 있지 않을 경우에 위치 찾기
아래 그림과 같이 취득점수가 정렬되어 있지 않을 경우에 XMATCH 함수로 값을 위치를 찾아보겠습니다. 기존의 MATCH 함수는 근사값을 찾을 때는 값이 정렬되어 있어야 하지만 XMATCH함수는 정렬되어 있지 않아도 됩니다.
1) 근사값 찾기(일치하거나 작은 항목)
세번째 인수 match_mode를 -1(일치하거나 작은 것을 찾음)로 입력
=XMATCH(92,$B$19:$B$27,-1) => 6
셀 범위 [$B$19:$B$27]에서 찾는 값 92는 존재하지 않으므로 92보다 작은 90을 찾아서 위치 6을 반환합니다. MATCH함수와 달리 XMATCH함수는 취득점수가 정렬되어 있지 않아도 위치를 찾아줍니다.
※ 자료가 정렬되어 있지 않으면 다음과 같이 MATCH 함수는 위치를 제대로 찾지 못합니다.
=MATCH(92,$B$19:$B$27,1) => 9 (잘못된 위치를 찾음, 9번 위치는 80~84 점수 구간임)
2) 근사값 찾기(이진검색)
세 번째 인수 match_mode를 -1(일치하거나 작은 항목을 찾음),
네 번째 인수 search_mode를 2로(오름차순으로 정렬된 lookup_array를 이진 검색) 입력
=XMATCH(92,$B$19:$B$27,-1,2) => 9
셀 범위 [$B$19:$B$27]가 정렬되어 있지 않으므로 잘못된 위치인 9가 반환됩니다. search_mode를 이진검색모드로 설정하면 반드시 범위가 정렬되어 있어야 합니다.
3. 텍스트에서 와일드카드 검색(유사한 값 찾기)
아래 그림과 같이 판매실적 자료가 있을 때 XMATCH 함수의 와일드카드 검색 기능으로 유사한 품목값을 찾을 수 있습니다.
1) 특정문자로 시작하는 값찾기(와일드카드 검색, '*' 문자 이용)
첫번째 인수를 '보드*'로 입력하고 세번째 인수 match_mode를 2(와일드카드검색)로 입력
=XMATCH("보드*",$B$32:$B$40,2) => 7
셀 범위 [$B$32:$B$40]에서 '보드'로 시작하는 품목 '보드마카 청색'의 위치 7을 구해줍니다.
2) 특정문자로 시작하는 값찾기(와일드카드 검색, '?' 문자 이용)
첫번째 인수를 '모나미???'로 입력하고 세번째 인수 match_mode를 2(와일드카드검색)로 입력
=XMATCH("모나미???",$B$32:$B$40,2) => 5
셀 범위 [$B$32:$B$40]에서 '모나미'로 시작하고 뒤의 문자가 3개(전체 6자리 텍스트) 인 '모나미형광펜'의 위치 5를 구해줍니다.
4. 역방향 검색(마지막 항목부터 찾기)
아래 그림과 같이 판매실적 자료가 있을 때 XMATCH 함수의 역방향 검색기능을 사용해서 가장 마지막 항목부터 값을 찾을 수 있습니다.
1) 가장 마지막 행에 있는 '모나미볼펜'의 위치 찾기
첫 번째 인수를 '모나미볼펜'으로 입력하고
세 번째 인수 match_mode를 0(일치하는 것을 검색),
네 번째 인수 search_mode를 -1(마지막 항목부터 역방향 검색)로 입력
=XMATCH("모나미볼펜",$B$32:$B$40,0,-1) => 8
[$B$32:$B$40] 범위에서 '모나미볼펜'을 마지막부터 찾아보면 2, 4, 6, 8번째에 각각 있지만 가장 마지막 항목은 8번째에 있으므로 위치 8을 구해줍니다.
2) 역방향 검색 기능 응용(마지막 판매일자 찾기)
위에서 예를 든 역방향 검색 기능을 이용하여 다음과 같이 특정 품목이 가장 최근(마지막)에 판매된 날짜를 찾을 수 있습니다.
=INDEX($C$32:$C$40,XMATCH("모나미볼펜",$B$32:$B$40,0,-1),1) => '2022-03-04'
이 수식은 중첩되어 있으므로 안쪽에 있는 XMATCH 함수가 먼저 실행되어 행의 위치 8을 반환하고, INDEX함수는 판매일 범위의 8번째 행, 1열에 해당하는 값 '2022-03-04'를 반환합니다.
[ 찾기 및 참조영역 함수 ]
- EXPAND 함수 – 범위 확장하기
- TOCOL 함수 – 범위를 하나의 열로 만들기
- TOROW 함수 – 범위를 하나의 행으로 만들기
- WRAPCOLS 함수 – 열 단위로 범위 채우기
- WRAPROWS 함수 – 행 단위로 범위 채우기
- 여러 열에서 중복 제거하기
- 판매실적 합치고 열 순서 바꾸기
- IMAGE 함수 – 셀에 이미지 삽입하기
- GROUPBY 함수 - 집계, 정렬, 필터링
- GROUPBY 함수로 한 번에 월별 합계 구하기
- DROP 함수 – 범위 제외하고 가져오기
- TAKE 함수 – 범위 가져오기
- CHOOSEROWS 함수 – 범위에서 행 가져오기
- CHOOSECOLS 함수 – 범위에서 열 가져오기
- HSTACK 함수 – 배열을 수평으로 합치기
- VSTACK 함수 – 배열을 수직으로 합치기
- 찾기 및 참조영역 함수 알아보기
- 행과 열 조건을 만족하는 값 찾기
- 엑셀에서 대소문자 구분해서 찾기
- VLOOKUP 함수 - 범위에서 값 찾기
- OFFSET 함수로 월별 누계 구하기
- UNIQUE 함수 - 중복 제거하기
- FORMULATEXT 함수 - 수식을 텍스트로 표시
- XMATCH 함수 사용법
- FILTER 함수 - 원하는 조건으로 필터링하기
- SORTBY 함수 - 범위의 값을 기준으로 데이터 정렬하기
- SORT 함수 - 데이터 정렬하기
- XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결)
- HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기
- HYPERLINK 함수 - 하이퍼링크 만들기
- ROWS 함수 - 행의 개수 구하기
- COLUMNS 함수 - 열의 개수 구하기
- AREAS 함수 - 참조영역내의 영역의 개수 구하기
- ADDRESS 함수 - 행,열 번호로 셀주소 표시하기
- TRANSPOSE 함수 - 행과 열을 바꾸기
- INDIRECT 함수 – 문자열을 참조로 바꾸기
- LOOKUP 함수 - 범위에서 값 찾기
- OFFSET 함수 - 행과 열 이동 후 참조구하기
- COLUMN 함수 - 열 번호 구하기
- ROW 함수 - 행 번호 구하기
- CHOOSE 함수 - 값목록에서 원하는 값 선택하기
- MATCH 함수 - 범위에서 값의 위치 찾기
- INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기
- VLOOKUP 함수 - 표에서 값 찾기(간단 버전)
- VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기
- INDEX, MATCH 함수를 이용한 다중조건으로 값찾기