- 엑셀의 VLOOKUP 함수는 오른쪽 방향으로만 검색이 가능하고 열 삽입/삭제시 수식이 깨지는 등의 한계가 있습니다.
- XLOOKUP 함수는 왼쪽, 오른쪽, 위, 아래 모든 방향으로 검색이 가능하며 열 삽입/삭제시에도 수식이 깨지지 않는 등의 장점을 가지고 있습니다.
- XLOOKUP 함수를 사용하면 실무 시나리오에서 발생하는 다양한 문제를 효율적으로 해결할 수 있으며, 기본 구문과 예제를 통해 쉽게 사용할 수 있습니다.
많은 직장인과 컴활 실무 준비생들이 엑셀에서 가장 먼저 배우는 대표적인 찾기 함수는 단연 VLOOKUP입니다. 하지만 실무에서 VLOOKUP을 사용하다 보면 갑자기 발생하는 #N/A 에러, 열을 추가했을 때 기존 수식이 깨지는 현상, 그리고 왼쪽 방향으로는 데이터를 찾지 못하는 한계 때문에 스트레스를 받았던 경험이 한 번쯤은 있으실 겁니다.
마이크로소프트는 이러한 기존 찾기 함수들의 고질적인 한계를 해결하기 위해 오피스 365(Excel 2021 이상) 버전에 혁신적인 엑셀 XLOOKUP 함수를 도입했습니다.
이 글에서는 왜 이제는 VLOOKUP을 내려놓고 엑셀 XLOOKUP을 써야 하는지, 두 함수의 차이점을 완벽하게 비교하고 실무에서 바로 써먹을 수 있는 시나리오별 예제를 통해 생산성을 10배 올리는 방법을 소개합니다.
📊 VLOOKUP XLOOKUP 비교 분석 한눈에 보기
본격적인 내용에 앞서, 두 엑셀 함수의 핵심 차이점을 표로 빠르게 정리해 보겠습니다.
| 기능 비교 항목 | VLOOKUP 함수 | XLOOKUP 함수 |
|---|---|---|
| 기본 검색 방향 | 오른쪽 방향만 가능 (기준열이 무조건 첫 열) | 왼쪽, 오른쪽, 위, 아래 모두 가능 |
| 열 삽입/삭제 영향 | 열 번호(Index)가 고정되어 수식이 깨짐 | 셀 범위를 직접 참조하여 열 추가에도 영향 없음 |
| 기본 일치 옵션 | 기본값이 ‘유사 일치’ (0이나 FALSE 생략 시 에러 위험) | 기본값이 ‘정확히 일치’ (생략 가능) |
| 에러 처리 (오류값) | IFERROR 함수를 추가로 중첩해야 함 |
함수 내부의 네 번째 인자(if_not_found)로 자체 해결 |
| 가로 검색 (HLOOKUP) | 불가 (별도의 HLOOKUP 함수 사용 필요) |
가로, 세로 검색 모두 지원 (HLOOKUP 대체) |
💼 실무 시나리오 1. “기준열 왼쪽에 있는 데이터를 가져와야 합니다”
Q. 부서원의 이름(C열)을 기준으로 사원번호(B열)를 조회해야 합니다. VLOOKUP을 쓰니 작동하지 않는데 어떻게 해야 하나요?
A. XLOOKUP을 사용하면 찾고자 하는 기준열이 어디에 있든 상관없이 왼쪽 방향의 데이터도 단 3초 만에 찾을 수 있습니다.
VLOOKUP의 한계와 XLOOKUP의 해결책
VLOOKUP은 반드시 찾고자 하는 값(Lookup_value)이 참조 영역의 첫 번째 열에 위치해야 합니다. 만약 찾을 값이 기준열보다 왼쪽에 있다면 데이터를 강제로 복사해서 오른쪽으로 옮기거나, 복잡한 INDEX와 MATCH 함수를 조합해야만 했습니다.
반면, 엑셀 XLOOKUP은 ‘찾을 범위’와 ‘가져올 범위’를 각각 독립적으로 지정하기 때문에 데이터의 위치에 구애받지 않습니다.
💡 XLOOKUP 기본 구문
excel
=XLOOKUP(찾을값, 찾을범위, 가져올범위, [포함되지않을때값], [일치모드], [검색모드])
Step-by-Step 실무 예제
다음과 같은 직원 정보 테이블이 있다고 가정해 봅시다.
| 사원번호 (B열) | 이름 (C열) | 부서 (D열) |
|---|---|---|
| K-001 | 홍길동 | 마케팅팀 |
| K-002 | 김철수 | 기획팀 |
| K-003 | 이영희 | 개발팀 |
[목표] 이름이 ‘이영희’인 직원의 ‘사원번호’를 조회하기
- 결과를 출력할 셀에 다음 수식을 입력합니다.
excel
=XLOOKUP("이영희", C2:C4, B2:B4) - 수식 해석:
"이영희": 찾고자 하는 값입니다.C2:C4: 이름이 입력되어 있는 찾을 범위입니다.B2:B4: 사원번호가 입력되어 있는 가져올 범위(결과 범위)입니다.- [Enter] 키를 누르면 이름 왼쪽에 있던 사원번호
K-003이 정확하게 출력됩니다.
💼 실무 시나리오 2. “자료를 업데이트했는데 수식이 다 깨져서 #REF! 에러가 납니다”
Q. 매주 보고서를 작성하면서 원본 데이터 중간에 새로운 열(비고란 등)을 추가합니다. 그런데 열을 추가할 때마다 VLOOKUP 수식을 적용해 둔 셀들이 전부 엉뚱한 값을 가져오거나 에러가 납니다. 해결 방법이 없을까요?
A. VLOOKUP은 열 번호를 숫자로 직접 입력하기 때문에 열이 추가되면 구조가 깨집니다. XLOOKUP은 범위 자체를 참조하므로 중간에 열을 백 개 추가해도 수식이 절대 깨지지 않습니다.
왜 VLOOKUP은 깨질까?
VLOOKUP 함수의 세 번째 인자는 가져올 열의 위치를 나타내는 숫자(Index)입니다. 예를 들어 =VLOOKUP("홍길동", B2:D4, 3, FALSE)에서 ‘3’은 세 번째 열을 가져오라는 뜻입니다. 만약 B열과 D열 사이에 새로운 열을 삽입하면, 기존의 세 번째 열은 네 번째 열로 밀려나지만 수식 속 ‘3’은 그대로 유지되므로 원치 않는 데이터가 출력됩니다.
XLOOKUP으로 안전한 수식 만들기
VLOOKUP XLOOKUP 비교 시 가장 큰 안정성 차이가 여기서 발생합니다. XLOOKUP은 숫자가 아닌 실제 범위(예: D2:D4)를 참조하므로, 중간에 열이 삽입되거나 삭제되더라도 엑셀이 자동으로 참조 범위를 조정해 줍니다.
Step-by-Step 실무 예제
- 기존에 정상 작동하던 XLOOKUP 수식:
“`excel
=XLOOKUP(“홍길동”, C2:C4, D2:D4) // 부서(D열) 정보를 가져옴 - 이름(C열)과 부서(D열) 사이에 ‘직급’이라는 새로운 열을 삽입합니다.
- 엑셀이 자동으로 부서 범위를 인식하여 수식을 다음과 같이 변경해 줍니다.
excel
=XLOOKUP("홍길동", C2:C4, E2:E4) // 자동으로 E열로 변경되어 안전하게 작동!
이 기능 덕분에 대용량 데이터를 다루는 실무 환경에서 휴먼 에러를 획기적으로 줄일 수 있습니다.
💼 실무 시나리오 3. “일치하는 값이 없을 때 나오는 #N/A 에러를 깔끔하게 숨기고 싶습니다”
Q. 거래처 코드를 조회하고 있는데, 신규 거래처처럼 목록에 없는 코드를 조회하면 화면에 지저분하게
#N/A에러가 표시됩니다. 상사에게 보고할 보고서인데 깔끔하게 “신규 거래처” 또는 “데이터 없음”으로 표시할 수 없을까요?A. 과거에는
IFERROR(VLOOKUP(...), "텍스트")형태로 함수를 두 번 겹쳐 써야 했습니다. 하지만 XLOOKUP은 함수 내부에 자체 에러 처리 기능이 탑재되어 있어 수식이 매우 간결해집니다.
IFERROR 중첩 없는 깔끔한 수식 작성법
XLOOKUP의 네 번째 인자인 [if_not_found] 영역을 활용하면 별도의 조건문이나 에러 처리 함수 없이도 오류 발생 시 출력할 대체 텍스트나 값을 지정할 수 있습니다.
Step-by-Step 실무 예제
거래처 목록에서 ‘애플코리아’라는 거래처의 담당자 연락처를 찾고자 합니다. 단, 목록에 없을 경우 “미등록 거래처”라고 표시하고 싶습니다.
| 거래처명 (B열) | 담당자 연락처 (C열) |
|---|---|
| 삼성전자 | 010-1234-5678 |
| LG전자 | 010-9876-5432 |
- 데이터를 조회할 셀에 아래와 같이 수식을 입력합니다.
excel
=XLOOKUP("애플코리아", B2:B3, C2:C3, "미등록 거래처") - 수식 해석:
"애플코리아"를B2:B3에서 검색합니다.- 존재하지 않으므로 에러(
#N/A)를 띄우는 대신, 네 번째 인자에 입력한"미등록 거래처"를 즉시 출력합니다. - 가독성이 획기적으로 향상되고, 수식의 길이도 짧아져 유지보수가 매우 쉬워집니다.
⏱️ 5분 실습 챌린지
백문이 불여일견! 지금 바로 엑셀을 켜고 아래 가상의 데이터를 입력한 뒤, 챌린지 과제 2개를 직접 해결해 보세요. 실무 감각과 컴활 실무 능력이 한 단계 업그레이드될 것입니다.
실습용 가상 데이터 테이블
| 제품코드 (A열) | 제품명 (B열) | 단가 (C열) | 재고수량 (D열) |
|---|---|---|---|
| P-101 | 모니터 | 250,000 | 15 |
| P-102 | 키보드 | 45,000 | 50 |
| P-103 | 마우스 | 30,000 | 80 |
🎯 챌린지 1: 역방향 조회 (오른쪽에서 왼쪽으로 찾기)
- 과제: 제품명 ‘키보드’를 기준으로 해당 제품의 ‘제품코드’를 찾아서 출력해 보세요.
- 힌트: 찾을 범위는
B2:B4이고, 결과값을 가져올 범위는 왼쪽의A2:A4입니다.
excel
// 정답 수식 확인하기 (드래그해 보세요)
=XLOOKUP("키보드", B2:B4, A2:A4)
🎯 챌린지 2: 에러 처리와 가격 조회 동시에 해결하기
- 과제: 제품코드 ‘P-105’의 ‘단가’를 조회해 보세요. 단, 목록에 해당 코드가 없을 경우 “코드 확인 필요”라는 문구가 출력되도록 수식을 작성해 보세요.
- 힌트: 네 번째 인자인
[if_not_found]자리를 활용하세요.
excel
// 정답 수식 확인하기 (드래그해 보세요)
=XLOOKUP("P-105", A2:A4, C2:C4, "코드 확인 필요")
💡 에디터의 한 줄 요약
VLOOKUP함수는 지난 수십 년간 엑셀을 지탱해 온 훌륭한 도구였지만, 현대의 동적이고 복잡한 데이터 업무 환경에서는 한계가 명확합니다.
오늘 배운 엑셀 XLOOKUP은 사용법이 더 직관적일 뿐만 아니라 수식의 안정성과 에러 처리까지 완벽하게 보장하는 상위 호환 함수입니다.지금 바로 실무 엑셀 템플릿에 적용해 보고, 불필요한 야근 시간을 획기적으로 줄여보세요!
업무 중 막히는 엑셀 함수 수식이나 컴활 실무와 관련해 궁금한 점이 있다면 언제든 아래 댓글을 통해 질문을 남겨주세요!