엑셀, VLOOKUP함수 사용법, INDEX함수와 MATCH함수 - 찾기,참조함수

엑셀로 데이터 작업을 할 때, 가장 많이 사용하는 함수중의 하나가 VLOOKUP함수일 것이다.

VLOOKUP함수는 그만큼 효과적이며 사용하기 쉽다.

 

어떤 백데이터가 있고 여기서 조건에 맞는 데이터를 찾아서 가져와 다른 작업을 하고자 할 때 바로 이 VLOOKUP함수가 쓰인다. LOOKUP의 뜻 그대로 주욱 살펴보는 함수이며 V는 VERTICAL의 머릿글자다. 대부분 데이터가 세로로 나열되어 있으므로 이것을 쓴다. 가로로 데이터를 검색할때는 HLOOKUP함수를 쓴다.

 

 

1. VLOOKUP함수 사용법

 

예제) 아래는 중간,기말성적표이다. 학생별로 중간,기말고사의 평균점수를 작성하라.

 

▶ VLOOKUP함수 사용법

VLOOKUP(찾을값 혹은 기준값, 찾을범위, 열번호, 옵션)

 

VLOOKUP함수는 찾을 범위에서 찾을값에 해당하는 행을 찾고 그 행에서 열번호 위치에 해당하는 셀을 참조하여 셀값을 반환하게 된다.

 

<B14>셀을 작성하기 위해 우선 "이대호"의 중간고사 국어성적을 찾아본다.

찾을값 : $A14   (여기서는 성명을 기준으로 일치하는 행을 찾음) 

찾을 범위 : $A$3:$K$10   (성명에 해당하는 열이 첫번째 열이 되도록 하고, 중간기말성적의 데이터 범위를 포함하도록 지정하면 된다. 지정후 'F4'키를 한 번 눌러 절대참조 형태로 만든다. 채우기핸들을 사용하기 위함이다.),

열번호 : 2   (찾을범위 내에서 참조할 열이 몇번째 열에 위치하고 있는가를 지정) 

옵션 : 0   (정확히 일치하는 값을 찾을때는 FALSE(0), 찾을값보다 작거나 같은 값일 때는 TRUE(1))

 

즉, 이대호의 중간고사 국어성적은

= VLOOKUP($A14,$A$3:$K$10,2,0)

을 하면 된다.

 

평균을 내야 하므로 중간, 기말을 각각 찾아 둘로 나누면 될 것이다.

B14 = (VLOOKUP($A14,$A$3:$K$10,2,0)+VLOOKUP($A14,$A$3:$K$10,7,0))/2

 

채우기 핸들을 이용하여 오른쪽으로 쭈욱 당기고, 또 아래로 쭈욱 당겨 나머지 셀들을 한꺼번에 구하도록 한다.

 

* 잊지 말아야 할 사항

(1) 찾을값이 포함된 열을 찾을범위의 첫열로 설정하여야 한다.

(2) 찾을범위는 보통 일정하게 정해지므로 절대참조로 묶어두고, 찾을값도 적절하게 혼합참조 형태로 하여야 채우기 핸들을 이용하여 나머지 셀을 쉽게 채울 수 있다.

 

 

2. INDEX함수와 MATCH함수

 

어떤 범위에서 행번호와 열번호를 이용하여 데이터를 가져오는 함수가 INDEX함수이며 보통 MATCH함수와 같이 쓰인다. (스타스키와 허치, 터너와 후치 같은 것이라 보면 된다.)

 

예제) 아래는 고객들의 저축액 표이다. 고객별로 이자를 구하라. 이자는 "저축액*이자율"로써 구하도록 한다.

 

 

(1) "저축액"은 위의 VLOOKUP함수를 이용하면 간단히 구할 수 있을 것이다.

<B16> "김정은" 고객의 저축액 = VLOOKUP(A16,$B$2:$D$12,3,FALSE)

 

(2) "이자율"은 은행별로 다르며 은행은 고객별로 설정되어 있으므로 우선 고객별 은행을 먼저 구하고 이후에 은행별 이자율을 구하도록 한다.

 

2-1) 고객별 은행은 고객의 왼쪽에 위치해 있으므로 그냥 VLOOKUP함수로는 찾을 수가 없다.

고객열(B열)의 고객 행위치가 은행열(A열)의 은행 행위치와 같으므로 이것을 (MATCH함수로 찾아) 이용하여 INDEX함수를 적용하도록 한다.

 

