엑셀 index, match 함수

직장인들이 가장 많이 쓰는 함수 중 하나인 vlookup에 대해 지난 시간에 올린 적이 있다. vlookup은 조건에 맞는 특정값을 찾아 출력하는 함수입니다. 사용하는 빈도수도 높고 편리한 함수이지만 치명적인 오류가 있다. 모든 데이터가 vlookup에 딱 들어맞으면 좋겠지만 그렇지 않은 경우도 대다수이죠.

 

데이터의 중간에서 값을 출력해야 하는 일도 부지기수인데, 그럴 때는 match 함수와 index 함수를 혼합하여 함수를 구성하면 아주 간단하게 해결됩니다. 그럼 match함수와 index함수를 하나씩 알아가보도록 하겠습니다.

MATCH 함수 정의

특정 범위, 배열에서 기준값이 위치하고 있는 행과 열 번호 반환하는 함수입니다. 범위를 열로 설정하느냐 행으로 설정하느냐에 따라 행,열 번호가 반환되죠. 수식은 다음과 같이 나타냅니다.
=match( 기준값, 값을 찾을 범위, 일치형태 )
값을 찾을 범위는 행, 열로 범위를 설정합니다. 행과 열 전체를 범위로 설정해도 되고, 아래와 같이 B열 일부를 범위로 지정해도 무방하죠. 일치형태는 1,0,-1로 쓸 수 있습니다.

 

1은 찾을 값이 작거나 같은 값 중에서 최대값을 찾는 옵션으로 범위가 오름차순으로 정렬되어 있어야 합니다. 0은 찾을 값과 같은 첫째 값을 찾는 옵션으로 범위의 정렬순서가 따로 정해져 있지 않아도 상관없습니다. -1은 찾을 값이 크거나 같은 값 중 가장 작은 값을 찾는 옵션으로 범위가 내림차순으로 정렬되어 있어야 합니다. 보통 일치하는 데이터를 찾는 경우가 대부분이므로 0을 주로 사용한다는 점을 기억하시면 좋을 것 같습니다.

 

주의하실 점은 찾을 데이터 범위가 셀병합으로 인해 육안으로는 J2:L3이지만 이렇게 범위를 지정하게 되면 함수가 제대로 된 값을 불러오지 못하기 때문에 범위가 하나의 행 데이터 범위로 제한되도록 J2:L2가 되도록 해야 한다는 점입니다.범위는 되도록이면 F4키를 눌러서 절대참조로 바꿔주세요. 절대참조로 해두시면 수식복사나 자동드래그를 사용하실 때 범위가 바뀌지 않아 수정할 필요가 없죠.

INDEX 함수 정의

지정한 범위에서 특정한 행,열에 위치한 조건값을 반환하는 함수입니다. 수식은 다음과 같이 나타냅니다.
=index( 참조할 범위, 행 번호, 열번호 )
참조할 범위에서 해당되는 행의 번호에서 열 번호에 위치한 값을 반환하라는 뜻이죠. 만약 유재석씨의 급여를 구하려고 한다면, 참조할 범위는 J4:L9가 되고 행 번호는 match함수로 값을 나타낸 D4행, 열 번호는 F4열이 됩니다. 이렇게 수식을 적으면 해당하는 6,200,000원이 급여가 되겠습니다.

이상으로 match, index 함수 사용법에 대해 간단히 알려드렸습니다. 처음에 언급했다시피 vlookup 함수처럼 데이터범위 설정에 제약이 없으므로 match, index 함수는 다양한 데이터에 적용이 가능합니다. 두 가지 함수를 조합해서 사용하는 게 처음에는 어려울 수도 있습니다.

 

그러나 엑셀은 기본 함수를 어느정도 알면 그 다음은 조합하고 응용하는 영역이기 때문에 이것저것 고민해보고 많이 사용해 보는 것이 중요하다고 생각합니다. 데이터에서 조건에 맞는 특정값을 찾는데는 이만한 함수가 없을 듯 합니다. 엑셀 초보를 벗어나기 위에서는 반복연습으로 인한 숙련자가 되는것이 지름길입니다.