VLOOKUP 없이 두 Excel 파일을 병합하는 방법 (단계별 가이드)
VLOOKUP 없이 두 Excel 파일을 병합하는 방법은 세 가지가 있습니다. XLOOKUP은 VLOOKUP의 검색 방향 및 일치 문제를 해결해 줍니다. Power Query의 Merge는 실제 조인을 수행하며 파일이 변경되면 자동으로 새로 고침됩니다. AI 데이터 에이전트를 사용하면 자연어로 조인을 설명하고 수식을 완전히 건너뛸 수 있습니다. 어떤 방법이 적합한지는 병합된 테이블이 필요한지, 아니면 그 데이터가 의미하는 해답이 필요한지에 따라 달라집니다.
이러한 작업은 어디서나 흔히 발생합니다. 한 파일에는 고객 목록이 있고 다른 파일에는 주문 내역 내보내기 파일이 있으며, 이 둘을 연결하는 유일한 고리는 이메일 주소나 계정 ID뿐입니다. 유용한 분석을 하기 전에 이 두 데이터를 하나의 뷰로 합쳐야 합니다.
VLOOKUP은 누구나 가장 먼저 찾는 수식이지만, 결국 누구나 한 번쯤은 이 수식 때문에 곤경을 치르곤 합니다. Excel에 대해 얼마나 깊이 고민하고 싶은지에 따라 선택할 수 있는 대안들을 순서대로 소개합니다.
두 파일을 조인한다는 것의 진짜 의미
조인은 공통 키를 사용하여 두 테이블의 행을 일치시킨 다음, 한 테이블의 열을 다른 테이블로 가져오는 작업입니다. 세 가지 결정이 조인의 성격을 정의하며, 이 중 하나라도 잘못되면 겉보기에는 맞아 보이지만 실제로는 틀린 결과가 나옵니다.
어떤 열이 키(key)인가요? 이메일, 주문 ID, SKU, 계정 번호 등이 있습니다. 양쪽 테이블에서 동일한 의미를 가져야 합니다.
일치하지 않는 행은 어떻게 처리하나요? 주문 내역이 없는 고객까지 모두 유지할 것인가요, 아니면 주문한 고객만 유지할 것인가요? 이는 서로 다른 질문이며 답도 다릅니다. Excel은 아무것도 묻지 않고 사용자가 지정한 대로 결과를 보여줍니다.
키가 중복될 수 있나요? 한 명의 고객이 5번 주문했다면 왼쪽에는 1개 행, 오른쪽에는 5개 행이 존재하게 됩니다. 5개의 행을 모두 유지할지, 아니면 1개의 요약된 행으로 만들지에 따라 전체 결과가 완전히 달라집니다.
수식을 작성하기 전에 이 세 가지 질문에 먼저 답해 보세요. 잘못된 조인의 대부분은 수식 오류가 아니라 명확히 정의하지 않은 전제 조건 때문에 발생합니다.
Excel에서 기본적으로 제공하는 두 파일 조인 방법
옵션 1: VLOOKUP, 그리고 이 수식이 계속 오류를 일으키는 이유
VLOOKUP은 범위의 가장 왼쪽 열을 검색한 다음, 지정된 위치 번호에 따라 오른쪽 열의 값을 반환합니다. 이러한 구조적 설계 때문에 네 가지 고질적인 함정이 발생하며, 이는 Microsoft의 VLOOKUP 함수 참조 문서에도 잘 설명되어 있습니다.
- 왼쪽을 검색할 수 없습니다. 찾고자 하는 값의 오른쪽에 키가 있는 경우, 원본 파일의 열 순서를 먼저 변경해야 합니다.
- 열 인덱스가 고정된 숫자로 입력됩니다. 검색 범위 내에 열을 삽입하면 수식은 계속해서 4번째 위치를 가리키게 되며, 이 위치는 이제 다른 필드가 되어 버립니다. 오류는 표시되지 않고 숫자만 조용히 바뀝니다.
- 일치 유형의 기본값이 유사 일치로 설정되어 있습니다. 마지막 인수를 생략하면 VLOOKUP은 데이터가 정렬되어 있다고 가정하고 가장 유사한 값을 찾습니다. 정렬되지 않은 데이터에서는 엉뚱한 값을 정확한 것처럼 반환합니다.
- 첫 번째 일치 항목만 반환합니다. 키가 중복되는 경우 첫 번째 행만 가져오며, 두 번째부터 다섯 번째 행이 존재한다는 경고조차 보여주지 않습니다.
VLOOKUP 자체가 나쁜 것은 아닙니다. 1980년대에 설계된 도구에 데이터베이스 작업을 요구하다 보니 발생하는 문제입니다. 게다가 에러를 요란하게 표시하는 대신 조용히 잘못된 값을 보여주기 때문에 가장 치명적인 방식으로 실패하게 됩니다.
옵션 2: XLOOKUP
XLOOKUP은 현대적인 대체 함수로, 앞서 언급한 네 가지 함정 중 세 가지를 해결합니다. 어느 방향으로든 검색할 수 있으며 기본적으로 정확히 일치하는 값을 찾습니다. 시트에 #N/A 오류를 남기는 대신 적절한 if_not_found 인수를 지정할 수 있습니다. 또한 위치 번호가 아닌 열 범위를 참조하므로 열을 삽입해도 수식이 조용히 깨지는 일이 없습니다. 구문은 Microsoft의 XLOOKUP 참조에서 확인할 수 있습니다.
하지만 여전히 VLOOKUP과 동일한 한계가 존재합니다. 바로 조인이 아니라 '찾기(lookup)' 함수라는 점입니다. 행당 하나의 값만 가져옵니다. 중복된 키는 여전히 첫 번째 일치 항목만 반환하며, 다음 분기에 다른 사람이 열어볼 파일의 수천 개 행에 걸쳐 수식을 계속 유지 관리해야 하는 부담이 있습니다.
옵션 3: Power Query Merge, 진정한 기본 해결책
Excel에서 실제 조인을 수행하고 싶다면 Power Query의 Merge가 정답입니다. 두 파일을 쿼리로 로드하고 Merge Queries를 선택한 다음, 양쪽의 키 열을 지정합니다. 그런 다음 조인 종류를 선택합니다. 왼쪽 외부(left outer)는 왼쪽의 모든 데이터를 유지하고, 내부(inner)는 일치하는 데이터만 유지하며, 전체 외부(full outer)는 양쪽 모두를 유지하고, 안티(anti)는 일치하지 않는 행만 분리합니다.
이 중 안티(anti) 조인은 과소평가되어 있지만 매우 유용합니다. "내 목록의 고객 중 주문이 전혀 없는 고객은 누구인가"라는 질문에 단 한 단계만으로 답을 주는데, 이는 일반적인 lookup 함수로는 구현하기 번거로운 작업입니다. 또한 Merge는 새로 고침이 가능하므로 다음 달에 새 파일이 들어와도 다시 빌드할 필요 없이 동일한 조인 과정을 거쳐 업데이트됩니다.
단점은 학습 곡선이 있다는 것입니다. 쿼리 단계, 확장된 테이블 열, 조인 종류 등은 모두 알아두면 유용하지만, 한 문장으로 물어볼 수 있는 질문을 해결하기 위해 대여섯 가지의 복잡한 개념을 먼저 이해해야 합니다.
세 가지 기본 방법 모두가 한계에 부딪히는 지점
Excel의 모든 기본 방식은 동일한 세 가지 한계를 공유합니다.
키 데이터가 깨끗한 경우는 거의 없습니다. john@acme.com과 John@Acme.com 은 동일한 고객이지만 정확히 일치하는 값을 찾는 방식으로는 서로 다른 데이터로 인식됩니다. 실제 데이터의 키에는 뒤공백, 일관되지 않은 대소문자, 텍스트로 저장된 숫자, 이전 내보내기 파일에서 묻어온 불필요한 아포스트로피 등이 포함되어 있습니다. 모든 기본 방식은 키를 먼저 표준화해야 하며, 일치율이 60%에 그치는 이유가 바로 이 때문이라는 사실을 친절하게 알려주지 않습니다.
병합된 테이블 자체가 최종 목적지가 아닙니다. 사람들은 단순히 병합된 시트를 원하는 것이 아닙니다. 어떤 세그먼트가 성장하고 있는지, 어떤 계정이 이탈했는지, 어떤 SKU가 마진을 남기고 있는지 알고 싶어 합니다. 조인은 단지 배관 작업일 뿐이며, 대부분의 시간은 이 배관 작업에 소요됩니다.
다음 담당자가 수식을 그대로 물려받게 됩니다. 중첩된 lookup 함수로 가득 찬 통합 문서는 유지 관리의 골칫거리입니다. 열 하나만 이동해도 작동을 멈추기 때문입니다.
Powerdrill Bloom으로 두 Excel 파일을 조인하는 방법
Powerdrill Bloom은 조인을 먼저 완료해야 하는 전처리 단계가 아니라 질문의 일부로 취급합니다. 두 파일을 업로드하고 서로 연결되는 요소를 말해주면, 행을 일치시키고 일치율을 보고한 다음 분석으로 바로 이어집니다.
1단계: 두 파일 업로드하기
두 통합 문서를 하나의 워크스페이스에 끌어다 놓습니다. Bloom은 Excel, CSV, TSV, PDF 파일을 읽고 가져오는 과정에서 자동으로 데이터를 정제하므로, 뒤공백이나 대소문자가 섞인 키가 누락되지 않고 정상적으로 처리됩니다.
키가 왼쪽에 오도록 열 순서를 바꿀 필요도 없고, 두 파일의 레이아웃이 일치할 필요도 없습니다.
2단계: 자연어로 조인 설명하기
두 파일이 어떻게 연결되는지, 그리고 어떤 결과를 원하는지 말해 보세요. "이메일 주소를 기준으로 주문 파일과 고객 파일을 매칭해 줘. 주문이 없는 고객도 모두 유지하고, 매칭되지 않은 고객이 몇 명인지 알려줘"라고 입력하는 것만으로 충분합니다.
그리고 이어서 질문을 계속할 수 있습니다. 바로 이 부분이 일반적인 lookup 함수로는 불가능한 영역입니다. "이제 고객 세그먼트별 매출을 보여주고, 지난 분기 대비 하락 폭이 가장 큰 10개 계정을 나열해 줘." 조인과 분석이 한 번에 이루어집니다.
매달 반복되는 작업이라면 이를 에이전트 스킬로 저장하여 매번 다시 입력할 필요 없이 다음 달 파일에 그대로 재실행할 수 있습니다.
3단계: 조인된 결과, 차트 또는 슬라이드 내보내기
조인된 테이블을 파일로 가져오거나, 차트를 추출하거나, 클릭 한 번으로 전체 캔버스를 슬라이드(Professional, Business, Fancy 스타일 중 선택)로 변환하여 PowerPoint나 Notion으로 내보낼 수 있습니다.
이 마지막 옵션이야말로 오후 업무 시간을 획기적으로 줄여주는 기능입니다. 조인 작업 자체가 최종 결과물은 아니기 때문입니다.
수식을 아끼는 것보다 이것이 더 중요한 이유
중요한 것은 수식을 쓰느냐 안 쓰느냐의 비교가 아닙니다. 데이터에 문제가 있을 때 각 방식이 어떻게 대처하는가입니다.
| VLOOKUP | XLOOKUP | Power Query Merge | Powerdrill Bloom | |
|---|---|---|---|---|
| 키 위치 제약 없음 | No | Yes | Yes | Yes |
| 열 삽입 시 수식 유지 | No | Yes | Yes | Yes |
| 중복 키 정상 처리 | No | No | Yes | Yes |
| 일치하지 않는 행 분리 | 수동 | 수동 | Yes (안티 조인) | Yes |
| 불완전한 키 자동 정제 | No | No | 수동 단계 필요 | Yes |
| 일치율 보고 | No | No | No | Yes |
| 질문에 대한 즉각적인 분석 제공 | No | No | No | Yes |
| 필요한 기술 | 수식 작성 | 수식 작성 | 쿼리 편집기 사용 | 자연어 입력 |
이 표를 객관적으로 살펴보면 결론은 "Excel이 쓸모없어졌다"가 아닙니다. Excel의 도구들은 병합된 테이블을 만드는 데 초점이 맞춰져 있지만, 테이블을 병합하는 것은 전체 작업 중 아주 일부분에 불과하다는 점입니다.
스프레드시트 조인 시 권장 사항
매칭 전에 키 데이터를 먼저 표준화하세요
공백을 제거하고, 대소문자를 통일하며, 양쪽의 ID가 동일한 데이터 유형으로 저장되어 있는지 확인하세요. 정제되지 않은 키로 조인을 수행하면 오류가 발생하지 않고 조용히 누락될 뿐입니다. 60%의 일치율은 데이터의 문제가 아니라 비즈니스 분석 결과처럼 오해하기 쉽습니다.
일치하지 않는 행의 개수를 항상 확인하세요
일치하지 않는 데이터 세트가 가장 흥미로운 인사이트를 담고 있는 경우가 많습니다. 주문이 없는 고객, 고객 정보가 없는 주문, 한 시스템에만 존재하고 다른 시스템에는 없는 SKU 등 운영상의 문제가 바로 여기에 숨어 있습니다. 이에 대한 자세한 내용은 데이터 파일 병합 방법 가이드에서 다루고 있습니다.
조인 전이 아니라 조인 후에 행 개수를 확인하세요
왼쪽 파일에 4,000개의 행이 있었는데 조인 결과가 11,000개가 되었다면, 키가 중복되어 데이터가 부풀려진 것입니다. 의도한 결과라면 괜찮지만, 그렇지 않다면 심각한 문제가 될 수 있습니다. 특히 매출 열의 합계를 구하기 전이라면 더욱 그렇습니다.
데이터를 집계하기 전에 일대다(one-to-many) 관계를 어떻게 처리할지 결정하세요
한 고객이 5개의 주문을 가지고 있다면, 5개의 행을 그대로 둘지 아니면 1개의 행으로 집계할지 결정해야 합니다. 부풀려진 데이터 상태에서 매출 합계를 구하면 중복 계산이 발생합니다. 이 한 가지 실수가 수식 오류보다 더 많은 잘못된 대시보드를 만들어냅니다.
피해야 할 흔한 실수들
- ID 대신 이름으로 조인하는 경우. 정확히 일치하는 값을 찾는 방식에서는 "Acme Corp", "Acme Corp.", "ACME Corporation"을 서로 다른 세 개의 회사로 인식합니다.
- VLOOKUP의 네 번째 인수를 생략하는 경우. 기본값이 유사 일치로 설정되어 있어, 정렬되지 않은 데이터에서 오류를 표시하지 않고 잘못된 값을 반환합니다.
#N/A를 0으로 해석하는 경우. 일치하는 데이터가 없는 것과 실제 값이 0인 것은 완전히 다른 의미입니다. 모든 수식을IFERROR(...,0)로 감싸버리면 이 차이가 가려지게 됩니다.- 중복을 제거하기 전에 조인하는 경우. 어느 한쪽에라도 중복 키가 있으면 조인 시 데이터가 곱절로 늘어납니다. 먼저 데이터를 정제한 후 조인하세요.
- 일대다 조인 후 합계를 구하는 경우. 전형적인 중복 계산 오류입니다. 합계 데이터를 신뢰하기 전에 항상 행 개수를 확인하세요.
결론
키 데이터가 깨끗하고 일회성으로 빠르게 값을 가져와야 할 때는 XLOOKUP이 가장 적합하며 30초면 충분합니다. 안정적인 파일에서 반복적으로 조인해야 한다면 Power Query Merge를 구축하고 안티 조인을 사용하여 일치하지 않는 데이터를 걸러내세요. 키 데이터가 지저분하거나, 키가 중복되거나, 병합된 시트보다 차트와 슬라이드가 실제로 필요한 경우에는 수식을 작성하는 대신 조인 과정을 말로 설명해 보세요.
비용 부담 없이 직접 가지고 계신 두 파일로 테스트해 볼 수 있습니다. Powerdrill Bloom은 무료 플랜에서도 매일 새로 고침되는 1,000 크레딧을 제공합니다. Excel AI assistant 및 merge CSV files 페이지에서 동일한 워크플로우를 확인할 수 있으며, analyzing Excel with AI 페이지에서는 단일 파일 분석 버전을 다룹니다.
자주 묻는 질문
두 Excel 파일을 병합할 때 VLOOKUP 대신 무엇을 사용할 수 있나요?
XLOOKUP은 VLOOKUP을 직접 대체하는 함수로, 검색 방향의 제약이 없고 기본적으로 정확히 일치하는 값을 찾으며 열을 삽입해도 수식이 깨지지 않는 등 VLOOKUP의 가장 큰 단점들을 해결합니다. 두 테이블 간의 진정한 조인을 원한다면 중복 키를 처리하고 일치하지 않는 행을 분리할 수 있는 Power Query의 Merge가 더 나은 기본 도구입니다.
파일 조인 시 Power Query가 VLOOKUP보다 더 나은가요?
반복적인 작업이라면 그렇습니다. Power Query는 선택 가능한 조인 종류를 통해 실제 조인을 수행하고, 원본 파일이 변경되면 새로 고침되며, 통합 문서에 수천 개의 수식을 남기지 않습니다. 정제된 하나의 열에서 일회성으로 데이터를 빠르게 가져올 때는 여전히 VLOOKUP이 더 빠릅니다.
열 이름이 서로 다른 두 Excel 파일은 어떻게 조인하나요?
Power Query를 사용하면 양쪽에서 서로 다른 키 열을 선택할 수 있으므로 열 이름이 일치하지 않아도 값만 같으면 조인할 수 있습니다. AI 데이터 에이전트는 한 걸음 더 나아가 파일을 읽는 동안 열을 자동으로 매칭하고, 양쪽 데이터가 일치하지 않는 부분을 보고해 줍니다.
VLOOKUP이 오류 대신 잘못된 값을 반환하는 이유는 무엇인가요?
대부분 네 번째 인수를 생략했기 때문입니다. 인수를 생략하면 VLOOKUP은 유사 일치를 수행하는데, 이는 데이터가 정렬되어 있다고 가정하고 그렇지 않은 경우 찾을 수 있는 가장 가까운 작은 값을 반환합니다. 정확히 일치하는 값을 찾으려면 마지막 인수를 FALSE로 설정하세요.
수식을 전혀 사용하지 않고 두 Excel 파일을 조인할 수 있나요?
네, 가능합니다. Power Query의 Merge는 쿼리 편집기를 사용하긴 하지만 Excel 내부에서 수식 없이 조인할 수 있는 방법입니다. AI 데이터 에이전트를 사용하면 두 파일을 업로드하고 한 문장으로 조인을 설명하기만 하면 되므로 수식이나 쿼리 단계가 전혀 필요하지 않습니다.