엑셀 MATCH 함수 사용법에 대해서 알아보겠습니다. MATCH 함수는 찾을 값이 지정한 범위 안에서 상대적인 위치를 반환해주는 함수입니다. 오늘은 MATCH 함수의 단일, 다중 조건일 때 사용법과 열과 행의 위치에 따른 사용법을 자세히 알아보겠습니다.
1. MATCH 함수
MATCH 함수는 단독으로 사용하기 보다는 INDEX 함수의 좌푯값으로 사용되어 데이터를 호출하는데 주로 사용됩니다. MATCH 함수로 상대적인 위치를 반환하고 이렇게 반환된 행과 열의 상대적 위치를 통해 INDEX 함수로 데이터를 호출하게 됩니다. MATCH 함수의 구성은 다음과 같습니다.
=MATCH ( 찾을 값, $범위, 일치 옵션)
- 찾을 값 : 찾을 값을 입력하거나 해당하는 셀을 지정합니다. 다중 조건을 찾기 위해 논리값 1을 사용하기도 합니다.
- 범위 : 찾을 값이 위치한 데이터 범위를 절대 참조로 지정합니다. 다중 조건을 검색하기 위해서 논리식을 사용할 수 있습니다.
- 일치 옵션 :
① 1 : 찾을 값과 일치하거나 작은 값(이하)을 찾으며 데이터는 오름차순으로 정렬되어야 함
② 0 : 찾을 값과 정확히 일치하는 것을 찾으며 데이터 정렬 상태와 상관없이 작동함
③ -1 : 찾을 값과 일치하거나 큰 값(이상)을 찾으며 데이터는 내림차순으로 정렬되어야 함
일치 옵션에 이상, 이하의 조건은 숫자 데이터를 비교할 때 사용하며 일반적으로 정확히 일치하는 값(0)을 주로 사용합니다.
2. MATCH 함수 사용 예시
1) 이하 근사값 찾기
MATCH 함수의 일치 옵션이 1(이하)일 때 사용예시를 알아보겠습니다. 데이터는 오름차순으로 정렬돼있어야 수식이 작동합니다. 아래와 같이 33이라는 숫자와 같거나 작은 값을 찾기 위해 수식을 작성하고 일치 옵션을 “1”로 지정하면 30이 위치해 있는 상대적 행 위치 3을 반환합니다. 만약 오름차순으로 데이터가 작성되지 않으면 N/A라는 오류를 반환하게 되므로 데이터 정렬 상태를 확인하고 작업하시기 바랍니다. 데이터 범위는 고정하기 위해 절대 참조 형식으로 입력하시기 바랍니다. 절대 참조는 데이터 영역을 선택하고 F4키를 눌러 행과 열에 해당하는 값에 “$” 가 모두 입력된 상태를 말합니다.
2) 이상 근사값 찾기
이제 일치 옵션이 -1(이상)일 때 찾는 값은 똑같이 33이라는 조건으로 찾아보겠습니다. 33 보다 크거나 같은 값에서 가장 가까운 값은 40입니다. 40이라는 숫자의 상대적 행 위치인 7을 반환하게 됩니다. 여기서 데이터는 내림차순으로 정렬되어야 오류 없이 작동하게 됩니다.
상대적인 위치는 내가 선택한 영역에서 행일 때 맨 위쪽이 1이 되며, 열일 때 맨 좌측이 1이 됩니다. 엑셀의 기본 위치와 혼동하기 쉬운 부분이니 정확히 이해하시고 넘어가시기 바랍니다.
3) 다중 조건일 때 일치하는 값 찾기
다중 조건일 때 데이터의 상대적 위치를 찾는 방법을 알아보겠습니다. 우선 MATCH 함수의 찾을 값은 “1”로 지정합니다. 범위에서 조건과 범위를 비교하여 논리곱을 통해 결괏값이 참(TRUE)이 나오는 위치를 찾게 됩니다. 비교 옵션은 정확히 일치하는 값인 0을 사용합니다. 수식의 구성은 다음과 같습니다.
=MATCH(1,(조건1=조건범위1)*(조건2=조건범위2)*(조건3=조건범위3),0)
아래 그림은 브랜드, 메뉴, 사이즈가 일치하는 값을 찾는 예시입니다. 수식에서 반환하는 값은 범위 내에서 상대적인 행 위치를 나타냅니다. 찾는 값이 1(TRUE)이며, 조건과 조건 범위가 연산을 통해 1(TRUE)이 반환되는 행의 위치를 호출하게 됩니다. 위에서 사용한 조건은 3개이지만 용도에 따라 조건을 줄이거나 늘려서 사용할 수 있습니다.
4) 열 방향 데이터 위치 찾기
위에서 다룬 데이터는 주로 사용하게 되는 행 방향에 맞춘 수식입니다. 우리가 일상적으로 사용하는 표는 맨 상단에 목록이 구성되며 행 방향으로 데이터를 작성하게 됩니다. 하지만 범위에 해당하는 값을 열 방향의 데이터로 지정하게 되면 열의 위치로 파악할 수 있습니다.
아래 그림처럼 “브랜드” “메뉴” “사이즈”라는 목록이 있을 때 각각의 상대적인 열 위치를 알아보겠습니다. 우선 MATCH 함수에서 찾을 값을 지정합니다. 직접 입력해도 되지만 여기서는 E4 ~ E6에 입력된 데이터를 사용하겠습니다. 이제 범위를 $A$3:$C$3을 절대 참조 형식으로 선택합니다. 일치 옵션은 정확히 일치를 선택하면 조건과 일치하는 데이터의 열의 위치를 확인할 수 있습니다.
마치며
오늘은 INDEX 함수와 같이 사용하게 되는 MATCH 함수에 대해서 자세히 알아보았습니다. 오늘 내용을 간단히 정리하겠습니다.
① MATCH 함수는 범위 안에서 찾을 값의 상대적인 위치를 알려준다.
② MATCH 함수는 범위에 따라 행 또는 열의 위치를 알려준다.
③ 일치 옵션을 사용하기 위해서는 데이터의 정렬방식을 확인해야 한다.
④ 논리 연산을 통해 다중 조건에 만족하는 값을 찾을 수 있다.
⑤ MATCH 함수로 호출된 열, 행 번호는 INDEX 함수에 사용할 수 있다.