[엑셀 함수] 찾기와 참조 함수 VLOOKUP 함수 강좌입니다.

이번 게시물부터는 찾기와 참조 함수들을 살펴봅니다. 많은 분들이 힘들어하는 함수 분류이기도 합니다. 엑셀 VLOOKUP 함수부터 배워봅니다.

Vertical lookup 에서 VLOOKUP 이란 함수명이 왔습니다. 비슷한 엑셀 함수로 HLOOKUP 함수도 있습니다. 이 둘이 또 짝이지요.

본론 들어갑니다.

* 엑셀 VLOOKUP 함수

기능: 검색값을 범위에서 찾아 필요한 열의 값을 취하는 함수입니다.
용법: =VLOOKUP( 검색값, 범위, 열번호, [검색유형] )
- 검색 유형은 옵션 인수이지만 아주 중요한 부분입니다. 검색 유형은 두가지 입니다.
  비슷하게 일치하는 것을 찾는 유형 (TRUE 또는 1 또는 생략) 과 정확히 일치하는 것을 찾는 유형 (FALSE 또는 0) 두가지 입니다.

용례1: =VLOOKUP( 25, C3:E10, 2)
용례1의 결과:
25의 값을 C3:E10 범위의 첫번째 열(열머리글 C열)에서 찾아서 범위에서 두번째 열(열 머리글 D열) 의 동일행 값을 결과로 반환합니다. 옵션인 검색 유형이 생략되어 있습니다. 생략된 경우는 TRUE 를 기본값으로 취합니다. 만일 25를 찾지 못하였다면 25보다 작은 값 중 가장 큰 값을 일치값으로 찾습니다.
이렇게 근사값으로 찾을 때는 반드시 첫번째 열이 오름 차순 정렬되어 있어야 합니다. 그렇지 않으면 정확한 값을 얻을 수 없습니다.

용례2: =VLOOKUP( 40, A2:C10, 3, TRUE)
용례2의 결과:
용례1과 사용법이 동일합니다. 단지 검색 유형을 명시적으로 설정해 놓았을 뿐입니다. 40 이란 값을 A2:C10의 범위에서 찾아 그 3번째 열인 C열의 동일행의 값을 취합니다. 검색 유형이 TRUE 이기에  비슷한 값을 찾습니다. 첫번째 열은 오름 차순 정렬되어  있어야 합니다.

용례3: =VLOOKUP( "ABC", A1:D5, 2, FALSE)
용례3의 결과:
엑셀 VLOOKUP 함수로 "ABC" 를 A1:D5 범위에서 찾아 2번째 열 B열의 동일행의 값을 반환합니다. 검색 유형이 FALSE 이기에 정확하게 일치하는 값만을 찾습니다. 만일 "ABC" 의 값을 찾을 수 없다면 #N/A 에러를 발생합니다.

용례4: =VLOOKUP( "ABC", A1:D5, 2, TRUE)
용례4의 결과:
"ABC" 를 A1:D5 범위에서 찾아 2번째 열의 동일행의 값을 반환합니다. 검색 유형이 TRUE 이기에 유사값을 찾습니다. 유사값을 찾을 때는 반드시 오름 차순 정렬되어 있어야 한다는 것 기억해 주세요. 수치를 찾을 때는 정확한 값이 없다면 보다 작은 가장 큰 값을 결과로 반환합니다. 하지만 문자열은 찾는값 보다 정렬 순서가 이전이면서 가장 마지막 값을 결과로 반환합니다.

기출 예제: 등급[C3:C10]과 면접표[G4:H8]를 이용하여 평가 점수[E3:E10]를 구하시오. (컴퓨터활용능력 2급 13회 E형)
- 평가점수 = 평점 + 필기점수
- HLOOKUP, VLOOKUP, INDEX 함수 중 알맞은 함수를 선택하여 사용.

기출 풀이:
E3 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.

=VLOOKUP(C3,$G$4:$H$8,2)+D3

입력후 E3셀의 채우기 핸들을 이용 수식 복사 합니다.

문제를 풀어 보면 등급을 등급표에서 찾아 그 등급에 해당하는 평점을 구하여 필기 점수와의 합계를 구합니다.

등급표를 수직으로 찾기에 엑셀 VLOOKUP 함수가 적당하네요. 또 등급표가 오름 차순 정렬이 되어 있기에 VLOOKUP 의 검색 유형을 비슷한 값을 찾는 방법을 택했습니다. 문자열이기에 정확하게 일치하는 값을 찾는 것도 좋습니다. 만일 등급표가 오름 차순 정렬되어 있지 않은 상태라면 위의 식은 엉뚱한 값을 나타낼 수 있습니다. 정렬되어 있지 않은 것을 찾을 때는 정확하게 일치하는 값을 찾는 옵션인 마지막 검색 유형 인수 FALSE 를 설정해 주시는 것이 맞습니다.

수식을 보면 엑셀 VLOOKUP 함수는 C3의 등급을 G4:H8 의 범위의 첫번째 열에서 찾아 2번째 열의 동일행 값을 반환하고 이 값과 D3의 필기 점수를 더해 최종 결과를 만들어 냅니다. 그런데 왜 VLOOKUP 함수의 범위 인수가 절대 참조 되어 있을까요?  

수식을 입력하고 결과를 얻는 열은 E열이지요. 먼저 E3 셀에서 작업했고 그 수식을 아래의 셀들로 수식 복사 합니다. 그러면 함수에서 참조하던 인수들도 아래로 행이 증가 하지요. 하지만 등급표는 수식 복사에 상관없이 범위 고정이지요. 하여 절대 참조가 사용되었습니다.

!!! VLOOKUP 함수 사용 유의점

1. 마지막 인수 검색 유형을 생략하면 TRUE(1) 유사값을 찾는다는 것.
2. 유사값을 찾을 때는 반드시 오름 차순 정렬되어 있어야 합니다.(정렬되어 있지않으면 엉뚱한 값이 나올 수 있음.)
3. 유사값을 찾을 때 일치값이 없고 보다 작은 값이 없으면 #N/A 에러를 발생.
4. 정확하게 일치하는 값을 찾을 때, 값을 찾을 수 없다면 #N/A 에러를 발생.

@ 이상이 엑셀 VLOOKUP 함수입니다. 찾기와 참조 함수는 상당히 까다롭다면 까다로운 함수 입니다. 질문있으면 댓글 달아 주세요.

감사합니다. ^^

[엑셀 모바일] VLOOKUP 함수로 수직 검색후 열 반환하기 :
http://secstart.tistory.com/754

* 컴활 2급과 컴활 3급 함수목록 ver 0.2