엑셀 VLOOKUP 함수, 야근을 없애는 5분 마법 (실무 예제 완벽 정복)
엑셀 앞에서 한숨 쉬어본 직장인이라면 주목해 주세요. 수백, 수천 개의 데이터 목록에서 특정 '제품 코드'에 맞는 '상품명'을, '사원 번호'에 해당하는 '부서명'을 일일이 눈으로 찾아 복사-붙여넣기 하고 계신가요? 여러분의 퇴근 시간을 앞당겨 줄 마법 같은 함수, VLOOKUP 이 있습니다.
복잡해 보이지만 원리만 알면 정말 간단합니다. 이 글 하나로 VLOOKUP의 기초 개념부터 실무에서 바로 써먹는 활용법, 그리고 골치 아픈 오류 해결법까지 5분 만에 마스터할 수 있도록 모든 것을 알려드리겠습니다.
1. VLOOKUP, 대체 무엇이고 왜 써야 할까요?
VLOOKUP 의 'V'는 Vertical(수직) 을 의미합니다. 그 이름처럼, 거대한 데이터 표에서 기준이 되는 값을 수직으로(↓) 스캔하여 찾아낸 뒤, 같은 행(→)에 있는 내가 원하는 열의 값을 쏙 가져오는 함수 입니다.
이런 상황을 상상해 보세요.
- 쇼핑몰 MD: 일일 주문 목록에 적힌 수백 개의 '상품 코드'를 보고, 별도 시트에 있는 '상품 정보' 표에서 '상품명'과 '판매가'를 하나하나 찾아 입력해야 할 때.
- 인사팀 담당자: '사원번호' 목록을 기준으로 각 직원의 '부서', '직급', '연락처'를 기입해야 할 때.
이런 단순 반복적인 '찾기' 업무를 단 한 번의 수식 입력과 드래그로 끝내주는 것이 바로 VLOOKUP의 존재 이유입니다.
2. VLOOKUP 함수 공식, 뼈대부터 완벽 해부
VLOOKUP 함수는 4개의 칸으로 이루어진 기차와 같습니다. 각 칸에 올바른 정보를 태워야 목적지에 정확히 도착할 수 있죠.
=VLOOKUP( ① 찾을 값, ② 참조 범위, ③ 가져올 열 번호, ④ 검색 방식 )
| 인수 | 공식 이름 | 쉬운 설명 | 예시 |
|---|---|---|---|
| ① 찾을 값 |
Lookup_value
|
검색의 기준이 되는 값입니다. "이걸로 찾아줘!" | 제품 코드 "P-123" |
| ② 참조 범위 |
Table_array
|
찾을 값이 반드시 첫 번째 열 에 위치한 데이터 표 전체 범위 | '상품정보' 시트의
A1:D100
|
| ③ 가져올 열 번호 |
Col_index_num
|
참조 범위 내에서, 가져오고 싶은 데이터가 있는 열의 순서 (첫 번째 열이 1) | '상품명'을 원하면
2
|
| ④ 검색 방식 |
[Range_lookup]
|
[가장 중요!] 어떻게 찾을지 정하는 옵션입니다. |
FALSE
또는
TRUE
|
핵심 포인트: ④ 검색 방식 (FALSE vs TRUE)
이 마지막 옵션이 VLOOKUP의 성패를 좌우합니다. 결론부터 말하면, 실무의 95%는 'FALSE'를 씁니다.
- FALSE (또는 숫자 0): 정확 일치
찾을 값과 100% 똑같은 값 만 찾습니다. 띄어쓰기 하나라도 다르면 다른 값으로 인식합니다.- 제품 코드, 사원번호, 주문번호 등 고유한 키 값 으로 정보를 찾을 때 사용합니다.
- 일치하는 값이 없으면
#N/A오류를 띄웁니다.
- TRUE (또는 숫자 1, 또는 생략): 유사 일치
찾을 값과 정확히 일치하는 값이 없으면, 그 값보다 작거나 같은 값 중 가장 가까운(큰) 값 을 찾습니다.- 점수에 따른 등급, 매출액에 따른 수수료율, 구매 금액별 할인율 등 특정 구간(Range)에 해당하는 값 을 찾을 때 사용합니다.
- 절대 규칙: 이 옵션을 사용하려면 참조 범위(Table_array)의 첫 번째 열이 반드시 오름차순(작은 값에서 큰 값 순서)으로 정렬 되어 있어야 합니다.
3. 실무 예제 1: 상품 코드에 맞는 상품명과 단가 찾아오기 (정확 일치
FALSE
)
가장 대표적인 VLOOKUP 활용법입니다. '주문 목록' 시트에 '상품 정보' 시트를 참조하여 상품명과 단가를 자동으로 채워보겠습니다.
| [상품 정보 시트] | ||
|---|---|---|
| A열 (상품 코드) | B열 (상품명) | C열 (단가) |
| P-001 | 튼튼한 멀티탭 | 12,000 |
| P-002 | 저소음 무선마우스 | 25,000 |
| P-003 | 기계식 키보드 | 89,000 |
| [주문 목록 시트] | |||
|---|---|---|---|
| A열 (주문번호) | B열 (상품 코드) | C열 (상품명) | D열 (단가) |
| 1 | P-003 | (여기에 채울 것) | (여기에 채울 것) |
| 2 | P-001 | ||
| 3 | P-002 |
1) C2셀에 '상품명' 찾아오기
=VLOOKUP(B2, '상품 정보'!$A$2:$C$4, 2, FALSE)
B2: "이걸로 찾아줘!" → '주문 목록' 시트의 상품 코드 "P-003"'상품 정보'!$A$2:$C$4: "여기서 찾아줘!" → '상품 정보' 시트의 A2부터 C4까지의 범위. 수식을 아래로 복사할 때 범위가 밀려나지 않도록 키보드 F4키를 눌러 절대 참조($) 를 꼭 걸어줍니다.2: "이 열에 있는 값을 가져와!" → 참조 범위(A:C)에서 '상품명'은 두 번째 열 에 있으므로2를 입력.FALSE: "똑같은 것만 찾아줘!" → 상품 코드와 정확히 일치 하는 값을 찾으라는 명령.
2) D2셀에 '단가' 찾아오기
=VLOOKUP(B2, '상품 정보'!$A$2:$C$4, 3, FALSE)
- 모든 구조는 위와 동일합니다. 단지 우리가 가져오고 싶은 '단가'가 참조 범위에서 세 번째 열 에 있으므로, 열 번호만
3으로 바꿔주면 됩니다.
이제 C2와 D2 셀을 완성한 뒤, 셀 오른쪽 아래 작은 점(채우기 핸들)을 더블클릭하면 모든 주문 목록이 순식간에 채워지는 마법을 경험할 수 있습니다.
4. 실무 예제 2: 점수에 따라 성과 등급 부여하기 (유사 일치
TRUE
)
이번에는 구간별 값을 찾는 '유사 일치' 예제입니다. 직원의 성과 점수에 따라 등급을 매겨보겠습니다.
| [등급 기준표 시트] (A열 오름차순 정렬 필수!) | |
|---|---|
| A열 (점수 하한선) | B열 (성과 등급) |
| 0 | C |
| 70 | B |
| 85 | A |
| 95 | S |
| [성과 평가 시트] | ||
|---|---|---|
| A열 (사원명) | B열 (성과 점수) | C열 (성과 등급) |
| 김하나 | 88 | (여기에 채울 것) |
| 이두리 | 97 | |
| 박세이 | 75 | |
| 최소영 | 65 |
1) C2셀에 '성과 등급' 부여하기
=VLOOKUP(B2, '등급 기준표'!$A$2:$B$5, 2, TRUE)
B2: "이걸로 찾아줘!" → 김하나 사원의 성과 점수 "88"'등급 기준표'!$A$2:$B$5: "여기서 찾아줘!" → '등급 기준표' 범위 (절대 참조 적용)2: "이 열 값을 가져와!" → '성과 등급'은 기준표의 두 번째 열 에 있으므로2TRUE: "비슷한 걸로 찾아줘!" → 유사 일치 옵션- 엑셀은 88점을 기준으로, 88보다 크지 않은 가장 큰 값인 '85' 를 찾습니다.
- 따라서 '85'점에 해당하는 'A' 등급을 반환합니다.
- 최소영 사원의 65점은, 65보다 크지 않은 가장 큰 값인 '0'을 찾아 'C' 등급을 받게 됩니다.
5. "이것만 알면 나도 VLOOKUP 전문가!" - 자주 만나는 오류 TOP 3
VLOOKUP을 사용하다 보면 누구나 오류 메시지를 만나게 됩니다. 당황하지 마세요. 원인만 알면 쉽게 해결할 수 있습니다.
| 오류 메시지 | 원인과 의미 | 해결 방법 |
|---|---|---|
#N/A
(Not Available) |
찾는 값이 참조 범위에 없음. - 오타가 있거나, - 눈에 보이지 않는 공백(스페이스)이 있거나, - 한쪽은 숫자, 한쪽은 텍스트 서식일 경우 발생. |
1. 데이터 클렌징:
TRIM
함수로 양쪽 데이터의 불필요한 공백을 제거합니다. 2. 서식 통일: 데이터 서식을 숫자로 통일합니다. 3. 오류 처리:
IFERROR
함수로 오류를 감싸주면 더 깔끔합니다.
=IFERROR(VLOOKUP(...), "자료 없음")
|
#REF!
(Reference) |
가져올 열 번호가 범위를 벗어남. (예: 참조 범위를 3개 열(
A:C
)로 잡고 4번째 열의 값을 가져오라고 명령) |
가져올 열 번호
가
참조 범위
내에 있는지 확인하고 올바른 열 번호로 수정합니다. |
#VALUE!
|
열 번호에 1보다 작은 값을 입력함. 또는 수식이 손상된 경우. |
가져올 열 번호
는 1 이상의 정수여야 합니다. 수식을 다시 확인합니다. |
6. VLOOKUP을 넘어서: 한계점과 더 강력한 대안
VLOOKUP은 매우 유용하지만 치명적인 단점이 있습니다. 1. 왼쪽을 보지 못함: 무조건 참조 범위의 첫 번째 열 에서만 값을 찾을 수 있습니다. 즉, '사원명'으로 왼쪽에 있는 '사원번호'를 찾을 수 없습니다. 2. 구조 변경에 취약: 참조 범위 중간에 새로운 열을 삽입하거나 삭제하면 열 번호가 밀려서 값이 틀어지거나
#REF!
오류가 발생합니다.
이러한 단점을 완벽히 보완하는 함수들이 있습니다. *
INDEX
+
MATCH
: 방향에 구애받지 않고 값을 찾을 수 있고, 열 삽입/삭제에도 강한 VLOOKUP의 전통적인 상위 호환 조합입니다. *
XLOOKUP
: (Excel 365, 2021 이상 버전 사용자라면 필독!) VLOOKUP과 INDEX/MATCH의 모든 장점을 합친 최종 진화형 함수입니다. 왼쪽 조회도 가능하고, 사용법도 훨씬 직관적입니다. 최신 버전 엑셀 사용자라면 이제 VLOOKUP 대신 XLOOKUP을 사용하는 것을 적극 권장합니다.
오늘 배운 VLOOKUP 함수 하나만 제대로 익혀도 엑셀 업무의 효율은 극적으로 상승합니다. 단순 반복 작업에 쏟던 시간을 아껴 더 중요한 일에 집중하고, 당당하게 '칼퇴'하는 스마트한 직장인이 되어보세요