VLOOKUP 오류 없이 두 표를 합치는 현실적인 기준
실무에서 두 표를 합치는 일은 자주 발생합니다. 예를 들어 주문 내역 표에는 상품코드와 주문수량이 있고, 상품 기준표에는 상품코드별 상품명과 단가가 있습니다. 이때 주문 내역에 상품명과 단가를 붙이면 보고서 작성이 훨씬 빨라집니다.
많은 직장인이 이 작업을 VLOOKUP으로 처리하지만, 열 위치가 바뀌거나 코드 형식이 다르거나 중복값이 있으면 오류가 쉽게 납니다. 이번 글에서는 VLOOKUP 설명은 길게 하지 않고, 바로 실무에서 쓸 수 있는 INDEX-MATCH, XLOOKUP, 파워쿼리 3가지 방법을 비교해 보겠습니다.
예시 데이터: 주문표와 상품 기준표
아래와 같은 두 표가 있다고 가정합니다.
| 주문번호 | 상품코드 | 수량 |
|---|---|---|
| O-1001 | A001 | 3 |
| O-1002 | A003 | 1 |
| O-1003 | A005 | 2 |
그리고 상품 기준표는 다음과 같습니다.
| 상품코드 | 상품명 | 단가 |
|---|---|---|
| A001 | 무선 마우스 | 15000 |
| A003 | USB 허브 | 22000 |
| A004 | 노트북 거치대 | 31000 |
목표는 주문표에 상품명과 단가를 붙이는 것입니다. 여기서 주문표의 A005는 상품 기준표에 없으므로 오류가 발생할 수 있습니다. 실무에서는 이런 누락 코드가 생각보다 자주 나옵니다.
방법 1. INDEX-MATCH: 구버전 엑셀까지 고려해야 할 때
회사 PC가 오래된 엑셀 버전을 쓰고 있거나, 여러 부서가 같은 파일을 열어야 한다면 INDEX-MATCH가 안정적인 선택입니다. Microsoft 365가 아니어도 비교적 넓게 사용할 수 있고, 기준표의 열 순서가 바뀌어도 VLOOKUP보다 덜 흔들립니다.
입력 예시
주문표의 D열에 상품명을 붙인다고 가정합니다. D2 셀에 아래 수식을 입력합니다.
=INDEX($G$2:$G$4, MATCH(B2, $F$2:$F$4, 0))
여기서 B2는 주문표의 상품코드, F열은 상품 기준표의 상품코드, G열은 가져올 상품명입니다.
실제 결과
| 주문번호 | 상품코드 | 수량 | 상품명 |
|---|---|---|---|
| O-1001 | A001 | 3 | 무선 마우스 |
| O-1002 | A003 | 1 | USB 허브 |
| O-1003 | A005 | 2 | #N/A |
A005는 기준표에 없기 때문에 #N/A가 나옵니다. 이 오류를 그대로 두면 피벗테이블이나 보고서 계산에서 문제가 생길 수 있습니다. 실무용으로는 IFERROR를 함께 쓰는 편이 안전합니다.
=IFERROR(INDEX($G$2:$G$4, MATCH(B2, $F$2:$F$4, 0)), “기준표 없음”)
이렇게 바꾸면 A005 결과가 #N/A 대신 기준표 없음으로 표시됩니다. 검토 담당자가 누락 데이터를 바로 찾을 수 있습니다.
INDEX-MATCH가 맞는 상황
- 회사 내 엑셀 버전이 제각각일 때
- XLOOKUP을 지원하지 않는 구버전 엑셀 사용자가 있을 때
- 파일을 외부 협력사와 주고받아야 할 때
- 수식 기반으로 결과를 즉시 확인해야 할 때
방법 2. XLOOKUP: Microsoft 365나 최신 엑셀이라면 가장 간단한 선택
최신 엑셀을 사용한다면 XLOOKUP이 가장 읽기 쉽습니다. 수식 구조가 자연스럽고, 오류 시 표시할 문구까지 한 번에 넣을 수 있습니다.
입력 예시
주문표의 D2 셀에 상품명을 가져오려면 아래처럼 입력합니다.
=XLOOKUP(B2, $F$2:$F$4, $G$2:$G$4, “기준표 없음”)
같은 방식으로 단가를 가져오려면 반환 범위만 H열로 바꾸면 됩니다.
=XLOOKUP(B2, $F$2:$F$4, $H$2:$H$4, “기준표 없음”)
실제 결과
| 주문번호 | 상품코드 | 수량 | 상품명 | 단가 |
|---|---|---|---|---|
| O-1001 | A001 | 3 | 무선 마우스 | 15000 |
| O-1002 | A003 | 1 | USB 허브 | 22000 |
| O-1003 | A005 | 2 | 기준표 없음 | 기준표 없음 |
XLOOKUP의 장점은 오류 처리가 수식 안에 포함된다는 점입니다. 별도로 IFERROR를 감싸지 않아도 누락값을 표시할 수 있습니다.
자주 재현되는 오류
다음 수식을 입력했는데 결과가 모두 기준표 없음으로 나오는 경우가 있습니다.
=XLOOKUP(B2, $F$2:$F$4, $G$2:$G$4, “기준표 없음”)
겉으로는 B2와 F2의 값이 모두 A001처럼 보이지만 실제로는 한쪽에 공백이 들어간 경우입니다.
| 셀 | 보이는 값 | 실제 상태 |
|---|---|---|
| B2 | A001 | 정상 |
| F2 | A001 | 뒤에 공백 포함 |
이때는 기준표의 상품코드를 먼저 정리하는 보조열을 만듭니다.
=TRIM(F2)
상품코드가 숫자처럼 보이지만 실제로는 텍스트인 경우도 있습니다. 예를 들어 한쪽은 1001, 다른 한쪽은 ‘1001 형태라면 매칭이 실패할 수 있습니다. 이때는 양쪽 형식을 맞춘 뒤 합치는 것이 안전합니다.
XLOOKUP이 맞는 상황
- Microsoft 365 또는 Excel 2021 이상을 사용 중일 때
- 수식을 다른 직원이 읽어도 쉽게 이해해야 할 때
- 누락값을 “기준표 없음”처럼 명확히 표시하고 싶을 때
- 데이터 행 수가 수천~수만 줄 정도일 때
방법 3. 파워쿼리: 파일이 크고 반복 작업이라면 가장 실무적
주문 데이터가 매일 내려오고, 기준표도 계속 갱신된다면 수식보다 파워쿼리가 더 적합합니다. 한 번만 병합 규칙을 만들어 두면 다음부터는 새 파일을 넣고 새로 고침만 하면 됩니다.
따라 하는 순서
- 주문표 범위를 선택하고 데이터 > 테이블/범위에서를 클릭합니다.
- 상품 기준표도 같은 방식으로 파워쿼리에 불러옵니다.
- 주문 쿼리에서 홈 > 쿼리 병합을 선택합니다.
- 첫 번째 표는 주문표, 두 번째 표는 상품 기준표로 선택합니다.
- 두 표 모두 상품코드 열을 클릭합니다.
- 조인 종류는 보통 왼쪽 외부를 선택합니다.
- 병합된 열을 펼쳐 상품명과 단가만 선택합니다.
- 닫기 및 로드를 클릭합니다.
여기서 왼쪽 외부를 선택하는 이유는 주문표의 모든 행을 유지하기 위해서입니다. 기준표에 없는 상품코드가 있더라도 주문 행 자체가 사라지지 않습니다. 대신 상품명과 단가가 비어 있으므로 누락 데이터를 확인할 수 있습니다.
입력 → 결과 장면
입력 상태에서 주문표에는 A005가 있지만 상품 기준표에는 A005가 없습니다. 파워쿼리 병합 후 결과는 다음처럼 나옵니다.
| 주문번호 | 상품코드 | 수량 | 상품명 | 단가 |
|---|---|---|---|---|
| O-1001 | A001 | 3 | 무선 마우스 | 15000 |
| O-1002 | A003 | 1 | USB 허브 | 22000 |
| O-1003 | A005 | 2 | null | null |
파워쿼리에서 null은 기준표에 매칭되는 값이 없다는 뜻입니다. 보고서용으로는 null을 그대로 두기보다 값 바꾸기 기능으로 “기준표 없음”으로 바꾸는 편이 읽기 쉽습니다.
파일 크기별 선택 기준
세 방법은 모두 두 표를 합칠 수 있지만, 파일 크기와 반복 여부에 따라 선택이 달라집니다.
| 상황 | 추천 방법 | 이유 |
|---|---|---|
| 수백~수천 행, 일회성 작업 | XLOOKUP | 수식이 짧고 오류 문구를 바로 넣을 수 있음 |
| 구버전 엑셀 사용자와 공유 | INDEX-MATCH | 호환성이 비교적 넓음 |
| 수만 행 이상, 매일 반복 | 파워쿼리 | 새로 고침 기반으로 반복 작업을 줄일 수 있음 |
| 열 위치가 자주 바뀌는 기준표 | XLOOKUP 또는 파워쿼리 | VLOOKUP처럼 열 번호에 크게 의존하지 않음 |
| 파일이 자주 느려짐 | 파워쿼리 | 대량 수식보다 관리가 쉬움 |
실무 기준으로 보면, 한 번만 처리할 작은 파일은 XLOOKUP, 호환성이 필요한 공유 파일은 INDEX-MATCH, 매주 또는 매일 반복되는 대용량 파일은 파워쿼리가 적합합니다.
대부분 놓치는 점: 합치기 전에 키 값을 먼저 점검하기
두 표를 합칠 때 가장 많이 놓치는 부분은 수식 자체가 아니라 상품코드, 거래처코드, 사번 같은 기준값의 품질입니다. 기준값이 흔들리면 어떤 방법을 써도 결과가 틀어집니다.
합치기 전에 아래 4가지를 먼저 확인하면 오류를 크게 줄일 수 있습니다.
- 앞뒤 공백이 있는가: TRIM으로 확인
- 숫자와 텍스트 형식이 섞였는가
- 기준표에 중복 코드가 있는가
- 주문표에는 있는데 기준표에는 없는 코드가 있는가
특히 기준표의 중복 코드는 조심해야 합니다. 예를 들어 A001 코드가 기준표에 두 번 있으면 XLOOKUP은 보통 첫 번째 값을 가져옵니다. 사용자는 정상 결과처럼 보지만 실제로는 잘못된 단가를 붙였을 수 있습니다.
중복 코드 확인 수식
상품 기준표의 F열에 상품코드가 있다면 옆 열에 아래 수식을 넣어 중복 여부를 확인합니다.
=COUNTIF($F$2:$F$1000, F2)
결과가 2 이상이면 같은 코드가 여러 번 있다는 뜻입니다. 이 상태에서 바로 병합하지 말고 기준표를 먼저 정리해야 합니다.
버전 호환성 정리
| 기능 | 권장 엑셀 환경 | 주의할 점 |
|---|---|---|
| INDEX-MATCH | 대부분의 데스크톱 엑셀 | 수식이 길어 초보자는 해석이 어려울 수 있음 |
| XLOOKUP | Microsoft 365, Excel 2021 이상 | 구버전 엑셀에서는 함수가 작동하지 않을 수 있음 |
| 파워쿼리 | Excel 2016 이상 또는 Microsoft 365 | 처음에는 불러오기와 병합 절차를 익혀야 함 |
회사 파일은 혼자 쓰는 파일과 다릅니다. 본인 PC에서는 XLOOKUP이 잘 되더라도, 다른 부서원이 구버전 엑셀로 열면 함수가 깨질 수 있습니다. 공유 파일이라면 먼저 사용 환경을 확인하는 것이 좋습니다.
바로 적용할 추천 순서
- 작은 파일이면 XLOOKUP으로 먼저 처리합니다.
- 공유 대상 중 구버전 엑셀 사용자가 있으면 INDEX-MATCH로 바꿉니다.
- 매번 같은 작업을 반복한다면 파워쿼리로 전환합니다.
- 결과를 확인할 때는 #N/A, 기준표 없음, null 행을 따로 필터링합니다.
- 최종 보고서 작성 전 기준표 중복 코드를 반드시 확인합니다.
처음부터 가장 복잡한 방법을 고를 필요는 없습니다. 다만 매일 같은 파일을 열고 같은 수식을 복사하고 있다면, 그때는 파워쿼리로 바꾸는 편이 업무 시간을 줄이는 데 더 현실적입니다.
FAQ
Q1. 회사 데이터로 ChatGPT에 엑셀 수식을 물어봐도 되나요?
가능은 하지만 원본 고객명, 거래처명, 사번, 전화번호, 이메일, 매출액 같은 민감 정보는 그대로 입력하지 않는 것이 안전합니다. 예를 들어 “A거래처”처럼 익명화하거나, 실제 숫자를 임의 숫자로 바꾼 샘플 표를 만들어 질문하는 방식을 권장합니다. 회사의 보안 규정이나 AI 사용 지침이 있다면 그 기준을 먼저 따라야 합니다.
Q2. XLOOKUP이 있는데도 INDEX-MATCH를 배워야 하나요?
최신 엑셀만 사용하는 조직이라면 XLOOKUP만으로도 충분한 경우가 많습니다. 다만 파일을 여러 부서, 외부 협력사, 오래된 PC 사용자와 공유한다면 INDEX-MATCH를 알아두는 것이 좋습니다. 함수 선택은 실력 문제가 아니라 파일을 열 사람의 환경 문제입니다.
Q3. 파워쿼리는 수식보다 항상 빠른가요?
항상 그렇지는 않습니다. 작은 표에서는 XLOOKUP이 더 빠르고 간단합니다. 파워쿼리는 행 수가 많거나, 여러 파일을 반복해서 합치거나, 매번 같은 전처리를 해야 할 때 효과가 커집니다. 수천 행 이하의 일회성 작업이라면 수식이 더 효율적일 수 있고, 수만 행 이상 반복 작업이라면 파워쿼리를 검토하는 편이 좋습니다.