Excel MATCH 함수 - 구문 및 사용
Excel의 MATCH 함수는 셀 범위에서 지정된 값을 검색하고 해당 값의 상대 위치를 반환합니다.
MATCH 함수의 구문은 다음과 같습니다.
Lookup_value (필수) - 찾으려는 값입니다. 숫자, 텍스트 또는 논리 값뿐만 아니라 셀 참조가 될 수 있습니다.
Lookup_array (필수) - 검색할 셀 범위입니다.
Match_type (선택 사항) - 일치 유형을 정의합니다. 1, 0, -1 값 중 하나일 수 있습니다. 0으로 설정된 match_type 인수는 정확한 일치만 반환하지만 다른 두 형식은 근사 일치를 허용합니다.
- 1 또는 생략(기본값) - 조회 배열에서 조회 값보다 작거나 같은 가장 큰 값을 찾습니다. 조회 배열을 가장 작은 것에서 가장 큰 것 또는 A에서 Z까지 오름차순으로 정렬해야 합니다.
- 0 - 조회 값과 정확히 동일한 배열의 첫 번째 값을 찾습니다. 정렬이 필요하지 않습니다.
- -1 - 배열에서 조회 값보다 크거나 같은 가장 작은 값을 찾습니다. 조회 배열은 가장 큰 것에서 가장 작은 것 또는 Z에서 A로 내림차순으로 정렬되어야 합니다.
MATCH 함수를 더 잘 이해하기 위해 이 데이터를 기반으로 간단한 공식을 만들어 보겠습니다:
A열의 학생 이름과 B열의 시험 점수를 가장 큰 것부터 가장 작은 것까지 정렬합니다. 특정 학생(예: Laura)이 다른 학생들 사이에서 어디에 있는지 알아보려면 다음과 같은 간단한 공식을 사용하십시오.
=MATCH("Laura", A2:A8, 0)
필요에 따라 조회 값을 일부 셀(이 예에서는 E1)에 넣고 Excel 일치 수식에서 해당 셀을 참조할 수 있습니다.
=MATCH(E1, A2:A8, 0)
위의 스크린샷에서 볼 수 있듯이 학생 이름은 임의의 순서로 입력되므로 이 일치 유형만 조회 배열에서 값을 정렬할 필요가 없기 때문에 match_type 인수를 0(정확히 일치)으로 설정합니다. 기술적으로 Match 수식은 범위 내에서 Laura의 상대적 위치를 반환합니다. 그러나 점수가 가장 큰 것부터 가장 작은 것까지 정렬되기 때문에 Laura가 모든 학생 중에서 5번째로 높은 점수를 받았다는 것도 알려줍니다.
MATCH 기능에 대해 알아야 할 4가지 사항
Excel에서 MATCH 함수에 대해 알아야 할 몇 가지 특이성이 있습니다.
- - MATCH 함수는 값 자체가 아니라 배열에서 조회 값의 상대적 위치를 반환합니다.
- - MATCH는 대/소문자를 구분하지 않으므로 텍스트 값을 처리할 때 소문자와 대문자를 구분하지 않습니다.
- - 조회 배열에 조회 값이 여러 번 있는 경우 첫 번째 값의 위치가 반환됩니다.
- - 조회 배열에서 조회 값을 찾을 수 없으면 #N/A 오류가 반환됩니다.
Excel에서 MATCH를 사용하는 방법 - 수식 예제
와일드카드와의 부분 일치
다른 많은 함수와 마찬가지로 MATCH는 다음과 같은 와일드카드 문자를 인식합니다.
- 물음표(?) - 단일 문자를 대체합니다.
- 별표(*) - 모든 문자 시퀀스를 대체합니다.
와일드카드는 match_type 0으로 설정된 일치 수식에만 사용할 수 있습니다.
와일드카드가 있는 일치 수식은 전체 텍스트 문자열이 아니라 일부 문자 또는 문자열의 일부만 일치시키려는 경우에 유용합니다. 요점을 설명하기 위해 다음 예제를 고려하십시오.
지난 달의 지역 리셀러 목록과 판매 수치가 있다고 가정합니다. 목록에서 특정 리셀러의 상대적 위치(판매 금액별로 내림차순으로 정렬)를 찾고 싶지만 첫 글자 몇 개는 기억하지만 그의 이름은 정확히 기억할 수 없습니다.
리셀러 이름이 A2:A11 범위에 있고 "car"로 시작하는 이름을 검색한다고 가정하면 수식은 다음과 같습니다.
=MATCH("car*", A2:A11,0)
일치 수식을 보다 다양하게 만들려면 일부 셀(이 예에서는 E1)에 조회 값을 입력하고 다음과 같이 해당 셀을 와일드카드 문자와 연결할 수 있습니다.
=MATCH(E1&"*", A2:A11,0)
아래 스크린샷에서 볼 수 있듯이 수식은 "Carter"의 위치인 2를 반환합니다.
조회 값에서 한 문자만 바꾸려면 다음과 같이 "?" 와일드카드 연산자를 사용합니다.
=MATCH("ba?er", A2:A11,0)
위의 공식은 "Baker"라는 이름과 일치하고 상대 위치인 5를 다시 실행합니다.
대/소문자 구분 MATCH 수식
이 자습서의 시작 부분에서 언급했듯이 MATCH 함수는 대문자와 소문자를 구분하지 않습니다. 대/소문자를 구분하는 Match 수식을 만들려면 대/소문자를 포함하여 셀을 정확하게 비교하는 EXACT 함수와 함께 MATCH를 사용합니다.
다음은 데이터를 일치시키는 일반적인 대/소문자 구분 수식입니다.
수식은 다음 논리와 함께 작동합니다.
- EXACT 함수는 조회 값을 조회 배열의 각 요소와 비교합니다. 비교된 셀이 정확히 같으면 함수는 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.
- 그런 다음 MATCH 함수는 TRUE(lookup_value)를 EXACT에서 반환된 배열의 각 값과 비교하고 첫 번째 일치 항목의 위치를 반환합니다.
Ctrl + Shift + Enter 키를 눌러야 정확하게 완료되는 배열 공식임을 유의하십시오.
조회 값이 E1 셀에 있고 조회 배열이 A2:A9라고 가정하면 수식은 다음과 같습니다.
=MATCH(TRUE, EXACT(A2:A9,E1),0)
다음 스크린샷은 Excel의 대/소문자 구분 일치 수식을 보여줍니다.
일치 및 차이에 대해 2개의 열 비교(ISNA MATCH)
두 목록에서 일치 및 차이점을 확인하는 것은 Excel에서 가장 일반적인 작업 중 하나이며 다양한 방법으로 수행할 수 있습니다.
ISNA/MATCH 공식은 그 중 하나입니다.
목록 2에 없는 목록 1의 값에 대해 수식은 "목록 1에 없음"을 반환합니다. 방법은 다음과 같습니다.
- MATCH 함수는 목록 1 내의 목록 2에서 값을 검색합니다. 값이 발견되면 상대 위치를 반환하고, 그렇지 않으면 #N/A 오류를 반환합니다.
- Excel의 ISNA 함수는 #N/A 오류("사용할 수 없음"을 의미)를 확인하는 한 가지만 수행합니다. 지정된 값이 #N/A 오류인 경우 함수는 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다. 이 경우 TRUE는 목록 1의 값을 목록 2 내에서 찾을 수 없음을 의미합니다(즉, MATCH에서 #N/A 오류가 반환됨).
- 사용자가 목록 1에 표시되지 않는 값에 대해 TRUE를 보는 것은 매우 혼란스러울 수 있으므로 ISNA를 IF 함수로 래핑하여 대신 "목록 1에 없음"을 표시하거나 원하는 텍스트를 표시합니다.
예를 들어 B 열의 값과 A 열의 값을 비교하기 위해 수식의 모양은 다음과 같습니다(여기서 B2는 맨 위 셀임).
=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")
엑셀의 MATCH 기능은 대소문자를 구분하지 않습니다. 문자 대소문자를 구분하려면 Lookup_array 인수에 EXCT 함수를 포함하고 Ctrl + Shift + Enter 키를 눌러 이 배열 공식을 완료해야 합니다:
=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")
다음 스크린샷은 두 수식이 모두 작동하는 것을 보여 줍니다.
Excel에서 두 목록을 비교하는 다른 방법을 배우려면 Excel에서 2 개의 열을 비교하는 방법을 참조하십시오.
Excel VLOOKUP 및 MATCH
VLOOKUP의 가장 성가신 단점 중 하나는 조회 테이블 내에서 열을 삽입하거나 삭제한 후 작동이 중지된다는 것입니다. 이는 VLOOKUP이 지정한 반환 열의 번호(인덱스 번호)에 따라 일치하는 값을 끌어오기 때문에 발생합니다.
수식에서 인덱스 번호가 "하드 코딩"되어 있기 때문에 Excel에서는 테이블에서 새 열을 추가하거나 삭제할 때 인덱스 번호를 조정할 수 없습니다.
Excel MATCH 함수는 조회 값의 상대 위치를 처리하므로 VLOOKUP의 col_index_num 인수에 완벽하게 맞습니다. 즉, 반환 열을 정적 숫자로 지정하는 대신 MATCH를 사용하여 해당 열의 현재 위치를 가져옵니다.
학생들의 시험 점수가 표시된 표를 사용하여 상대적 위치가 아닌 실제 점수를 검색합니다.
룩업 값이 셀 F1에 있다고 가정하면 테이블 배열은 $A$1:$C$2입니다:
공식은 다음과 같습니다:
=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)
3번째 인수(col_index_num)는 가져오려는 수학 점수가 테이블의 3번째 열이기 때문에 3으로 설정됩니다. 아래 스크린 샷에서 볼 수 있듯이이 일반 Vlookup 수식이 잘 작동합니다.
그러나 열을 삽입하거나 삭제할 때까지만 :
col_index_num을 3으로 설정하면 Excel이 세 번째 열에서 값을 가져오라고 하는 반면, 테이블 배열에는 열이 두 개밖에 없습니다.
이러한 일이 발생하지 않도록 하려면 다음 Match 함수를 포함하여 Vlookup 수식을 보다 동적으로 만들 수 있습니다.
MATCH(E2,A1:C1,0)
- E2는 조회 값으로, 반환 열의 이름, 즉 값을 가져오려는 열(이 예에서는 수학 점수)과 정확히 같습니다.
- A1 : C1은 테이블 헤더를 포함하는 조회 배열입니다.
이제 다음과 같이 Vlookup 수식의 col_index_num 인수에 이 Match 함수를 포함합니다.
=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)
그리고 추가하거나 삭제하는 열의 수에 관계없이 완벽하게 작동하는지 확인하십시오.
위의 스크린샷에서 사용자가 수식을 워크시트의 다른 위치로 이동하더라도 수식이 올바르게 작동하도록 모든 셀 참조를 잠갔습니다. 아래 스크린샷에서 볼 수 있듯이 수식은 열을 삭제한 후에도 제대로 작동합니다.
또한 Excel은 이 경우 절대 참조를 적절하게 조정할 수 있을 만큼 똑똑합니다.
Excel HLOOKUP 및 MATCH
비슷한 방식으로 Excel MATCH 함수를 사용하여 HLOOKUP 수식을 향상시킬 수 있습니다.
일반적인 원칙은 기본적으로 Vlookup의 경우와 동일합니다. Match 함수를 사용하여 반환 열의 상대 위치를 가져오고 해당 숫자를 Hlookup 수식의 row_index_num 인수에 제공합니다.
조회 값이 B5 셀에 있고 테이블 배열이 B1 : H3이고 반환 행의 이름 (MATCH에 대한 조회 값)이 A6 셀에 있고 행 머리글이 A1 : A3이라고 가정하면 전체 수식은 다음과 같습니다.
=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)
방금 보았 듯이 Hlookup / Vlookup & Match의 조합은 확실히 일반 Hlookup 및 Vlookup 수식보다 개선되었습니다. 그러나 MATCH 함수가 모든 제한을 제거하지는 않습니다. 특히 Vlookup Match 수식은 여전히 왼쪽을 볼 수 없으며 Hlookup Match는 맨 위에 있는 행 이외의 다른 행에서 검색하지 못합니다.
위의 (및 몇 가지 다른) 제한 사항을 극복하려면 여러 측면에서 Vlookup 및 Hlookup보다 우수한 Excel에서 조회를 수행하는 매우 강력하고 다양한 방법을 제공하는 INDEX MATCH의 조합을 사용하는 것이 좋습니다.
'워드프로세서 > 엑셀' 카테고리의 다른 글
| 엑셀 숫자나 날짜를 이용한 IF문 활용 (0) | 2023.06.12 |
|---|---|
| 엑셀 데이터 막대 만들기 (0) | 2023.06.12 |
| 엑셀 표 스타일 및 서식 설정 (0) | 2023.06.10 |
| 엑셀 원형 그래프(파이 차트) 만들기 (0) | 2023.06.10 |
| 엑셀 막대 그래프 만들기 (0) | 2023.06.10 |