자동으로 교차값을 찾아주는 INDEX, MATCH 함수(매우 중요)

이번 포스팅에서는 자동으로 교차값을 찾아주는 INDEX, MATCH 함수에 대해 작성하겠습니다. 엑셀에서 기준에 적합한 행/열의 교차값을 찾는 경우가 생각보다 자주 있습니다. 개별로 사용할 경우에는 큰 메리트가 없지만 함께 사용할 경우에는 활용도가 매우 높습니다. 무조건 알고 계시는 것이 좋습니다.

 

 

INDEX 함수 기본 사용법

이 함수는 행과 열이 교차되는 지점의 값을 출력해주는 함수입니다. 따라서 두 가지 이상의 조건에 일치하는 값을 찾을 수 있습니다.

• 함수 구성 '=INDEX(참조 범위, 행 번호, 열 번호)'
• 만약 'D1:H6'범위에서 '대리'이면서 평가가 'A'일 때의 인센티브를 구한다고 한다면 B3셀에 '=INDEX(E2:H6,2,2)'를 입력합니다.

B3셀에 '=INDEX(E2:H6,2,2)'를 입력하면 인센티브 값을 가져올 수 있습니다.

다만, 행번호와 열번호를 내가 직접 지정해야 하기에 불편하고 효율적이지 않습니다.

 

 

MATCH 함수 기본 사용법

이 함수는 지정 범위에서 찾을 값을 몇 번째 있는지 찾아주는 함수입니다. 그렇기에 INDEX 형/열번호에 대신 사용할 수 있습니다.

• 함수 구성 '=MATCH(찾을값, 찾을범위, 찾기옵션)'
  찾기 옵션 0 : 일치하는 값
  찾기 옵션 -1 : 내림차순으로 정렬된 범위 중 찾을 값보다 큰 범위 안에서 가장 작은값
  찾기 옵션 1 or 생략 : 오름차순으로 정렬된 범위 중 찾을 값보다 작은 범위 안에서 가장 큰 값
  
  
• 만약 'D1:H6'범위에서,
  '대리'의 위치 값을 찾는다면 '=MATCH(A2,D2:D6,0)'을 입력합니다. 두번째에 위치해 있어 '2'가 출력되었습니다.
  'A'의 위치 값을 찾는다면 '=MATCH(A3,E1:H1,0)'을 입력합니다. 두번째에 위치해 있어 '2'가 출력되었습니다.

B2셀에 '2', B3셀에 '2'가 출력되었습니다

 

 

INDEX / MATCH 함수로 데이터 찾기(매우 중요)

각 함수를 가끔 따로 사용할 때가 있지만 대부분 두 함수는 같이 사용을 합니다. 그리고 사용빈도도 엄청 높습니다.

• 만약 N2셀에 두 조건(직급(L열), 평가(M열))에 따른 인센티브를 표(D1:H6)에서 가져온다고 할 경우,
  N2셀에 '=INDEX($E$2:$H$6, MATCH($L2,$D$2:$D$6,0), MATCH($M2,$E$1:$H$1,0))'을 입력합니다.
  - '$E$2:$H$6' : 값(인센티브)을 찾을 범위 입니다.
  - 'MATCH($L2,$D$2:$D$6,0)' : 과장($L2)직급의 위치를 표($D$2:$D$6)에서 찾으면 '3'이 구해집니다.
  - 'MATCH($M2,$E$1:$H$1,0)' : S($M2)평가의 위치를 표($E$1:$H$1)에서 찾으면 '1'이 구해집니다.
  - 따라서 N2셀에는 인센티브 표(D1:H6)의 3행, 1열의 값인 2,500,000원이 출력이 됩니다.
  - 참고로 참조범위는 모두 절대참조를 해야 합니다. 절대참조를 하지 않을 경우 자동 채우기 핸들 시 범위가 변경되어 '값 없음(#N/A)'이 출력됩니다.

N2셀에 인센티브가 출력되었습니다

 

이상 자동으로 교차값을 찾아주는 INDEX, MATCH 함수(매우 중요) 그리고 두 함수를 함께 사용하는 방법에 대해 알아보았습니다.

감사합니다.