엑셀 INDEX 함수와 MATCH 함수로 VLOOKUP 함수 기능 발휘하기

이전 글에서 VLOOKUP 함수와 HLOOKUP 함수에 대해 알아보았습니다. 그런데, 이 함수를 사용하려면 찾으려는 값이 있는 셀의 위치가 표의 가장 왼쪽 또는 위쪽에 있어야 하는 제약이 있습니다. 그렇지 못한 경우는 표를 수정해야 VLOOKUP 함수와 HLOOKUP 함수를 사용할 수 있습니다. 이럴 때 표의 수정 없이 이를 대치할 수 있는 방법이 있어서 이를 알아보고자 합니다.

 

VLOOKUP함수

VLOOKUP함수

 

HLOOKUP 함수

HLOOKUP 함수

 

 

 

 

1. INDEX 함수

  INDEX 함수는 지정한 셀의 값을 참조하는 함수입니다. 사용 방법은 다음과 같습니다.

 

   INDEX ( Array, Row_num, Column_num )

 

Array : 참조할 값이 포함된 셀 범위

 

Row_num : 참조할 값이 있는 셀의 행 인덱스 값

 

Column_num : 참조할 값이 있는 셀의 열 인덱스 값

 

찾을 값이 있는 셀을 포함하는 데이터 영역을 설정하고 지정한 행과 열의 주소에 있는 셀 값을 참조하여 반환합니다.

 

2. MATCH 함수

  MATCH 함수는 찾으려는 값의 위치를 찾아 해당 값의 인덱스 번호를 반환하는 함수입니다. 사용 방법은 다음과 같습니다.

 

  MATCH ( Lookup Value, Lookup Array, Match Type )

 

Lookup Value : 찾으려는 값

 

Lookup Array : 찾으려는 값을 포함한 데이터 범위

 

Match Type : 1, 0, -1을 입력할 수 있습니다. 생략 가능하며 기본값은 1이 적용됩니다.

  1인 경우 - Lookup Array의 값이 오름차순 정렬이 되어 있어야 하며 Lookup Value보다 큰 값을 만날 때까지 동일한 값을 찾아 결과를 반환하며, 찾지 못한 경우에는 값 들 중에서 가장 큰 값의 위치를 찾아 반환합니다.

 

  0인 경우 - Lookup Array에서 Lookup Value 값이 위치한 첫 번째 위치를 찾아 반환합니다.

 

  -1인 경우 - Lookup Array의 값이 내림차순 정렬이 되어 있어야 하며 Lookup Value보다 작은 값을 만날 때까지 동일한 값을 찾아 결과를 반환하며, 찾지 못한 경우에는 값 들 중에서 가장 작은 값의 위치를 찾아 반환합니다.

 

 

 

 

3. VLOOKUP 함수 대신 사용

  VLOOKUP 함수를 사용한 경우 이를 INDEX 함수와 MATCH 함수로 대치해서 사용 가능합니다. 이렇게 VLOOKUP 함수를 대치하여 사용하는 방법은 다음과 같습니다.

 

   = INDEX( Array, MATCH( Lookup Value, Lookup Array, Match Type ), Column_num )

 

INDEX 함수 사용 방법에서 Row_num 입력 자리에 MATCH 함수를 입력합니다.

이때 Array는 참조하려는 값이 포함된 열의 범위를 지정합니다.

Column_num는 값은 항상 1을 입력하기 때문에 생략하셔도 됩니다. 이유는 Array에 입력하는 데이터 범위가 열 한 개만을 지정하기 때문입니다.

 

만약, HLOOKUP 함수를 대치하여 사용하려면 다음과 같이 Column_num 입력 자리에 MATCH 함수를 입력합니다.

 

   = INDEX( Array, Row_num, MATCH( Lookup Value, Lookup Array, Match Type ) )

 

4. 예제로 확인

[그림 1]과 같이 영업 실적 시트를 예제로 Index 함수와 Match 함수를 사용하는 것에 대해 알아보겠습니다.

 

