엑셀에서 VLOOKUP 함수는 표(테이블) 또는 행 별 범위에서 원하는 데이터(값)을 찾을 때 사용하는 함수입니다.
정말 유용하게 사용할 수 있는 함수인데 의외로 함수의 존재를 모르거나 사용법에 대해 어려워 하는 사람이 많은듯 합니다.
상품코드만 쭉 나와 있는 시트에 상품 이름을 갖다 붙인 다든지, 상품의 전년도 매출액을 금년도 매출액 옆에 붙여 금액 비교에 사용한 다든지, 또는 상품단가를 단가 시트에서 가져온 다든지 정말 다양한 용도로 활용이 가능합니다.
마스터 성격의 데이터를 한 시트에 모아 놓고 엑셀에서 VLOOKUP을 사용하여 다른 시트에서 해당 마스터의 정보를 쉽게 가져와서 표시할 수 있습니다.
이번 글에서는 엑셀에서 VLOOKUP을 사용하는 방법을 예제를 통해 알아보겠습니다.
VLOOKUP의 기본 형식
▼ VLOOKUP의 기본 구문 형식 입니다. (사용 예)
엑셀의 셀에 “=VLOOKUP(“이라고 입력하면 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 이렇게 도움말이 표시됩니다.
표시된 도움말을 보면 알 수 있듯이 VLOOKUP에는 네 가지 인수가 있습니다.
- lookup_value: VLOOKUP이 찾을 값, 즉 데이터를 찾기 위해 서로 일치시키려는 값을 말합니다. 참조범위의 첫번째 열의 값이기도 합니다.
- table_array: VLOOKUP에서 찾을 데이터가 들어 있는 전체 범위, 즉 데이터를 확인하려는 위치를 말합니다. 다른 엑셀의 시트를 사용할 수도 있습니다. (‘절대참조($)’로 입력)
- col_index_num : 가져오려는 데이터가 들어 있는 참조범위(table_array)의 열 번호를 말합니다. 참조범위의 열은 1번부터 시작합니다.
- range_lookup : 찾을 값의 일치 허용 옵션 입니다. 정확히 일치하는 값을 가져오려면 FALSE 또는 0을 입력하고, 유사일치하는 값을 가져오려면 TRUE 또는 1을 입력하면 됩니다. 기본적으로 FALSE 또는 0을 입력한다고 생각하면 됩니다.
VLOOKUP 사용 예제
▼ VLOOKUP을 사용하는 예제 입니다.
– VLOOKUP 사용 목적은 Sheet1의 이름 열에 Sheet2의 이름을 가져다 붙이는 것입니다. 붙이는 기준은 코드가 서로 일치할 때 입니다.
1. 이름 열 B2에서 =VLOOKUP( 까지 입력하고 A2를 마우스 클릭한 뒤 쉼표(,)까지 입력합니다.
2. 참조 범위가 Sheet2에 있으니 Sheet2를 선택합니다. (참조 범위가 동일한 시트에 있을 수도 있습니다.)
3. 범위를 지정해 줍니다.(Sheet2의 A2부터 C13까지 마우스로 쭉 선택하면 됩니다.)
4. (중요!!) 범위를 선택한 상태에서 F4 키를 눌러 절대참조($)로 변환 합니다. (A2:C13 ▶ $A$2:$C$13)
※ 참고로 엑셀에서 절대 참조와 상대 참조에 대한 자세한 설명은 엑셀에서 상대 참조와 절대 참조의 구분과 사용법 글을 참고하시면 됩니다.
5. 가져올 이름이 Sheet2의 두번째 열에 있기 때문에 3번째 인수에 2를 입력하고 정확히 일치하는 데이터를 가져오기 위해 4번째 인수에 FALSE 또는 0을 입력하고 괄호를 닫고’)‘ 엔터를 누릅니다.
6. Sheet1의 B2 셀에 ‘포도’ 이름을 가져오게 됩니다.
7. B열을 전체 적용하기 위해 B2 셀의 우측 하단의 작은 네모 모양을 마우스 더블클릭하거나 마우스 클릭해서 B열 끝까지 쭉 내리면 B열이 모두 VLOOKUP이 적용됩니다.
8. 아래 캡쳐 화면은 참조 범위 지정할 때 절대참조를 하지 않고 VLOOKUP을 적용했을 때의 화면입니다.
Sheet2!A2:C13 이 부분이 Sheet1의 B열의 행이 내려가면 같이 번호가 증가하게 됩니다.
따라서 참조 범위가 처음 지정했던 범위를 벗어나게 됩니다.
VLOOKUP 오류 코드 설명
엑셀에서 VLOOKUP을 사용할 때 뿐만 아니라 다양한 작업 과정에 ‘#’으로 시작하는 오류 코드를 만나게 됩니다.
- #N/A : 찾는 값이 없을 때 나오는 표시. 따라서 오류는 아닙니다.
- #NAME? : 찾는 값에 텍스트를 직접 입력하는 경우 큰따옴표(“)로 감싸주지 않거나 VLOOKUP 함수를 잘 못 기술하면 #NAME? 오류가 표시됩니다.
- #REF! : 참조범위의 열 번호 숫자가 실제 참조범위 열의 갯수를 초과하면 안 됩니다. 위의 예는 참조범위 열의 수가 총 3개인데 열 번호 5를 입력해서 생긴 오류 입니다.
- #VALUE! : 참조범위의 열 번호 숫자는 0 이하를 입력할 수 없습니다. 위의 예는 열 번호에 숫자 0을 입력해서 생긴 오류 입니다. lookup_value(찾는 값) 인수가 255자를 초과해도 #VALUE! 오류가 표시 됩니다.
– 11행과 12행의 코드는 모두 ‘100’인데 11행은 데이터를 찾아오고 12행은 데이터를 찾아오지 못했습니다.
보기에는 동일한 ‘100’이지만 11행은 숫자 형식이고 12행은 텍스트 형식 입니다. 실제 VLOOKUP을 사용하다 보면 셀 서식의 표시 형식이 달라서 찾지 못하는 경우가 많습니다.(위쪽 Sheet2 캡쳐화면의 코드 ‘100’을 확인해 보세요)
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
이 글이 도움이 되었기를 바랍니다. ^-^