XMATCH 함수 사용법

엑셀의 XMATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환합니다.

이 함수는 용도가 비슷한 MATCH 함수 기능에 다음과 같은 유용한 기능이 추가되었습니다.

• MATCH 함수는 근사값을 찾을 때는 값이 정렬되어 있어야 하지만 XMATCH 함수는 정렬되어 있지 않아도 값을 찾을 수 있다.
• 빠른 검색을 위해 이진검색모드를 따로 지정할 수 있다. 단, 이진검색을 사용할 때는 범위가 정렬되어 있어야 한다.
• 와일드카드 검색(*,?)을 지원한다. '모나미*'로 찾으면 '모나미'로 시작하는 '모나미볼펜' , '모나미문구' 등을 찾을 수 있다.
• 역방향 검색을 지원한다. 배열 또는 범위의 마지막 항목부터 첫번째 항목으로 역방향으로 값을 찾을 수 있다.

MATCH 함수와 마찬가지지로 XMATCH 함수도 단순히 위치만 찾아 주므로 위치만 가지고 쓸 일이 많이 없습니다. INDEX 함수 등 다른 함수와 함께 사용해서 성적 등급을 계산하는 등 응용해서 많이 사용되므로 함수의 사용법을 잘 익혀 두면 복잡한 문제를 처리할 때 많은 도움이 됩니다.

※ XMATCH함수는 Microsoft 365, 엑셀 2021 이상 버전에서만 사용할 수 있습니다.

 

구문(Syntax)

(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'를 반환합니다.

 

 

[ 찾기 및 참조영역 함수 ]