엑셀을 사용하다 보면 다른 표에 있는 정보를 자동으로 찾아와야 하는 경우가 있습니다.
예를 들어 상품번호를 입력하면 상품명이 자동으로 표시되도록 하거나, 사원번호를 입력하면 이름과 부서를 자동으로 불러오는 작업입니다.
이런 작업을 할 때 유용하게 사용할 수 있는 함수가 XLOOKUP 함수입니다.
기존에는 VLOOKUP이나 HLOOKUP을 많이 사용했지만, XLOOKUP은 보다 편리한 방식으로 데이터를 검색할 수 있어 최근 엑셀에서 활용도가 높아지고 있습니다.
이번 글에서는 엑셀을 처음 사용하는 사람도 이해할 수 있도록 XLOOKUP 함수의 기본적인 사용법부터 실제 업무에서 활용하는 방법까지 하나씩 알아보겠습니다.
XLOOKUP 함수란?
XLOOKUP은 특정 값을 찾아서 해당 값과 연결된 다른 데이터를 가져오는 함수입니다.
쉽게 표현하면 다음과 같습니다.
"이 값을 찾아서 옆에 있는 관련 정보를 가져와줘."
예를 들어 다음과 같은 직원 목록이 있다고 가정해 보겠습니다.
사번 이름 부서 직급
1001 홍길동 총무팀 대리
1002 박공정 회계팀 과장
1003 김공명 영업팀 사원
1004 이철수 인사팀 대리
여기에서 사번 1003을 입력했을 때 자동으로 박철수라는 이름을 가져오고 싶다면 XLOOKUP을 사용할 수 있습니다.
XLOOKUP 기본 사용법
XLOOKUP의 기본적인 수식 형태는 다음과 같습니다.
=XLOOKUP(찾을값, 찾을범위, 가져올범위)
각각의 의미는 다음과 같습니다.
찾을값 : 찾고 싶은 데이터
찾을범위 : 데이터를 검색할 범위
가져올범위 : 검색 결과로 가져올 데이터의 범위
예를 들어 사번이 A2:A5에 있고 이름이 B2:B5에 있다고 가정하겠습니다.
=XLOOKUP(1003,A2:A5,B2:B5)
그러면 결과는 다음과 같이 표시됩니다.
박철수
즉, A열에서 1003을 찾은 다음 같은 행에 있는 B열의 값을 가져오는 것입니다.
셀을 이용해서 XLOOKUP 사용하기
실제 업무에서는 숫자를 수식에 직접 입력하기보다 특정 셀을 검색하도록 만드는 경우가 많습니다.
예를 들어 D2 셀에 사번을 입력한다고 가정하겠습니다.
=XLOOKUP(D2,A2:A5,B2:B5)
D2에 1003을 입력하면 결과는 자동으로 박철수가 됩니다.
D2의 값을 1001로 변경하면 결과도 자동으로 김민수로 변경됩니다.
이렇게 만들어 놓으면 같은 수식을 계속 입력할 필요가 없어 편리합니다.
XLOOKUP으로 부서 찾기
XLOOKUP은 이름뿐만 아니라 다른 정보도 가져올 수 있습니다.
예를 들어 사번을 기준으로 부서를 가져오고 싶다면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(D2,A2:A5,C2:C5)
여기에서
A2:A5 → 사번
C2:C5 → 부서
가 됩니다.
D2에 1003이 입력되어 있다면 결과는 영업팀으로 표시됩니다.
XLOOKUP으로 직급 찾기
직급도 같은 방법으로 가져올 수 있습니다.
=XLOOKUP(D2,A2:A5,D2:D5)
다만 이 예제에서는 검색할 사번과 결과를 표시할 셀의 위치가 겹칠 수 있기 때문에 실제 표를 만들 때는 검색용 셀과 결과 셀을 별도로 배치하는 것이 좋습니다.
예를 들어 검색할 사번을 F2에 입력하고 직급을 G2에 표시한다면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(F2,A2:A5,D2:D5)
이처럼 XLOOKUP은 원하는 열의 정보를 자유롭게 가져올 수 있습니다.
XLOOKUP에서 찾는 값이 없을 때
실무에서 자주 발생하는 문제가 있습니다.
찾으려는 값이 표에 존재하지 않는 경우입니다.
이때 XLOOKUP은 기본적으로 #N/A 오류를 표시합니다.
하지만 네 번째 인수를 이용하면 원하는 문구를 표시할 수 있습니다.
=XLOOKUP(F2,A2:A5,B2:B5,"찾는 정보가 없습니다")
검색 결과가 존재하지 않는다면 다음과 같이 표시됩니다.
찾는 정보가 없습니다
이 방법을 사용하면 엑셀 표가 훨씬 깔끔해집니다.
XLOOKUP의 기본 구조 자세히 알아보기
XLOOKUP은 다음과 같은 형태로 사용할 수 있습니다.
=XLOOKUP(찾을값, 찾을범위, 반환범위, [찾지 못했을 때], [일치옵션], [검색옵션])
초보자가 처음부터 모든 옵션을 외울 필요는 없습니다.
실무에서는 다음 세 가지를 먼저 기억하면 됩니다.
=XLOOKUP(찾을값, 찾을범위, 반환범위)
그리고 검색 결과가 없을 때 문구를 표시하고 싶다면 다음과 같이 사용합니다.
=XLOOKUP(찾을값, 찾을범위, 반환범위,"없음")
이 정도만 익혀도 기본적인 XLOOKUP 작업은 충분히 할 수 있습니다.
XLOOKUP의 장점
XLOOKUP은 기존의 VLOOKUP보다 편리한 부분이 많습니다.
왼쪽에 있는 값도 찾을 수 있다
VLOOKUP은 기본적으로 검색 기준 열의 오른쪽에 있는 값을 가져오는 방식으로 사용합니다.
반면 XLOOKUP은 검색 범위와 결과 범위를 별도로 지정할 수 있기 때문에 왼쪽에 있는 데이터도 쉽게 가져올 수 있습니다.
이 점은 XLOOKUP의 대표적인 장점입니다.
열 번호를 입력하지 않아도 된다
VLOOKUP을 사용할 때는 몇 번째 열의 데이터를 가져올지 숫자로 지정해야 합니다.
반면 XLOOKUP은 직접 가져올 범위를 지정합니다.
따라서 표의 열이 추가되거나 순서가 변경되었을 때 수식을 관리하기가 상대적으로 편리합니다.
세로 검색과 가로 검색에 활용할 수 있다
XLOOKUP은 세로 방향뿐만 아니라 가로 방향의 데이터 검색에도 활용할 수 있습니다.
따라서 기존의 VLOOKUP과 HLOOKUP을 각각 사용하는 상황에서도 XLOOKUP을 활용할 수 있습니다.
XLOOKUP과 VLOOKUP 차이
두 함수 모두 원하는 데이터를 찾아오는 데 사용하지만 사용 방법에는 차이가 있습니다.
| 구분 | XLOOKUP | VLOOKUP |
| 사용 편의성 | 비교적 편리 | 익숙한 방식 |
| 왼쪽 데이터 검색 | 가능 | 기본 방식에서는 제한적 |
| 반환 범위 지정 | 직접 지정 | 열 번호 입력 |
| 가로 검색 | 가능 | 별도 함수 필요 |
| 찾지 못했을 때 표시 | 별도 인수 지원 | 별도 처리 필요 |
| 열 추가에 따른 관리 | 비교적 편리 | 주의 필요 |
새로운 엑셀 환경에서 작업한다면 XLOOKUP을 익혀 두는 것이 유용합니다.
다만 기존에 VLOOKUP으로 작성된 업무 파일이 많다면 두 함수 모두 사용할 수 있도록 익혀두는 것이 좋습니다.
XLOOKUP을 사용할 때 자주 하는 실수
검색 범위와 반환 범위의 크기가 다른 경우
예를 들어 다음과 같이 범위의 행 수가 다르면 문제가 발생할 수 있습니다.
=XLOOKUP(A2,B2:B10,C2:C20)
검색 범위와 반환 범위가 서로 대응되도록 설정하는 것이 중요합니다.
숫자와 텍스트 형식이 다른 경우
겉으로는 같은 숫자로 보이더라도 한쪽은 숫자이고 다른 쪽은 텍스트로 저장되어 있다면 원하는 결과가 나오지 않을 수 있습니다.
예를 들어
1001
과
"1001"
은 엑셀에서 서로 다른 형태로 인식될 수 있습니다.
검색이 제대로 되지 않는다면 셀의 데이터 형식을 확인해 보는 것이 좋습니다.
띄어쓰기가 포함된 경우
이름이나 상품명 같은 텍스트 데이터를 검색할 때 앞뒤에 불필요한 공백이 들어가 있으면 검색이 제대로 되지 않을 수 있습니다.
특히 다른 파일에서 복사해 온 데이터를 사용할 때 자주 발생하는 문제입니다.
XLOOKUP 실무 활용 예시
XLOOKUP은 다양한 업무에서 활용할 수 있습니다.
직원 관리
사번을 입력하면 이름, 부서, 직급을 자동으로 표시할 수 있습니다.
상품 관리
상품코드를 입력하면 상품명이나 가격을 자동으로 가져올 수 있습니다.
재고 관리
제품번호를 기준으로 현재 재고 수량을 확인할 수 있습니다.
매출 관리
거래처 코드를 이용해 거래처명이나 담당자를 자동으로 불러올 수 있습니다.
고객 관리
고객번호를 기준으로 고객 정보를 검색하는 표를 만들 수 있습니다.
이처럼 고유한 번호를 기준으로 다른 정보를 자동으로 찾아오는 업무에서 특히 유용합니다.
XLOOKUP을 처음 배우는 사람에게 추천하는 연습 방법
처음부터 복잡한 수식을 외우려고 하면 어렵게 느껴질 수 있습니다.
다음 순서대로 연습하는 것이 좋습니다.
1단계
간단한 표를 직접 만들어 봅니다.
2단계
고유번호를 기준으로 이름을 찾아봅니다.
3단계
같은 방법으로 부서와 가격 등을 찾아봅니다.
4단계
검색 결과가 없을 때 표시할 문구를 추가합니다.
5단계
실제 업무에서 사용하는 데이터를 활용해 봅니다.
함수를 단순히 암기하는 것보다 직접 표를 만들어 여러 번 사용해 보는 것이 훨씬 효과적입니다.
자주 묻는 질문
XLOOKUP은 모든 엑셀에서 사용할 수 있나요?
XLOOKUP은 비교적 최근 버전의 Excel에서 제공되는 함수입니다. 사용 중인 엑셀 버전에 따라 지원 여부가 다를 수 있습니다.
XLOOKUP과 VLOOKUP 중 어떤 것을 사용해야 하나요?
새로운 문서를 작성하고 XLOOKUP을 사용할 수 있는 환경이라면 XLOOKUP이 편리한 경우가 많습니다. 하지만 기존 파일과의 호환성을 고려해야 한다면 VLOOKUP을 함께 알아두는 것이 좋습니다.
XLOOKUP에서 #N/A가 나옵니다.
검색하려는 값이 검색 범위에 존재하는지 먼저 확인해 보세요. 숫자와 텍스트 형식이 서로 다른 경우에도 문제가 발생할 수 있습니다.
XLOOKUP으로 왼쪽 값을 가져올 수 있나요?
네. XLOOKUP은 검색 범위와 반환 범위를 별도로 지정하기 때문에 검색 기준보다 왼쪽에 있는 데이터도 가져올 수 있습니다.
XLOOKUP을 여러 조건으로 검색할 수 있나요?
가능합니다. 여러 조건을 결합하는 방법을 이용하면 보다 복잡한 데이터 검색에도 활용할 수 있습니다. 다만 처음에는 기본적인 단일 조건 검색부터 익히는 것이 좋습니다.
XLOOKUP은 엑셀에서 데이터를 검색하고 필요한 정보를 자동으로 가져올 때 매우 유용한 함수입니다.
처음에는 수식이 복잡해 보일 수 있지만 기본 구조인
=XLOOKUP(찾을값, 찾을범위, 반환범위)
이 세 부분만 먼저 이해하면 어렵지 않습니다.
특히 직원 관리, 상품 관리, 재고 관리, 매출 관리처럼 특정 번호를 기준으로 다른 정보를 찾아야 하는 작업에서 활용도가 높습니다.
엑셀 함수를 잘 활용하면 반복적으로 데이터를 찾아 입력하는 시간을 줄이고 실수도 줄일 수 있습니다. XLOOKUP을 처음 배우는 경우에는 복잡한 수식부터 시작하기보다 간단한 표를 직접 만들어 이름이나 상품명을 찾아오는 연습부터 해보는 것을 추천합니다.
기본적인 사용법에 익숙해진 뒤 조건을 추가하거나 다른 함수와 결합하면 더욱 다양한 업무에 활용할 수 있습니다.