엑셀데이터 조회 INDEX MATCH 함수 사용방법

엑셀데이터 조회 INDEX MATCH 함수 사용방법

지난 편에 VLOOKUP을 활용한 함수에 대해 알아보았는데요.

VLOOKUP 함수는 키워드가 된 기준열의 우측열만 데이터 조회가 가능하였는데,

INDEX 와 MATCH 함수를 사용하면 다른열도 가능하다고 말씀드렸습니다.

오늘은 INDEX와 MATCH 사용법에 대해 알아보려 합니다.

​

VLOOKUP 함수 설명시 사용했던 그림인데

빨간색 사각형 부분이 INDEX와 MATCH 을 통해 함수를 작성해둔 부분입니다.

내용은 내선전화 부분을 드롭다운하여 선택하면 해당 번호의 이름이 출력되도록 만들었습니다. 

VLOOKUP 같은 경우에 내선전화 항목이 성명 부분 보다 우측에 있기 때문에 VLOOKUP을 사용해서 조회할 수가 없습니다. 

 

728x90

일단은 MATCH함수 사용법부터 알아보겠습니다.

MATCH 함수는 조건(키워드)가 들어간 위치정보(행 또는 열의 순번)를 지정한 결과로 출력합니다.


결과물의 위치로 출력되는 순서는

선택한 가장 상단행이 1행, 다음 2행 순이며,

선택한 범위의 가장 좌측열이 1열, 2열 순으로 진행됩니다.

​

그러므로 선택한 범위내에서 507의 위치는 E6:E10 까지 조회했을 경우는 3번째에 위치하며,

B8:F8 까지 조회했을 경우 4번째에 위치합니다.

방향이나 지정영역에 따라 순번 정보는 바뀔 수 있습니다.

​

그럼 함수를 직접작성하여 결과를 볼까요?

MATCH 함수는 다음과 같이 작성합니다.

​

일단 내선전화 열의 선택 영역으로 조회해 보겠습니다.

​

키워드 ' 507 ' 로 E열 6행 부터 10행 까지 선택한 범위내에서 507의 위치는 3행으로서 결과값이 3이 출력됩니다.

​

만약 행으로 길게 선택영역이 바뀐다면 선택영역에 따라 결과값이 바뀝니다.

해볼까요?

B8:F8 행으로 선택영역을 조회하게 되면, 507은 선택한 부분으로 부터 4번째 열에 위치하므로 결과값은 4를 반환합니다.

​
이제 우리는 MATCH 함수가 선택한 영역내의 위치정보(행또는 열의 순서)를 결과값으로 반환한다는 것을 알게 되었습니다. 

​

 

*MATCH 함수는 다음과 같이 사용하기도 합니다.

=MATCH(선택 범위, 위치정보, 0)

세번째 '0'은 MATCHTYPE 부분인데 이부분을 생략하여도 함수 결과값은 제출 됩니다. 

​

​MATCHTYPE 은 

다음으로 INDEX 함수를 알아 보겠습니다.

INDEX 함수의 사용법은 다음과 같습니다.

함수는 선택한 영역에서 해당 순번(위치)의 데이터를 출력하게 됩니다.

이 순번(위치정보)은 MATCH 함수 선택 영역의 행열 계산과 마찬가지로 선택 영역에 따라 달라집니다.

예시를 통해 보겠습니다. 

​

C열 6행 부터 10행까지 선택 범위 내에서 3번 째 순번의 데이터를 출력하라 

함수는

=INDEX(C6:C10, 3) 결과 값은 마이클이 됩니다.


​

C열 8행 부터 F열 8행까지 선택 범위 내에서 1번째 순번의 데이터를 출력하라


=INDEX(C8:F8,1) 결과 값은 마이클 이 됩니다.


C6:F10 박스로 선택된 범위 내에서 3행 2열에 있는 데이터를 출력하라

=INDEX(B6:F10, 3,2) 결과 값은 마이클이 됩니다.

MATCH 함수와 마찬가지로 선택영역에 따라 데이터 위치가 달라짐을 알 수 있습니다.

​

그럼 INDEX 함수는 위치정보를 통해 값을 출력하는데 MATCH 함수는 위치정보를 제공하므로 

함께 사용할 수가 있습니다.

그림 13을 보시면 INDEX 함수안에 위치정보를 표시할 곳에 MATCH 함수를 통해 값을 도출 하고 있습니다 .

=INDEX(C6:C10, MATCH(F2, E6:E10))

C열 6행 부터 C열 10행까지, E열 6행 부터 E열 10행사이에서 F2(505) 데이터가 있는 순번( 1번 ) 의 데이터를 출력하라

C열 6행에서 C열 10행까지 첫번째 순번은 브라운 입니다. 

결과값은 브라운이 출력됩니다.

위와 같이 INDEX와 MATCH 함수를 함께 사용할 수 있습니다.
​

또한 지난편에서 설명한 드롭다운을 사용하면 

그림의 빨간색 박스처럼 내선번호 목록을 조회하여 클릭하면 

해당 성명이 출력하도록 설정 할 수도 있습니다. 

이에 INDEX, MATCH 함수까지 설명하였습니다.