참조 함수, VLOOKUP
이번에는 엑셀 함수의 꽃이라 불리는 VLOOKUP 함수를 배워보겠습니다.
엑셀 열과 행으로 결합된 셀의 집합으로 이루어져 있습니다.
열과 행의 번호로 셀의 주소를 부여하고, 이 주소를 통해 셀에 있는 값을 참조할 수 있습니다.
구조화된 자료를 대량으로 다루는 것에 최적화 된 엑셀.
마치 DB(Database)를 연상시키지 않나요?
엑셀은 일반 사용자가 손쉽게 DB(데이터베이스)를 사용 가능하게 해주는 프로그램이라 해도 손색이 없습니다.
DB는 데이터를 입력하고, 원하는 데이터만 조회하는게 핵심적인 기능입니다.
수천, 수만 건의 데이터가 입력된 워크시트에서, 필요할 때 그 중 원하는 값만 불러와 사용할 수 있다는 것은 엄청나게 매력적인 기능입니다.
엑셀은 다양한 방법으로 이런 기능을 제공하지만, 그 중 가장 손쉽게 접근 가능하고 활용범위가 넓은 함수가 바로 VLOOKUP 입니다.
가장 손쉬운 방법이라 했으나 VLOOKUP 사용에 어려움을 겪는 사용자가 많습니다.
여러 단계의 예제를 통해 VLOOKUP 함수를 마스터 하시기 바랍니다.
함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(VLOOKUP)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.
위의 그림에서 =VLOOKUP( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (lookup_value, table_array, col_index_num, [range_lookup]) 의 의미는 아래와 같습니다.
lookup_value : 찾을 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
table_array : 찾을 값(lookup_value)과 참조할 값이 포함된 범위 주소를 두 번째 인수로 입력해야 합니다. (필수 입력)
col_index_num : 참조할 값이 참조 범위 주소의 몇 번째 열에 위치하고 있는지를 세 번째 인수로 입력해야 합니다. (필수 입력)
[range_lookup] : FALSE 면 정확히 일치하는 값, TRUE 또는 생략하면 비슷하게 일치하는 값을 찾는 찾기 옵션을 네 번째 인수로 입력해야 합니다. (선택 입력)
왼쪽의 표에 학생의 이름과 생년월일 정보가 포함된 목록이 있습니다.
해당 목록에 있는 학생 중 한 학생의 생년월일 정보를 검색하여 참조해 보겠습니다.
참조 함수, VLOOKUP 자세히 살펴보기
E3 셀에 찾을 이름 “김민정” 을 입력합니다.
F3 셀에 =VLOOKUP(E3,B3:C8,2,FALSE) 함수 수식을 입력합니다.
그러면 F3 셀에 김민정 학생의 생년월일 값 “1997.5.21” 이 출력됩니다.
F3 셀에 입력된 참조 함수 수식을 분석해 보겠습니다.
- 첫 번째 인수 lookup_value E3 는 찾을 값 입니다. 즉, 찾을 학생의 이름이 입력되어 있는 참조 주소 입니다.
- 두 번째 인수 table_array B3:C8 는 찾을 값이 포함된 테이블의 참조 범위 주소 입니다.
이 때 주의할 점은 lookup_value 의 값이 포함되어 있는 열이 가장 좌측에 위치하는 범위로 지정해야 한다는 것 입니다.
위의 예제는 학생의 이름이 lookup_value 이기 때문에 이름 열이 가장 좌측에 위치하도록 B 열 부터 table_array 의 범위를 지정해야 한다는 것 입니다.
즉, 찾을 값과 참조할 값이 포함된 범위인 B3:C8 을 table_array 로 지정하면 됩니다.
참조 범위는 절대주소로 지정하는 것이 좋습니다. 수식을 복사하여 다른 셀에 적용시 참조 범위가 의도치 않은 형태로 변경될 수 있기 때문입니다. - 세 번째 인수 col_index_num 은 참조할 값이 위치한 컬럼의 위치 값을 의미합니다.
참조 범위인 B3:C8 에서는 B 열에 위치한 값은 1, C 열에 위치한 값은 2의 값을 갖습니다. - 네 번째 인수 [range_lookup] 은 값을 찾는 방식에 대한 옵션 입니다.
선택적으로 입력되는 인수이며 생략시 TRUE 가 입력된 것과 동일하게 동작합니다.
학생의 이름은 텍스트로 이루어진 값이며 완전히 일치하는 경우를 찾고 있기 때문에 FALSE 를 입력하면 됩니다.
참조 범위의 가장 좌측 열에서 김민정 학생의 행을 찾아서 두 번째 열에 위치한 값을 출력하라는 함수 입니다.
입력된 수식이 올바르게 동작하여 F3 셀에 김민정 학생의 생년월일 값 “1997.5.21” 이 출력된 것 입니다.
참조 함수, VLOOKUP 오류
찾으려는 값 lookup_value 가 참조 범위 table_array 에 있는 경우에는 정상적인 결과가 출력됩니다.
만약, 찾으려는 값이 참조 범위에 존재하지 않는 값이라면 어떻게 될까요?
찾을 이름 E3 셀에 참조 범위에 존재하지 않는 학생의 이름 “홍길동” 을 입력해 보겠습니다.
생년월일 F3에 입력된 수식은 변경하지 않았습니다.
E3 셀에 “홍길동” 을 입력하고 엔터를 누르면 F3 셀에 “#N/A” 오류가 발생하는 것을 보실 수 있습니다.
찾을 값에 해당하는 값이 존재하지 않는다는 오류 입니다.
위의 오류가 발생하면 VLOOKUP 함수에 입력된 인수의 값을 자세히 확인해야 합니다.
위의 예제에서는 두 번째 인수인 참조 범위 B3:C8 에 찾을 값인 첫 번째 인수 E3의 값이 정확하게(네 번째 인수 FALSE) 일치하는 값이 없기 때문입니다.
VLOOKUP 함수에 사용되는 네 개의 인수 중 어느 하나라도 문제가 있다면 “#N/A” 오류를 보게 됩니다.
참조 함수, VLOOKUP 오류 해결 방법
오류를 해결하는 방법은 무엇일까요?
네 개의 인수를 천천히 살펴보는 것이 그 방법 입니다.
VLOOUP 함수 수식이 입력된 셀에서 F2(펑션키)를 누르면 현재 수식이 참조하고 있는 값들을 눈에 띄는 다양한 색상으로 표시해 줍니다.
가장 먼저 두 번째 인수 table_array 참조 범위를 확인해야 합니다.
그 다음으로 세 번째 인수 col_index_num 열 번호를 확인해야 합니다.
마지막으로 확인할 것이 첫 번째 인수 lookup_value 찾을 값 입니다.
위의 예에서 찾을 이름에 “홍길동” 입력시 “#N/A” 오류가 발생했습니다.
table_array 는 B3:C8 로 지정되어 전체 학생의 이름과 생년월일을 포함하고 있기 때문에 문제 없습니다.
col_index_num 은 참조하려는 값이 생년월일 값이고 참조 범위의 두 번째 열에 위치하고 있기 때문에 2로 문제 없이 입력되었습니다.
마지막으로 학생 이름 “홍길동” 이 이름 열에 존재하는지 확인을 해야 합니다.
오류의 원인을 찾았습니다. “홍길동” 은 참조 범위에 존재하지 않는 이름 입니다.
찾을 이름을 “서경준” 으로 참조 범위에 존재하는 값으로 변경하여 입력하니 오류가 사라지고 정상적인 생년월일 값이 출력됩니다.
오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.
각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.
엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.