[그림 1] 영업 실적 예제

[그림 1]에 있는 데이터에서 사람 이름을 입력하면 그 사람의 매출 실적을 따로 표시되도록 [그림 2]와 같이 시트에 작성합니다.

 

[그림 2] 검색하여 해당 데이터 출력

[그림 2]의 성명 바로 밑의 셀(I5)에 찾고자 하는 사람의 이름을 입력하면 그 사람의 매출이 J5 ~ M5 셀에 나타나도록 하려고 합니다.

 

이럴 때 주로 사용하는 것이 VLOOKUP 함수입니다. 

 

J5 셀에 다음과 같이 입력합니다.

 

  =VLOOKUP($I$5,$C$4:$G$11,2,0)

 

위와 같이 입력하면 I5 셀에 이름을 입력하면 해당하는 사람의 1분기 실적이 입력되는 것을 확인하실 수 있습니다. 그런데, 데이터 범위 지정을 보시면 $C$4:$G$11을 지정하였습니다.

데이터 전체를 하지 않은 이유는 VLOOKUP 함수의 경우 찾으려는 값이 있는 열이 데이터 범위 가장 왼쪽 열에 위치해야 하기 때문입니다.

 

 

 

 

[그림 2]에서 검색하여 데이터를 찾는 내용 중에서 직위도 함께 찾아 표시하도록 [그림 3]과 같이 수정합니다.

 

[그림 3] 검색 목록에 직위 추가

J열에 직위를 찾는 항목을 추가하였습니다. 문제는 [그림 1]에서 데이터를 보시면 가장 왼쪽 열(B열)에 직위가 있고 그다음 열(C열)에 성명이 있습니다. 이렇게 되면 VLOOKUP 함수를 사용하여 직위를 찾을 수 없게 됩니다. 이유는 성명이 가장 왼쪽에 위치하여야 하기 때문에 검색 범위를 C열부터 잡아야 해서 B열은 데이터 범위로 포함시킬 수 없게 됩니다. VLOOKUP 함수를 사용하려면 입력된 데이터의 B열과 C열의 위치를 바꿔줘야 합니다. 

 

하지만, Index 함수와 Match 함수를 사용하면 데이터의 열 변환 없이도 VLOOKUP 함수로 데이터를 검색하여 찾는 것처럼 할 수 있습니다.

 

J5 셀에 다음과 같이 수식을 입력합니다.

 

  =INDEX($B$5:$B$11,MATCH($I$5,$C$5:$C$11,0),1)

 

$B$5:$B$11 : 찾으려는 값(직위)이 입력되어 있는 열인 B열을 선택합니다.

 

$I$5 : 검색하려는 사람 이름을 입력하는 셀 주소

 

$C$5:$C$11 : 검색하려는 데이터(성명)가 입력되어 있는 열을 입력

 

0 : 데이터를 검색하여 첫 번째 동일한 값을 만났을 때 인덱스 번호를 반환하라는 옵션

 

1 : 셀 주소의 열 값인데 Index 함수에서는 열 하나만을 범위로 지정하였으므로 1을 입력하였으며 생략하여도 상관없습니다. 생략하면 기본값으로 1이 지정됩니다.

 

J5 셀에 해당하는 직위가 제대로 표시되는 것을 확인하실 수 있습니다.

 

이제 K5 ~ N5 셀에 다음과 같이 입력합니다.

 

1분기 (K5 셀) : =INDEX($D$5:$D$11,MATCH($I$5,$C$5:$C$11,0))

 

2분기 (I5 셀) : =INDEX($E$5:$E$11,MATCH($I$5,$C$5:$C$11,0))

 

3분기 (M5 셀) : =INDEX($F$5:$F$11,MATCH($I$5,$C$5:$C$11,0))

 

4분기 (N5 셀) : =INDEX($G$5:$G$11,MATCH($I$5,$C$5:$C$11,0))