주어진 key에 대한 값을 찾아 주는 vlookup 함수

VLOOKUP 함수는, 같은 값을 갖는 행을 찾아서, 그 행에 해당하는 다른 열의 값을 반환한다. 이 함수가 필요한 전형적인 경우를 보자.

즉, VLOOKUP(A5, $A$8:$B$13,2,FALSE)에서

A5 는 다른 검색영역에서 찾을 실제 값이다. 위에서는 번호와 점수가 연관된 표가 있으므로 번호를 사용했다.
$A$8:$B$13 는 검색을 할 영역을 지정하고 있다. 이 때, A8 로 하지 않고 $A$8 로 한 이유는, 그 영역을 고정시키기 위한 것이다. 즉, 끌어 놓기를 하면 A5로 된 것은 A6, A7 로 변해야 하지만 검색영역은 고정시켜야 하기 때문에 사용한다. 만약 같은 파일의 다른 sheet를 영역으로 지정하고 싶다면 sheet_name!$A$8:$B$13 처럼 가장 앞에 sheet 이름을 쓰고 느낌표 ! 를 한 다음 영역을 지정하면 된다. 이것은 다른 함수에서도 동일한 방식을 따른다.
2 는, 검색영역에서 찾아진 값 중 두 번째 열에 있는 값을 원한다는 것을 표시한다.
FALSE 는, A5 에 해당하는 값과 '정확히 같은 값'을 검색하라는 의미이다.

그 기본 원리는 다음과 같다.

알고 싶은 것은, 김수로의 영어 성적인데, 현재 영어 성적은 번호로 정리되어 있다. 이럴 때 해야 하는 작업은 김수로의 번호에 해당하는 영어 성적을 찾는 일인데, 위와 같은 순서로 진행되게 된다. 즉, 김수로의 번호를 찾고, 영어 성적으로 가서 그 번호에 해당하는 점수를 찾은 후, 그 점수를 가져오는 것이다. 정확히 이와 같은 류의 작업을 하는 함수가 vlookup 함수이다. 즉, (값1, 값2), (값2, 값3) 이 있을 때, (값1, 값3) 을 만드는 작업. 이 함수의 원형(prototype)은 다음과 같다.

VLOOKUP(검색값, 검색구간영역, 반환될 값의 열 번호, 일치정도의 옵션)

각각을 살펴 보면,
1. 검색값 : 이 값은 key 에 해당하는 값이 무엇이냐, 하는 것으로, 위의 예에서는 김수로의 번호에 해당하는 4가 되겠다. 따라서 위의 경우 이 값의 실제값은 C17 이 되겠지.
2. 검색구간영역 : 검색값을 찾을 구간이다. 이 구간의 첫 번째 열에서 검색값을 찾는다. 위의 경우 B10에서 C14까지 있으니까 실제값은 B10:C14인데, 밑의 데이터 김일순이나 이말자의 경우에도 사용할 것이므로 지정된 영역을 변경하지 않는 옵션 $을 써서 실제 값은 $B$10:$C$14 가 된다. 같은 파일의 다른 sheet, 다른 파일의 다른 sheet, 같은 sheet의 다른 영역 (위 예제처럼)이 모두 검색구간영역이 될 수 있다.
3. 반환될 값의 열 번호 : 검색값을 검색구간영역에서 찾았다면, 그 행에 해당하는 값 중 몇 번째 열에 있는 값을 반환할 것인가를 설정한다. 위의 경우 두 번째 열에 있는 값을 반환하면 되니까 2 가 된다.
4. 일치정도 옵션 : 검색구간영역에 검색값과 '정확히' 일치하는 것이 있을 때는 FALSE이고, 정확한 값이 없고 검색값을 중간에 포함할 수 있는 두 값이 있다면 TRUE 이다. 이 때, 만약 이 옵션에 TRUE로 사용할 경우 검색구간영역은 첫 번째 열을 기준으로 오름차순으로 정렬되어 있어야 한다. 아마도 binary search 를 이용하기 위함이겠지.

따라서 위 예제의 경우 D17에 들어갈 실제 함수는 다음과 같다.

=VLOOKUP(C17,$B$10:$C$14,2,FALSE)

이 상태로 drag 하면 나머지 값도 쉽게 채울 수 있게 된다.

sheet에 들어 있는 데이터가 매우 많을 경우 - 약 10,000 개? - , vlookup 함수를 한 번 실행한 이후에는 상황이 괜찮다면 값으로 붙여 넣기^[각주:1]를 한다. 왜냐 하면, vlookup 이 적용된 sheet를 수정할 때마다 이 함수를 재실행하게 되는데, 막상 이 함수의 결과에는 영향을 주지 않는 어떤 작업을 수행하더라도 이 작업이 재실행될텐데 그와 같은 작업을 할 때 실행시간이 꽤 걸릴 수 있기 때문이다.

1. 복사할 위치에 마우스 커서를 놓고 오른쪽 클릭을 해서 나오는 메뉴 중 세 번째의 "선택하여 붙여 넣기...", 또는 영어 버전의 "Paste Special..." [본문으로]