[Excel] INDEX와 MATCH를 사용하여 일치하는 값의 필요한 데이터 가져오기

#238

요즘들어 엑셀을 사용할 일이 부쩍 많아졌습니다.

 

일하면서 거의 사용할 일이 없어서 기능들을 많이 까먹었다보니 기억이 가물가물한 상태에서 사용하려고 하니 약간 헤매는 경우가 있었습니다.

 

이제는 슬슬 기억에만 의존할 나이가 아니라서 사용한 기능마다 정리를 해볼까합니다.

 

1. 사용 사례

하나의 전체 데이터가 있고 그 중 일부 데이터 값을 이용하여 필요한 데이터만 추출해야하는 경우가 있습니다.

이 경우 필터를 이용하여 작업을 할 수도 있지만 다른 데이터와 조인을 하거나 수정이 일어날 경우 원본데이터는 유지한 채 별도의 테이블을 만들어야하는 경우가 있습니다.

 

아래와 같은 경우를 보시면 부서원 정보에서 담당자 현황만 따로 뽑고 싶은 경우입니다.

(물론 이 경우에는 필터를 사용하여 작업이 가능합니다.)

 

담당자 정보만 아는 상태에서 해당 담당자의 부서, 핸드폰, 이메일 정보가 필요합니다.

이 때 INDEX와 MATCH함수를 사용하여 데이터를 추출할 수 있습니다.

 

2. 함수 알아보기

1) INDEX 함수

특정한 범위(행, 열, 테이블 등)에서 행, 열 번호를 통하여 원하는 값을 반환합니다.

INDEX(범위, row_num, [colum_num])

 

위의 데이터를 예를들어 한 번 사용해보도록 하겠습니다.

 

=INDEX(B5:B12,1)

부서 데이터가 있는 컬럼의 값을 범위로 정하고 1번째 행에 있는 값을 가져오는 것으로 함수를 작성하였습니다.

범위에 있는 데이터 중 1번째 행에 있는 값이 A이므로 결과는 A를 반환합니다.

 

2) MATCH 함수

특정한 범위에서 원하는 값의 위치를 반환합니다.

MATCH(원하는 값, 범위, (1,0,-1))

여기서 1,0,-1의 기능은 다음과 같습니다.

1 : 원하는 값보다 같은 값 중 최대값

0 : 원하는 값과 같은 첫째 값

-1 : 원하는 값보다 같은 값 중 최소값

 

마찬가지로 데이터로 예를들어 보겠습니다.

 

=MATCH(C6,C5:C12,0)

팀원 데이터가 있는 컬럼의 값을 범위로 정하고 '이'라는 값이 있는 위치를 반환하도록 작성하였습니다.

그 결과 '이'값이 있는 위치값 2를 반환하였습니다.

 

3. 최종 적용하기

이제 이 두 함수를 이용하여 담당자 현황을 작성해보도록 하겠습니다.

 

함수 사용 방법

=INDEX(출력할 데이터가 있는 범위,MATCH(원하는 값, 원본 데이터에서 원하는 값이 있는 범위,0))

(같은 행이 있다는 전제 하에 작성)

 

1) 부서 정보 작성하기

=INDEX($B$5:$B$12,MATCH(H5,$C$5:$C$12,0))

(범위가 변하지 않는 범위에는 절대값을 지정합니다.)

출력할 데이터가 있는 범위 : 부서원 정보에서 부서 열

원하는 값 : 담당자 현황에서 담당자 값

원본 데이터에서 원하는 값이 있는 범위 부서원 정보에서 팀원 열

 

2) 핸드폰 정보 작성하기

=INDEX($D$5:$D$12,MATCH(H5,$C$5:$C$12,0))

(범위가 변하지 않는 범위에는 절대값을 지정합니다.)

출력할 데이터가 있는 범위 : 부서원 정보에서 핸드폰 열

원하는 값 : 담당자 현황에서 담당자 값

원본 데이터에서 원하는 값이 있는 범위 부서원 정보에서 팀원 열

 

3) 이메일 정보 작성하기

=INDEX($E$5:$E$12,MATCH(H5,$C$5:$C$12,0))

(범위가 변하지 않는 범위에는 절대값을 지정합니다.)

출력할 데이터가 있는 범위 : 부서원 정보에서 이메일 열

원하는 값 : 담당자 현황에서 담당자 값

원본 데이터에서 원하는 값이 있는 범위 부서원 정보에서 팀원 열

이렇게 해서 담당자 정보만을 가지고 원하는 테이블을 작성하였습니다.

 

이번 같은 경우 간단한 작업이였지만 복잡한 경우에는 이 함수들과 다른 함수들을 응용하여 사용해야 원하는 값을 얻을 수 있습니다.

 

이렇게 정리하였으니 절대 까먹지 말아야 겠습니다.