엑셀로 업무 자료를 정리하다 보면 다른 표에 있는 데이터를 찾아 현재 작업 중인 표에 자동으로 가져와야 하는 경우가 많습니다. 거래처 코드를 기준으로 거래처명을 찾거나, 상품코드를 이용해 상품 가격을 불러오거나, 직원번호를 기준으로 부서와 담당자 정보를 자동으로 입력하는 업무가 대표적입니다.
이처럼 특정 값을 기준으로 다른 데이터를 찾아오는 작업에서 많이 사용하는 함수가 VLOOKUP입니다. 하지만 최근 엑셀에서는 VLOOKUP의 단점을 보완한 XLOOKUP 함수를 사용할 수 있어 복잡한 데이터 관리 업무를 보다 편리하게 처리할 수 있습니다.
XLOOKUP 함수는 검색할 범위와 결과를 가져올 범위를 별도로 지정할 수 있기 때문에 VLOOKUP보다 직관적으로 사용할 수 있다는 장점이 있습니다. 특히 실무에서 열의 위치가 변경되거나 왼쪽에 있는 데이터를 찾아야 하는 상황에서 유용합니다.
이번 글에서는 엑셀 XLOOKUP 함수의 기본 사용법부터 실무 예제, VLOOKUP과의 차이점, 오류를 처리하는 방법까지 초보자도 이해하기 쉽게 정리해 보겠습니다.
XLOOKUP 함수란 무엇인가?
XLOOKUP은 특정 값을 검색한 다음 해당 값과 연결된 다른 데이터를 찾아 반환하는 엑셀 함수입니다.
쉽게 말하면 “이 값을 찾아서 그에 해당하는 다른 정보를 가져와줘”라고 엑셀에 요청하는 기능입니다.
예를 들어 다음과 같은 상품 목록이 있다고 가정해 보겠습니다.
상품코드 | 상품명 | 판매가격
P001 | 노트북 | 1200000
P002 | 모니터 | 350000
P003 | 키보드 | 80000
P004 | 마우스 | 40000
주문서에 상품코드만 입력되어 있고 상품명과 가격을 자동으로 표시하고 싶다면 XLOOKUP을 활용할 수 있습니다.
상품코드 P002를 기준으로 상품명을 찾는다면 엑셀은 상품 목록에서 P002를 검색한 후 해당 상품의 상품명인 모니터를 자동으로 가져올 수 있습니다.
XLOOKUP 함수 기본 문법
XLOOKUP 함수의 기본적인 구조는 다음과 같습니다.
=XLOOKUP(찾을값, 검색범위, 반환범위)
각 항목의 의미를 이해하면 XLOOKUP을 쉽게 사용할 수 있습니다.
찾을값은 검색하려는 기준 데이터입니다. 상품코드, 직원번호, 거래처코드 등이 해당됩니다.
검색범위는 찾을값을 검색할 영역입니다.
반환범위는 검색 결과로 가져올 데이터를 지정하는 영역입니다.
예를 들어 상품코드가 A2에 있고 상품코드 목록이 F2:F5, 상품명이 G2:G5에 있다면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(A2,F2:F5,G2:G5)
이 수식은 A2에 입력된 상품코드를 F2:F5에서 찾고, 일치하는 행의 G2:G5에 있는 상품명을 가져옵니다.
XLOOKUP으로 상품 가격 가져오기
상품명뿐만 아니라 상품 가격도 간단하게 가져올 수 있습니다.
상품 가격이 H2:H5에 입력되어 있다면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(A2,F2:F5,H2:H5)
여기서 A2는 찾으려는 상품코드이고 F2:F5는 상품코드가 입력된 검색범위입니다. H2:H5는 가격이 입력된 반환범위입니다.
VLOOKUP에서는 가져오려는 열의 번호를 직접 입력해야 하지만 XLOOKUP에서는 원하는 반환범위를 직접 지정할 수 있기 때문에 수식을 이해하기가 비교적 쉽습니다.
XLOOKUP 함수에서 정확한 값 찾기
XLOOKUP은 기본적으로 정확한 일치를 기준으로 값을 찾기 때문에 일반적인 업무용 데이터 검색에 적합합니다.
예를 들어 직원번호나 상품코드처럼 정확하게 일치해야 하는 데이터를 검색할 때 별도로 FALSE를 입력하지 않아도 됩니다.
다음과 같은 수식을 사용할 수 있습니다.
=XLOOKUP(A2,F2:F100,G2:G100)
A2에 입력된 값을 F2:F100에서 찾아 정확하게 일치하는 데이터를 G2:G100에서 가져옵니다.
이러한 방식은 VLOOKUP의 기본적인 사용 방법과 비교했을 때 수식이 간결하다는 장점이 있습니다.
XLOOKUP으로 검색 결과가 없을 때 처리하기
실무에서 데이터를 검색하다 보면 검색하려는 값이 기준표에 없는 경우가 있습니다.
예를 들어 상품코드가 P999인데 상품 목록에는 P001부터 P100까지만 등록되어 있다면 검색 결과를 찾을 수 없습니다.
이 경우 XLOOKUP의 네 번째 인수를 사용하여 검색 결과가 없을 때 표시할 내용을 지정할 수 있습니다.
예를 들어 다음과 같이 작성할 수 있습니다.
=XLOOKUP(A2,F2:F100,G2:G100,”상품 확인 필요”)
이렇게 하면 검색할 값이 존재할 경우 해당 상품명이 표시되고, 값을 찾지 못하면 “상품 확인 필요”라는 문구가 표시됩니다.
업무용 엑셀 파일에서 #N/A와 같은 오류 메시지가 그대로 나타나는 것을 방지할 수 있어 보고서 작성에도 유용합니다.
XLOOKUP 실무 예제 1: 거래처명 자동 입력
회사에서 거래처코드를 기준으로 거래처명을 자동으로 입력한다고 가정해 보겠습니다.
거래처 정보가 다음과 같이 구성되어 있습니다.
거래처코드 | 거래처명 | 담당자
C001 | 한국상사 | 김철수
C002 | 대한유통 | 이영희
C003 | 미래기업 | 박민수
주문서의 A2 셀에 거래처코드가 입력되어 있고 거래처코드가 F2:F4, 거래처명이 G2:G4에 있다면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(A2,F2:F4,G2:G4,”거래처 확인 필요”)
A2의 거래처코드를 F2:F4에서 찾고 해당하는 거래처명을 G2:G4에서 가져오는 방식입니다.
거래처가 수백 개 이상 등록되어 있는 경우에도 동일한 방식으로 활용할 수 있습니다.
XLOOKUP 실무 예제 2: 직원번호로 부서 찾기
직원번호를 기준으로 부서 정보를 자동으로 가져오는 업무에서도 XLOOKUP을 사용할 수 있습니다.
직원번호 | 이름 | 부서
1001 | 김민수 | 영업팀
1002 | 박지영 | 인사팀
1003 | 이준호 | 기획팀
직원번호가 A2에 입력되어 있고 직원번호가 F2:F4, 부서가 H2:H4에 있다면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(A2,F2:F4,H2:H4,”직원 정보 없음”)
직원번호를 입력하면 해당 직원의 부서가 자동으로 표시됩니다.
인사자료나 조직도 관리처럼 반복적으로 직원 정보를 확인해야 하는 업무에서 활용하기 좋은 방법입니다.
XLOOKUP 실무 예제 3: 상품코드로 가격 찾기
상품 판매자료를 작성할 때 상품코드만 입력하고 판매가격을 자동으로 가져오도록 만들 수도 있습니다.
상품코드 | 상품명 | 가격
P001 | 노트북 | 1200000
P002 | 모니터 | 350000
P003 | 키보드 | 80000
상품코드가 A2에 입력되어 있고 상품코드가 F2:F4, 가격이 H2:H4에 있다면 다음과 같은 수식을 사용할 수 있습니다.
=XLOOKUP(A2,F2:F4,H2:H4,”가격 확인 필요”)
상품 가격이 변경되었을 경우 기준이 되는 상품 가격표만 수정하면 XLOOKUP 결과에도 변경된 가격이 반영됩니다.
XLOOKUP의 가장 큰 장점은 왼쪽 데이터도 검색할 수 있다는 것
XLOOKUP이 VLOOKUP보다 편리한 이유 중 하나는 검색 방향에 대한 제한이 적다는 것입니다.
VLOOKUP은 검색 기준이 되는 값이 선택한 데이터 범위의 가장 왼쪽 열에 있어야 합니다.
반면 XLOOKUP은 검색범위와 반환범위를 별도로 지정할 수 있기 때문에 검색 기준이 되는 열의 왼쪽에 있는 데이터도 가져올 수 있습니다.
예를 들어 다음과 같은 자료가 있다고 가정해 보겠습니다.
상품명 | 상품코드 | 가격
노트북 | P001 | 1200000
모니터 | P002 | 350000
키보드 | P003 | 80000
상품코드를 기준으로 상품명을 찾으려면 상품코드의 왼쪽에 있는 상품명을 가져와야 합니다.
이 경우 XLOOKUP을 활용하면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(A2,G2:G4,F2:F4)
검색범위와 반환범위를 자유롭게 지정할 수 있기 때문에 VLOOKUP에서 발생할 수 있는 방향 제한을 피할 수 있습니다.
XLOOKUP과 VLOOKUP의 차이점
XLOOKUP과 VLOOKUP은 모두 특정 데이터를 검색해서 관련 정보를 가져오는 함수라는 공통점이 있습니다. 하지만 사용 방식과 기능에는 차이가 있습니다.
VLOOKUP은 다음과 같은 구조를 사용합니다.
=VLOOKUP(찾을값,데이터범위,열번호,FALSE)
VLOOKUP에서는 데이터를 가져올 열의 순서를 숫자로 지정해야 합니다.
반면 XLOOKUP은 다음과 같이 검색범위와 반환범위를 직접 지정합니다.
=XLOOKUP(찾을값,검색범위,반환범위)
따라서 XLOOKUP은 수식을 작성할 때 어떤 데이터를 찾고 어떤 데이터를 가져오는지 직관적으로 확인하기 쉽습니다.
또한 VLOOKUP은 검색 기준이 되는 열이 데이터 범위의 가장 왼쪽에 있어야 하지만 XLOOKUP은 검색범위와 반환범위를 별도로 지정할 수 있습니다.
XLOOKUP에서 열이 추가되어도 편리한 이유
VLOOKUP을 사용할 때는 반환할 열의 번호를 입력해야 하기 때문에 중간에 열이 추가되면 수식을 다시 확인해야 하는 경우가 있습니다.
예를 들어 상품코드가 첫 번째 열이고 상품명이 두 번째 열이라면 상품명을 가져오기 위해 2를 입력합니다.
그런데 중간에 새로운 열이 추가되면 기존에 작성한 열번호와 실제 데이터 위치가 달라질 수 있습니다.
XLOOKUP은 반환범위를 직접 지정하기 때문에 이러한 문제를 줄일 수 있습니다.
예를 들어 상품명을 가져오는 범위를 직접 지정했다면 중간에 다른 열이 추가되더라도 수식의 의미를 이해하기 쉽습니다.
XLOOKUP으로 가장 가까운 값 찾기
XLOOKUP은 정확하게 일치하는 값뿐만 아니라 검색 옵션을 활용해 조건에 맞는 값을 찾는 기능도 제공합니다.
예를 들어 특정 기준값보다 작거나 큰 값 중 가장 가까운 데이터를 찾아야 하는 업무에서 활용할 수 있습니다.
다만 일반적인 직장 업무에서는 상품코드, 직원번호, 거래처코드처럼 정확한 값을 검색하는 경우가 많기 때문에 처음 XLOOKUP을 배우는 단계에서는 정확한 일치 검색부터 익히는 것이 좋습니다.
XLOOKUP 사용 시 주의할 점
XLOOKUP을 사용할 때는 검색범위와 반환범위의 크기를 동일하게 설정하는 것이 중요합니다.
예를 들어 검색범위가 F2:F100이라면 반환범위도 같은 데이터 행을 기준으로 구성되어 있어야 합니다.
또한 검색하려는 값의 데이터 형식도 확인해야 합니다. 숫자로 저장된 값과 문자로 저장된 값은 화면에서 비슷하게 보이더라도 검색 결과에 영향을 줄 수 있습니다.
불필요한 공백이 포함된 데이터도 검색에 문제가 될 수 있습니다. 특히 외부 자료를 복사해서 엑셀에 붙여넣은 경우 데이터에 보이지 않는 공백이 포함되어 있는지 확인하는 것이 좋습니다.
XLOOKUP을 사용할 수 없는 경우 확인할 사항
XLOOKUP은 모든 엑셀 버전에서 사용할 수 있는 것은 아닙니다.
사용 중인 엑셀 버전에 따라 XLOOKUP 함수가 지원되지 않을 수 있으므로 수식을 입력했을 때 함수 이름 자체가 인식되지 않는다면 사용하는 엑셀 버전을 확인해야 합니다.
XLOOKUP을 사용할 수 없는 환경에서는 VLOOKUP이나 INDEX와 MATCH 같은 기존 함수를 활용할 수 있습니다.
회사에서 여러 사람이 동일한 엑셀 파일을 사용하는 경우에는 파일을 공유하는 사람들의 엑셀 버전도 함께 고려하는 것이 좋습니다.
XLOOKUP을 처음 배우는 직장인을 위한 연습 방법
XLOOKUP을 처음 사용하는 경우 실제 업무에서 자주 접하는 데이터를 활용해 연습하는 것이 좋습니다.
먼저 상품코드와 상품명이 있는 간단한 표를 만든 다음 상품코드를 입력하면 상품명이 자동으로 표시되도록 만들어 보는 것이 좋습니다.
그다음 상품 가격을 추가하고 상품코드에 따라 가격까지 자동으로 가져오도록 연습해 볼 수 있습니다.
이후 거래처코드와 거래처명, 직원번호와 부서, 고객번호와 연락처 등 다양한 데이터를 활용하면 XLOOKUP의 기본 원리를 쉽게 익힐 수 있습니다.
특히 다음과 같은 기본 구조를 기억해두면 좋습니다.
=XLOOKUP(찾을값,검색범위,반환범위)
이 구조만 이해해도 다양한 업무 데이터에 XLOOKUP을 적용할 수 있습니다.
XLOOKUP과 VLOOKUP 중 무엇을 사용하는 것이 좋을까?
기존에 VLOOKUP을 사용하고 있다면 당장 모든 수식을 XLOOKUP으로 변경할 필요는 없습니다.
VLOOKUP은 오랫동안 사용되어 온 함수이기 때문에 기존 업무 파일이나 회사에서 사용하는 엑셀 문서에서 여전히 많이 활용됩니다.
하지만 최신 엑셀 환경에서 새로운 업무 파일을 만들고 있다면 XLOOKUP을 익혀두는 것이 좋습니다.
특히 검색범위와 반환범위를 직접 지정할 수 있고 왼쪽 데이터도 검색할 수 있다는 점은 실무에서 상당한 편의성을 제공합니다.
따라서 VLOOKUP을 이미 알고 있다면 XLOOKUP을 추가로 익혀두는 방식이 가장 현실적입니다.
마무리
엑셀 XLOOKUP 함수는 특정 값을 기준으로 원하는 데이터를 찾아오는 데 유용한 함수입니다. 상품코드로 상품명을 찾거나 직원번호로 부서를 찾는 것처럼 반복적인 데이터 검색 업무를 자동화하는 데 활용할 수 있습니다.
기본적인 사용법은 다음과 같습니다.
=XLOOKUP(찾을값,검색범위,반환범위)
VLOOKUP과 비교하면 열번호를 직접 입력할 필요가 없고 검색범위와 반환범위를 별도로 지정할 수 있다는 점이 큰 특징입니다. 또한 검색 기준이 되는 데이터의 왼쪽에 있는 값을 가져올 수 있어 다양한 형태의 엑셀 자료에 활용하기 좋습니다.
직장에서 엑셀을 자주 사용한다면 VLOOKUP뿐만 아니라 XLOOKUP도 함께 익혀두는 것이 좋습니다. 특히 거래처 관리, 상품 관리, 재고 관리, 고객 정보 관리, 직원 정보 관리 등 반복적으로 데이터를 검색해야 하는 업무에서는 XLOOKUP을 활용해 수작업을 줄일 수 있습니다.
처음에는 상품코드로 상품명을 찾는 간단한 예제부터 시작해 보고, 익숙해지면 가격, 담당자, 부서, 연락처 등 여러 정보를 자동으로 불러오는 방식으로 활용 범위를 넓혀보는 것이 좋습니다.
엑셀에서 반복적으로 다른 표의 데이터를 찾아 직접 입력하고 있다면 XLOOKUP 함수를 활용해 보세요. 적절하게 사용하면 데이터 검색과 정리에 걸리는 시간을 줄이고 보다 효율적인 엑셀 업무 환경을 만드는 데 도움이 될 수 있습니다.
답글 남기기