엑셀 INDEX, MATCH 함수 응용 - VLOOKUP 효과 내기

 

앞서 INDEX, MATCH, VLOOKUP 함수를 각각 확인하신 분들은 문득 깨달으셨을 겁니다.

INDEX와 MATCH 함수를 이용해서 VLOOKUP 함수 효과를 낼수 있겠다는 것을...

 

해서 잠시 쉬어가는 타이밍으로, INDEX와 MATCH 함수를 이용해서 VLOOKUP 함수로 응용하고 계속 나가도록 하겠습니다.

 

예제 : A회사는 '15년도 신입사원 채용을 진행하였고, 최종 합격자 3명이 선별되었다.

하지만 인사담당자의 실수로 DATA가 일부 지워졌으니, 기존 BACKUP-DATA를 이용해서 최종 합격자의 명단에서 지워진 DATA를 채워넣으려 한다.

 

 

 

좌측 "면접자 점수"가 BACKUP-DATA"이며 우측의 "최종 합격자" 면단에 노란색 음영처리된 부분을 채워야 합니다.

아마 바로 떠오르는 함수는 VLOOKUP 이겠지만, 이번엔 INDEX와 MATCH 함수를 사용해서 찾아보도록 하겠습니다.

 

INDEX 함수에 들어갈 3가지 항목은 array(검색영역), row_num(행) ,column_num(열) 입니다.

수식으로 전환하여, 수험번호 100178의 성명을 알아내려면 =INDEX(C3:C9,1,1)로 작성하면 결과값은 "박민구"가 나올 것입니다.

 

 

물론, 위와 같이 DATA의 양이 얼마 되지 않아 100178의 위치를 눈으로 파악이 가능하기 때문에 가능한 것이고, 만약 행X열이 3,000X10,000과 같이 방대한 DATA에서는 저렇게 눈으로 찾아서 하는 건 거의 불가능하겠죠?

 

해서 우리가 눈으로 바로 확인했던 행/열 위치를, 엑셀이라는 프로그램에게 MATCH 함수를 이용해서 "매칭/검색"토록 시키는 겁니다.

이때, VLOOKUP 함수때와 마찬가지로 "공통분모(비교/기준값)"를 찾아야 합니다.

위 자료에서는 '수험번호'가 양쪽 DATA를 비교할수 있는 유일한 비교/기준값이 됩니다. 즉, '수험번호'를 기준으로 나머지 DATA들을 검색/매칭해서 가져오게 하는 것입니다.

 

자, 그러기 위해서 MATCH 함수를 INDEX 함수 안으로 넣어보도록 하겠습니다.

INDEX 함수의 영역 설정도 수정이 필요하겠습니다.

우선 MATCH 함수에서 수험번호를 기준으로 100178을 매칭/검색하여 좌표를 확인하게끔 하고,

그 좌표를 기준으로 INDEX 함수로 이름을 알아내게 하는 겁니다.

 

 

 

수식을 정리하면, =INDEX(B3:C9,MATCH(I3,B3:B9,0),2) 와 같이 됩니다.

MATCH(I3,B3:B9,0) 의 결과값으로 1 을 찾게 됩니다.

> I3(수험번호 100178)을 기준값으로 B3:B9 영역에서 몇번째 위치해 있는지 찾아라.

이렇게 찾게된 결과값 1은 =INDEX(B3:C9,1,2)로 이어져서

> B3:C9 영역에서 위에서 1번째, 좌측에서 2번째 값을 나타내라. (2는 검색영역 가장 최측에서부터 2번째라는 의미입니다. - INDEX 함수에서 설명)

최종 결과값 "박민구"를 표시하게 됩니다.

 

참고로 100178 면접자의 거주지를 찾기 위함 함수는 =INDEX(B3:D9,MATCH(I3,B3:B9,0),3) 입니다.

> 차이점은 INDEX 함수에서의 검색영역과 좌측에서 몇번째에 해당 DATA가 있느냐 겠죠?

 

그럼 VLOOKUP 합수와 비교해보겠습니다. (100178 면접자의 성명찾기)

ㅁ INDEX와 MATCH 함수 활용 : =INDEX(B3:C9,MATCH(I3,B3:B9,0),2)

ㅁ VLOOKUP 함수 이용 : =VLOOKUP(I3,B3:C9,2,0)

 

조금만 눈썰미가 있으신 분들이라면 비슷한 부분들이나 의미들을 찾을 수 있으실 것 같습니다.