엑셀의 VLOOKUP 함수는 스프레드시트에서 원하는 값을 빠르게 찾아낼 때 사용하는 대표적인 조회 함수입니다. 이 글에서는 Excel 2019와 Microsoft 365를 포함한 모든 버전의 엑셀에서 VLOOKUP 함수를 사용하는 방법을 기본 문법부터 다양한 실전 예제까지 자세히 설명합니다.
VLOOKUP 함수란 무엇인가?
VLOOKUP 함수는 표 형태로 정리된 데이터에서 특정 값을 찾는 데 사용됩니다. 열 제목을 기준으로 데이터가 행별로 정리되어 있다면, VLOOKUP을 통해 특정 열에서 원하는 값을 손쉽게 찾아낼 수 있습니다.
VLOOKUP을 실행하면, 엑셀은 먼저 찾고자 하는 데이터가 있는 행을 찾은 뒤, 그 행 안에서 지정된 열에 위치한 값을 반환합니다. 즉, '세로(Vertical)로 찾는다'는 의미에서 VLOOKUP이라는 이름이 붙었습니다.
VLOOKUP 함수의 문법과 인수
VLOOKUP 함수는 네 가지 요소로 구성됩니다.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value(찾을 값): 검색하려는 값입니다. 반드시 lookup_table 범위의 첫 번째 열에 있어야 합니다.
- table_array(참조 범위): 검색이 이루어질 범위입니다. 찾을 값이 포함된 전체 표 범위를 지정합니다.
- col_index_num(열 번호): 참조 범위의 왼쪽에서부터 몇 번째 열에 있는 값을 반환할지 나타내는 숫자입니다.
- range_lookup(일치 옵션): 선택 사항이며 TRUE 또는 FALSE를 입력할 수 있습니다. TRUE(또는 생략)는 근사값 일치를, FALSE는 정확한 일치를 의미합니다. 생략 시 기본값은 TRUE로 설정되어 근사 일치를 찾습니다.
VLOOKUP 함수 활용 예제
다음은 VLOOKUP 함수가 실제로 작동하는 모습을 보여주는 다양한 예제입니다.
예제 1: 단어 옆에 있는 값 찾기
=VLOOKUP("Lemons",A2:B5,2)여러 품목 목록에서 '레몬(Lemons)'의 재고량을 확인하는 간단한 예제입니다. 검색 범위는 A2:B5이며, '재고(In Stock)' 정보가 범위 내 두 번째 열에 있으므로 열 번호는 2를 입력합니다. 결과값으로 22가 반환됩니다.
예제 2: 이름으로 직원 번호 찾기
=VLOOKUP(A8,B2:D7,3)
=VLOOKUP(A9,A2:D7,2)
같은 데이터 집합을 사용하지만 서로 다른 열에서 정보를 가져오는 두 가지 예제입니다. 첫 번째 수식은 A8 셀의 이름(Finley)에 해당하는 직급을 세 번째 열에서 가져오고, 두 번째 수식은 A9 셀의 사원번호(819868)와 일치하는 이름을 두 번째 열에서 반환합니다. 수식이 특정 텍스트가 아닌 셀 참조를 사용하기 때문에 따옴표 없이 작성해도 됩니다.
예제 3: IF문과 함께 사용하기
=IF(VLOOKUP(A2,Sheet4!A2:B5,2)>10,"No","Yes")
VLOOKUP은 다른 엑셀 함수와 결합하거나 다른 시트의 데이터를 활용할 수도 있습니다. 이 예제에서는 A열 품목을 추가로 주문해야 하는지 판단하기 위해 두 가지 기법을 동시에 사용했습니다. IF 함수를 통해 Sheet4!A2:B5 범위에서 두 번째 위치의 값이 10보다 크면 'No'를 표시하여 추가 주문이 필요 없음을 나타냅니다.
예제 4: 표에서 가장 가까운 숫자 찾기
=VLOOKUP(D2,$A$2:$B$6,2)
마지막 예제는 신발 대량 주문 건수에 따라 적용해야 할 할인율을 찾는 상황입니다. 찾으려는 주문 수량은 D열에 있고, 할인 정보가 담긴 범위는 A2:B6이며 그중 두 번째 열에 할인율이 있습니다. 정확한 일치가 필요하지 않으므로 range_lookup 인수를 비워두어 TRUE(근사 일치)로 처리합니다. 정확한 값이 없으면 그보다 작은 가장 가까운 값이 적용됩니다.
예를 들어 60개 주문의 경우 표에 정확히 일치하는 값이 없으므로 바로 아래 작은 값인 50이 적용되어 75% 할인율이 계산됩니다. F열은 할인이 반영된 최종 가격을 보여줍니다.
VLOOKUP 오류 및 주의사항
엑셀에서 VLOOKUP 함수를 사용할 때 기억해야 할 핵심 사항은 다음과 같습니다.
- 찾을 값이 텍스트 문자열인 경우 반드시 따옴표로 묶어야 합니다.
- VLOOKUP이 결과를 찾지 못하면 #N/A 오류가 반환됩니다.
- 참조 범위 내에 찾을 값보다 크거나 같은 숫자가 없으면 오류가 발생합니다.
- 열 번호가 참조 범위의 실제 열 개수보다 크면 #REF! 오류가 반환됩니다.
- 찾을 값은 항상 참조 범위의 맨 왼쪽 열에 위치해야 하며, 열 번호 계산 시 이 열이 1번으로 간주됩니다.
- range_lookup을 FALSE로 지정했는데 정확한 일치가 없으면 #N/A가 반환됩니다.
- range_lookup을 TRUE로 지정했는데 정확한 일치가 없으면 그보다 작은 다음 값이 반환됩니다.
- 정렬되지 않은 표에서는 range_lookup을 FALSE로 지정하여 첫 번째 정확한 일치값을 반환받는 것이 좋습니다.
- range_lookup이 TRUE이거나 생략된 경우, 첫 번째 열은 알파벳순 또는 숫자순으로 정렬되어 있어야 합니다. 정렬되어 있지 않으면 예상치 못한 값이 반환될 수 있습니다.
- 절대 참조($ 기호 사용)를 활용하면 참조 범위가 변경되지 않은 채 수식을 자동 채우기할 수 있습니다.
VLOOKUP과 유사한 다른 함수들
VLOOKUP은 세로 방향 조회를 수행하는 함수로, 열을 기준으로 정보를 가져옵니다. 만약 데이터가 가로 방향으로 정리되어 있고 행을 따라 내려가며 값을 찾고 싶다면 HLOOKUP 함수를 사용하면 됩니다.
또한 XLOOKUP 함수는 VLOOKUP과 비슷하지만 어떤 방향으로든 조회가 가능한 더 강력한 최신 함수입니다. Microsoft 365를 사용 중이라면 XLOOKUP 도입도 고려해볼 만합니다.