VLOOKUP으로 안될때 INDEX,MATCH 사용하기

공개됨 글쓴이 Admin댓글 5개

VLOOKUP함수로 값을 찾을 수 없을 때 INDEX,MATCH를 많이 사용하는데 그 전에 VLOOKUP에 대해 살펴보자.

아래와 같이 “출근기록”이 있는데 사번과 출근일시만 있고 이름과 부서는 비어 있다.

                                    출근기록

 

별도로 관리하고 있는 “사원정보” 시트에는 사번별로 부서와 이름이 있다.

                             사원정보

 

이 때 다음과 같이 VLOOKUP함수를 이용하면 사번에 해당하는 이름과 부서를 출근기록 시트에 가져올 수 있다.

=VLOOKUP(A2,사원정보!$A$2:$C$15,3,FALSE)

 

그런데 아래 처럼  “사원정보” 시트의 “사번” 이 첫번째 열이 아니라… C열에 있다면 ???

VLOOKUP함수로 값을 찾을 수 없다. 

VLOOKUP함수는

  • 키값(사번)이 찾는 범위에서 항상 첫번째 열에 있어야 하고
  • 키 값에 딸린 값들(부서,이름,급여)이 키값의 다음열에 있어야 작동하기 때문이다. 

 

위와 같이 사원정보에서 사번이 C열에 있을 때 출근기록 시트에 이름을 가져오기 위해서는 다음과 같이 INDEX,MATCH를 이용한 수식을 C2셀에 입력한다.

=INDEX(사원정보!$A$2:$D$11,MATCH(출근기록!A2,사원정보!$C$2:$C$11,0),2)

 

수식이 정상적으로 입력되었다면 사번이 102인 직원의 이름 “박영업”이 구해진다.

 

위의 복잡한 수식을 이해하기 위해 INDEX함수와 MATCH함수를 각각 살펴보자.

INDEX함수는 값을 찾을 범위에서 행번호와 열번호에 해당되는 값을 가져온다.
엑셀 INDEX 함수 구문

 

INDEX함수는 어떤 범위에서 몇번째 행인지, 몇번째 열인지를 알면 값을 가져오므로
아래 그림과 같이 사원정보!$A$2:$D$11 범위의 2행, 2열에서 “박영업”을 구해준다.

수식을 입력할 때 “이름”이 있는 열의 위치는 2열로 정해져 있으므로 마지막 인수를 2로 입력하면 되지만 행의 위치는 사번에 따라 바뀌므로 “행의 위치를 구해주는 MATCH함수“를 이용해서 구해야 한다.

 

MATCH는 “값을 찾을 범위”에서 찾은 값의 위치를 가져오는데

엑셀 MATCH 함수 구문

다음 그림과 같이 사번이 있는 위치(행번호)를 가져올 수 있다.

위와 같이 MATCH함수가 먼저 실행되면 사번 102가 있는 행위 위치 2가 구해지고 이것을 INDEX함수로 넘겨주면 최종적으로 “박영업”이 구해진다.

이 과정으로 그림으로 정리하면 다음과 같다.

 

지금까지 이름을 가져왔는데 이제 사번 102에 해당하는 부서를 가지고 오자.
다음과 같이 D2셀에 수식을 입력한다.

=INDEX(사원정보!$A$2:$D$11,MATCH(출근기록!A2,사원정보!$C$2:$C$11,0),1)

이름을 가지고 올 때와 수식이 거의 똑같다. 이름을 가지고 올 때는 마지막 인수인 열의 위치가 2였는데 부서를 가지고 올때는 마지막 인수가 1이다.

사원정보 시트에서 부서는 첫번째 열에서 가져와야 하므로 인수를 1을 입력해야 한다.

 

수식이 정상적으로 입력되었다면 사번이 102인 직원의 부서 “영업팀”이 구해진다.

 

이제 마지막으로 C2, D2셀의 수식을 복사하여 C3, D3셀 아래로 복사하여 이름, 부서 찾기를 완료하자.

 

이것으로 “VLOOKUP으로 안될때 INDEX,MATCH 사용하기”에 대한 설명을 마친다.
실제 사용 예는 아래 동영상을 참고하자.

 

INDEX,MATCH함수에 대해 자세한 설명이 필요하면 아래 링크를 따라가서 살펴보자.

[ 엑셀함수 INDEX – 특정 범위에서 행과 열을 이용하여 값 찾기 ]

[ 엑셀함수 MATCH – 특정 범위에서 값의 위치 찾기 ]


 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

 

VLOOKUP으로 안될때 INDEX,MATCH 사용하기에 1개의 응답

  1. 정말로 정말로 감사드립니다…..
    참고로 찾고자하는 셀이 숫자형식으로 안되어있는경우(ex. 한글문서에서 붙여넣기한경우) 오류가 나니, 숫자를 찾고자하는 경우 셀의 서식이 숫자형식인지 반드시 확인해야하는 듯합니다.

댓글 남기기

이메일은 공개되지 않습니다.