저는 엑셀 작업을 하면서 데이터 표 간의 연결이 필요할 때 VLOOKUP를 자주 활용합니다. 예를 들어 주문 내역에서 상품 코드를 기준으로 상품명이나 가격을 즉시 찾아야 할 때 이 함수가 큰 시간을 절약해 주었습니다. 아래 글은 실제로 사용했던 경험을 바탕으로 VLOOKUP의 기본 원리와 실무 예제, 그리고 주의사항을 간결하게 정리한 것입니다.
VLOOKUP 함수 개요
VLOOKUP은 세로 방향으로 특정 값을 찾아, 그 값이 존재하는 행의 다른 열에 있는 데이터를 반환하는 엑셀 함수입니다. 데이터를 연결하고 빠르게 조회해야 하는 상황에서 자주 사용됩니다.
주요 특징
- 찾고자 하는 값이 표의 첫 번째 열에 있어야 합니다.
- 정확히 일치(FALSE) 또는 근사 일치(TRUE) 중 하나를 선택할 수 있습니다.
- 반환할 값은 table_array의 왼쪽에서부터 시작하는 열 인덱스(col_index_num)로 지정합니다.
- 근사 일치를 사용할 때는 table_array의 첫 열이 오름차순으로 정렬되어 있어야 합니다.
함수 구문
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])인수 설명
- lookup_value (찾을 값): table_array의 첫 번째 열에서 찾으려는 값입니다. 숫자, 텍스트, 날짜 혹은 셀 참조가 될 수 있습니다. 예: “사과”, 101, A2
- table_array (찾을 범위): lookup_value를 찾을 열과 반환할 값이 들어 있는 열을 모두 포함하는 데이터 범위입니다. 이 범위의 첫 열이 lookup_value가 있어야 하며, 보통 절대 참조($A$1:$D$10)를 사용합니다.
- col_index_num (열 번호): lookup_value가 있는 행에서 반환할 값이 들어 있는 열의 번호입니다. 예를 들어 table_array의 왼쪽 열이 1, 그다음 열이 2입니다.
- [range_lookup] (일치 옵션 – 선택 사항): 정확히 일치할지( FALSE ) 근사 일치할지(True) 여부를 설정합니다. FALSE일 때는 정확히 일치하는 값을 찾고, 일치하지 않으면 #N/A를 반환합니다. TRUE일 때는 근사치를 찾고, 첫 열이 오름차순으로 정렬되어 있어야 합니다.
예제 모음
다음은 가상의 “상품 목록” 시트와 “주문 내역” 시트를 기반으로 한 간단한 예제들입니다. 데이터 구성이 실제 상황과 비슷하다고 생각하시면 이해에 도움이 됩니다. 자세한 데이터 구성은 아래와 같습니다.
상품 목록 시트(Sheet1)
상품 코드 | 상품명 | 가격 | 재고
P001 사과 1000 50
P002 바나나 1500 30
P003 오렌지 1200 40
P004 딸기 2500 20
P005 포도 3000 10주문 내역 시트(Sheet2)
주문 번호 | 상품 코드 | 상품명 | 가격 | 수량 | 총액
ODR001 | P002 | | | 2 |
ODR002 | P004 | | | 1 |
ODR003 | P001 | | | 3 |
ODR004 | P005 | | | 2 |예제 1: 정확히 일치하는 값으로 상품명 찾기
목표: 주문 내역 시트에서 상품 코드로 상품명을 가져오기
=VLOOKUP(B2, Sheet1!$A$2:$D$6, 2, FALSE)- lookup_value: B2(현재 행의 상품 코드, 예: “P002”)
- table_array: Sheet1!$A$2:$D$6(상품 목록의 코드부터 재고까지의 범위, 절대참조로 고정)
- col_index_num: 2(상품명은 두 번째 열에 위치)
- range_lookup: FALSE(정확히 일치)
결과: 주문 내역 시트의 C 열에 해당하는 상품명이 채워집니다. 예를 들어 ODR001의 C 열은 바나나가 됩니다.
예제 2: 정확히 일치하는 값으로 가격 찾기
목표: 주문 내역 시트에서 상품 코드로 가격을 가져오기
=VLOOKUP(B2, Sheet1!$A$2:$D$6, 3, FALSE)- col_index_num: 3(가격은 세 번째 열)
결과: 각 행의 가격이 채워집니다. 예를 들어 ODR001은 1500이 됩니다.
예제 3: IFERROR를 이용한 오류 처리
목표: 존재하지 않는 상품 코드가 입력되었을 때 친절한 메시지 표시
=IFERROR(VLOOKUP(B2, Sheet1!$A$2:$D$6, 2, FALSE), "상품 없음")- lookup_value가 표에 없으면 “상품 없음”으로 표시됩니다.
결과: 존재하지 않는 코드가 입력되면 C 열에 “상품 없음”이 표시됩니다.
예제 4: 근사 일치를 이용한 등급 부여
목표: 근사치를 이용해 점수에 따른 등급을 매기기
참고: 이 예제는 근사 일치의 일반적인 사용법을 보여주며, 표의 첫 열은 반드시 오름차순으로 정렬되어 있어야 합니다.
성적 등급 시트(Sheet3)
점수 이상 | 등급
0 | F
60 | D
70 | C
80 | B
90 | A
학생 점수 시트(Sheet4)
이름 | 점수 | 등급
김철수 | 85 |
이영희 | 72 |
박민수 | 58 |
최지아 | 91 |학생 점수 시트의 C2 셀에 입력:
=VLOOKUP(B2, Sheet3!$A$2:$B$6, 2, TRUE)- lookup_value: 학생의 점수(예: 85)
- table_array: Sheet3!$A$2:$B$6(점수와 등급 범위)
- col_index_num: 2(등급은 두 번째 열)
- range_lookup: TRUE(근사 일치) – 85점은 80점 구간의 등급인 B를 반환
결과: C 열에 각 학생의 등급이 채워집니다. 예: 김철수 85점 → B, 이영희 72점 → C, 박민수 58점 → F, 최지아 91점 → A
VLOOKUP 사용 시 주의사항 및 팁
- 첫 번째 열 규칙: lookup_value는 table_array의 첫 번째 열에 있어야 하며, 찾으려는 값이 이 열에 없으면 VLOOKUP은 작동하지 않습니다. 이때는 INDEX+MATCH나 XLOOKUP를 고려해 보세요. VLOOKUP 함수 – Microsoft Learn
- 절대 참조 사용: 수식을 다른 셀로 복사해도 범위가 고정되도록 $A$1:$D$10과 같이 절대 참조를 사용하는 것이 좋습니다. Excel Jet: VLOOKUP Function
- 정확히 일치를 기본으로: 대부분의 경우 정확한 값을 찾아야 하므로 range_lookup을 FALSE로 설정하는 것이 안전합니다. 필요 시 근사 일치를 사용할 때만 TRUE를 사용합니다.
- 근사 일치의 정렬 필요성: range_lookup을 TRUE로 사용할 때는 첫 열이 오름차순으로 정렬되어 있어야 합니다. 그렇지 않으면 예기치 않은 결과가 나올 수 있습니다.
- 오류 처리: lookup_value를 찾지 못하면 #N/A가 발생합니다. 이 경우 IFERROR를 활용해 사용자 친화적인 메시지로 처리하는 것이 좋습니다.
- 대소문자 구분 없음: 기본적으로 대소문자를 구분하지 않습니다.
- 공백 주의: 셀 값에 관찰되지 않는 공백이 있으면 일치하지 않을 수 있습니다. 필요 시 TRIM을 활용해 값을 먼저 정리하십시오. 예: =VLOOKUP(TRIM(B2), …)
- 와일드카드 사용: range_lookup이 FALSE인 경우, 와일드카드 문자(*)와 (?)를 사용할 수 있습니다. 예: “사과*”은 “사과”와 “사과주스” 등과 일치합니다.
참고 자료