엑셀에서 데이터를 다루다 보면 어느 순간 꼭 마주치는 게 VLOOKUP 함수입니다. 두 개의 표를 연결하거나, 수천 행 데이터에서 특정 값을 찾아야 할 때 이 함수가 없으면 진짜 손으로 다 찾아야 하거든요. 처음엔 어렵게 느껴지지만 한 번 이해하면 그다음부터는 생각보다 쉽습니다.
VLOOKUP 함수 기본 구조 이해하기
엑셀 VLOOKUP 함수는 세로(Vertical) 방향으로 데이터를 찾는 함수입니다. 찾을 값을 기준으로 지정된 표에서 같은 행에 있는 다른 열의 값을 불러오는 방식이죠.
기본 문법은 이렇습니다.
=VLOOKUP(찾는값, 참조범위, 열번호, [일치방법])
- 찾는값 – 기준이 되는 값. 셀 참조 또는 직접 입력
- 참조범위 – 데이터를 찾을 표 전체 범위
- 열번호 – 참조범위에서 몇 번째 열의 값을 가져올지
- 일치방법 – TRUE면 유사 일치, FALSE면 정확 일치 (보통 FALSE 사용)
처음 배울 때 제일 헷갈리는 게 열번호인데, 참조범위 내에서의 순서입니다. 전체 스프레드시트의 열 번호가 아니에요. 범위를 A:D로 잡으면 A열이 1, B열이 2, C열이 3, D열이 4가 됩니다.
일치방법 TRUE vs FALSE
TRUE(또는 1)는 유사 일치로, 정확한 값이 없으면 가장 가까운 작은 값을 반환합니다. 성적 등급 계산 같은 구간 데이터에 씁니다. FALSE(또는 0)는 정확 일치로 일반적인 코드나 이름 검색에 사용합니다.
VLOOKUP 실무 예제 – 직원 정보 찾기
가장 흔한 실무 예제를 들어볼게요. 직원 명단 표(A열-사원번호, B열-이름, C열-부서, D열-연봉)가 있고, 다른 시트에서 사원번호를 입력하면 이름과 부서가 자동으로 나오게 하고 싶다면 어떻게 할까요.
이름을 불러오는 수식은 이렇습니다. =VLOOKUP(A2, 직원명단!A:D, 2, FALSE)
여기서 A2는 검색할 사원번호가 있는 셀, 직원명단!A:D는 직원 정보가 있는 시트의 A열부터 D열 전체, 2는 이름이 있는 두 번째 열, FALSE는 정확히 일치하는 값만 가져오라는 의미입니다.
처음 회사 들어왔을 때 선배한테 VLOOKUP 쓰는 법 물어봤더니 “그냥 쓰다 보면 됩니다”라는 답변만 들었던 기억이 납니다. 지금 생각하면 설명하기가 귀찮았던 거겠죠. 사실 한 번 해보면 정말 금방 이해됩니다.
| 상황 | 수식 예시 | 설명 |
|---|---|---|
| 이름으로 이메일 찾기 | =VLOOKUP(A2,B:E,4,FALSE) |
B열이 이름, E열(4번째)이 이메일 |
| 상품코드로 가격 찾기 | =VLOOKUP(C2,가격표!A:C,3,FALSE) |
다른 시트의 가격표 참조 |
| 점수로 등급 분류 | =VLOOKUP(B2,등급기준!A:B,2,TRUE) |
유사일치로 구간 값 반환 |
| 주문번호로 배송상태 확인 | =VLOOKUP(D2,배송!A:D,4,FALSE) |
배송 시트 연동 |
VLOOKUP 오류 처리하기 – #N/A 에러 없애는 법
VLOOKUP을 쓰다 보면 #N/A 에러를 자주 만나게 됩니다. 찾는 값이 참조 범위에 없을 때 나타나는 오류인데, 이 에러가 그대로 표시되면 보기도 싫고 다른 계산에도 영향을 줍니다.
IFERROR 함수를 감싸면 해결됩니다. 아래처럼 쓰면 값을 못 찾았을 때 빈 칸이나 원하는 텍스트를 표시할 수 있어요.
=IFERROR(VLOOKUP(A2,B:E,3,FALSE),"없음")
이렇게 하면 찾는 값이 있으면 결과를 표시하고, 없으면 “없음”이라고 나옵니다. 빈 칸으로 만들고 싶다면 “없음” 대신 “”(빈 따옴표)를 넣으면 됩니다.
VLOOKUP 자주 나오는 오류 유형
찾는 값 없음. IFERROR 감싸기
열번호가 범위 초과. 범위 확인
데이터 형식 불일치. 숫자↔텍스트
VLOOKUP 고급 활용 – 여러 조건, 역방향 검색
VLOOKUP의 한계 중 하나가 왼쪽 방향 검색이 안 된다는 점입니다. 찾는 값이 항상 참조 범위의 첫 번째 열에 있어야 하고, 오른쪽으로만 데이터를 가져올 수 있죠.
역방향 검색이 필요할 때는 INDEX + MATCH 조합을 씁니다. 하지만 Excel 2021 이상 또는 Microsoft 365 버전이라면 XLOOKUP을 사용하면 훨씬 편합니다. XLOOKUP은 방향 제한이 없고 여러 값을 동시에 반환할 수도 있거든요.
▲ 여러 조건으로 찾아야 할 때는 두 열을 & 연산자로 합친 보조 열을 만들어 단일 키로 변환하는 방법을 씁니다. 예를 들어 “이름+부서”를 합친 열을 따로 만들고 그 열을 기준으로 VLOOKUP을 적용하는 식이죠.
이걸 처음 배울 때 왜 이렇게 복잡하게 해야 하나 싶었는데, 결국 XLOOKUP이나 파워쿼리로 넘어가는 게 더 나은 방향이긴 합니다. 그래도 VLOOKUP은 어디서나 통하는 함수라 알아두면 분명히 써먹을 때가 옵니다.
VLOOKUP 속도 느릴 때 해결 방법
데이터가 수만 행 이상이면 VLOOKUP이 느려지는 문제가 생깁니다. 매번 전체 범위를 스캔하기 때문입니다. 몇 가지 방법으로 속도를 개선할 수 있습니다.
첫째, 전체 열 참조(A:D) 대신 실제 데이터가 있는 범위($A$2:$D$1000)로 지정하면 불필요한 범위 스캔을 줄일 수 있습니다. 절대 참조($)를 쓰면 수식을 복사할 때 범위가 틀어지지 않아서 좋습니다.
둘째, 데이터 정렬과 TRUE 일치 조합입니다. 참조 범위를 오름차순으로 정렬하고 일치 방법을 TRUE로 설정하면 이진 검색이 적용되어 훨씬 빠릅니다. 물론 정확 일치가 필요한 경우엔 사용할 수 없는 방법이죠.
셋째, VLOOKUP 대신 XLOOKUP이나 INDEX/MATCH를 사용하는 것도 속도 향상에 도움이 됩니다. 특히 파일 크기가 크고 수식이 많다면 파워쿼리로 전환하는 것도 고려해볼 만합니다.
자주 묻는 질문 FAQ
Q. VLOOKUP 함수에서 같은 값이 여러 개 있으면 어떻게 되나요?
VLOOKUP은 첫 번째로 찾은 값만 반환합니다. 중복 데이터가 있는 경우 모든 결과를 가져오려면 FILTER 함수나 파워쿼리를 사용해야 합니다.
Q. VLOOKUP으로 왼쪽 열 데이터를 가져올 수 없나요?
기본 VLOOKUP은 찾는 값 기준으로 오른쪽 열만 참조합니다. 왼쪽 데이터를 가져오려면 INDEX+MATCH 조합이나 XLOOKUP(Excel 2021 이상)을 사용하세요.
Q. VLOOKUP 함수에서 대소문자를 구분해서 찾을 수 있나요?
기본 VLOOKUP은 대소문자를 구분하지 않습니다. 대소문자 구분이 필요하다면 EXACT 함수와 배열 수식을 조합하거나, 데이터를 통일시킨 후 사용하는 방법을 권장합니다.
Q. 다른 파일(워크북)의 데이터를 VLOOKUP으로 참조할 수 있나요?
참조 파일이 열려 있는 경우에는 외부 파일 경로를 포함한 범위를 지정해서 사용할 수 있습니다. 다만 파일이 닫혀 있으면 속도가 느려지거나 오류가 발생할 수 있어 같은 파일 내에서 관리하는 것이 안전합니다.
Q. VLOOKUP 대신 XLOOKUP을 써야 할 시점은 언제인가요?
Excel 2021이나 Microsoft 365를 사용 중이라면 XLOOKUP이 훨씬 편합니다. 역방향 검색, 다중 결과 반환, 기본 오류 처리 기능이 내장되어 있어서 VLOOKUP의 불편함 대부분을 해결합니다. 다만 팀 내 구버전 Excel 사용자가 있다면 호환성 때문에 VLOOKUP을 계속 써야 하는 경우가 있습니다.