▶ INDEX,MATCH함수 사용법

INDEX(찾을 범위, 행위치, 열위치)

MATCH(찾을값 혹은 기준값, 찾을범위, 옵션)

 

INDEX함수로 <B16>셀에 해당하는 은행을 찾아보자.

찾을범위 : $A$2:$A$12   (은행열의 데이터를 지정하고 F4를 눌러 절대참조로 바꾼다.)

행위치 : MATCH(A16,$B$2:$B$12,0) (고객열에서 찾을 고객이 위치한 행을 구한다.)

- MATCH함수 인수 (찾을값 : A16 (찾고자 하는 고객명), 찾을범위 : $B$2:$B$12 (고객열 데이터), 옵션 : 0(정확한 값을 찾음))

열위치 : 1 (찾을범위의 열이 하나뿐이므로 그냥 1을 적는다.)

 

<B16> "김정은"고객의 은행 = INDEX($A$2:$A$12,MATCH(A16,$B$2:$B$12,0),1)

 

2-2) 은행명을 찾았으므로 이제 이것을 VLOOKUP함수의 인수로 하여 F2:G4 범위에서 이자율을 가져오도록 한다.

 

<B16> "김정은"고객의 이자율 = VLOOKUP("김정은"고객의 은행,$F$2:$G$4,2,FALSE)

= VLOOKUP(INDEX($A$2:$A$12,MATCH(A16,$B$2:$B$12,0),1),$F$2:$G$4,2,FALSE)

 

(3) 따라서, 최종적으로 구하는 B16셀의 식은

B16 = "김정은" 고객의 저축액 * "김정은"고객의 이자율

= VLOOKUP(A16,$B$2:$D$12,3,FALSE) *VLOOKUP(INDEX($A$2:$A$12,MATCH(A16,$B$2:$B$12,0),1),$F$2:$G$4,2,FALSE)

 

채우기 핸들을 더블클릭하면 아래의 셀들이 모두 채워진다.

 

* INDEX, MATCH 함수는 약간 까다롭지만 INDEX함수 사용법에 따라 차례로 인수를 대입하며, 찾을값의 행위치나 열위치를 MATCH함수로 찾는다는 것을 기억해두면 유용하게 써먹을 수 있다.

 

 

3. VLOOKUP함수 참고사항

 

- 찾고자 하는 값이 찾을범위에 없을 때는 #N/A 에러가 뜬다.

 

- VLOOKUP함수는 다른 시트의 데이터를 읽어올수도 있고, 다른 파일의 데이터를 읽어오는 것도 가능하다. 다만, 다른 파일의 데이터를 읽어오려면 그 파일도 열려있어야 한다. 안 그러면 #REF(참조 오류)가 뜬다.

 

- 또한, 여러조건에 해당하는 것을 찾을수는 없으며, 찾을값과 일치하거나 일정범위내에 있는 데이터만을 찾을 수 있다.

 

cf.)

* 여러조건에 해당하는 데이터들의 합 구하기 :

 http://blog.naver.com/sejinssy/100133492345 참조

 

*  두가지 조건에 해당하는 행의 참조값 찾기

http://blog.naver.com/sejinssy/100152725456 참조

 

- 옵션값을 TRUE로 할 때는 찾을범위의 값들이 오름차순으로 일정하게 정렬이 되어 있어야 한다.

찾을값보다 작거나 같은 값을 순차적으로 찾기 때문이다. 데이터가 ~이상,~미만으로 정해진 표의 경우 이를 이용할 수 있다.

(예) 과세표를 참조하여 소득에 따른 과세 구하기

 

 

 

* VLOOKUP함수는 사용법이 비교적 간단하고, 백데이터에서 필요한 값을 찾아오는데 효과적이므로 여러 파생적인 작업을 하는데 유용하게 쓰인다. 엑셀 작업의 상당수가 데이터를 찾아오는 것임을 생각해보면 그 유용성을 짐작할 수 있다. 또한, INDEX, MATCH함수는 조금 까다롭기는 하지만 익혀두면 VLOOKUP함수보다 더 폭넓게 활용할 수 있다.)

[출처] 엑셀, VLOOKUP함수 사용법, INDEX함수와 MATCH함수 - 찾기,참조함수|작성자 강동의별