XLOOKUP은 다른 표나 범위에서 원하는 값을 찾은 뒤, 같은 행에 있는 다른 값을 가져오는 엑셀 함수입니다. 상품코드를 입력하면 상품명이나 가격을 자동으로 가져오거나, 사번을 입력하면 직원의 이름과 부서를 한 번에 불러오는 식으로 사용할 수 있습니다. 특히 기존 VLOOKUP과 달리 검색하는 열의 왼쪽에 있는 값도 가져올 수 있고 기본적으로 정확한 일치를 사용하기 때문에 실무에서 활용하기 편리합니다.
1. XLOOKUP 함수는 무엇일까?
2. 가장 기본적인 XLOOKUP 사용법
3. 상품코드로 상품명을 찾아오는 방법
4. 상품코드로 가격까지 자동으로 가져오는 방법
5. 값을 찾지 못했을 때 오류 대신 문구 표시하기
6. 왼쪽에 있는 값도 찾아올 수 있을까?
7. 한 번에 여러 열의 값을 가져오는 방법
8. 가장 가까운 값을 찾거나 부분적으로 검색하는 방법
9. XLOOKUP 사용할 때 자주 생기는 오류
10. XLOOKUP과 VLOOKUP의 차이
11. 자주 묻는 질문
12. 마무리
1. XLOOKUP 함수는 무엇일까?
XLOOKUP은 표에서 원하는 값을 검색하고 같은 위치에 있는 다른 값을 가져오는 함수입니다.
예를 들어 아래와 같은 상품 표가 있다고 생각해보겠습니다.
A001 | 무선마우스 | 25000
A002 | 키보드 | 45000
A003 | USB허브 | 32000
A004 | 모니터받침대 | 28000
여기서 A003을 입력하면 "USB허브"를 자동으로 가져오게 만들 수 있습니다.
바로 이런 작업에 사용하는 것이 XLOOKUP입니다.
찾을 값 → 어디에서 찾을지 → 무엇을 가져올지
이 세 가지만 지정하면 기본적인 XLOOKUP을 사용할 수 있습니다.
2. 가장 기본적인 XLOOKUP 사용법
XLOOKUP의 기본적인 문법은 다음과 같습니다.
=XLOOKUP(찾을값, 찾을범위, 가져올범위)
예를 들어 F2 셀에 상품코드를 입력하고, A열에서 상품코드를 찾은 뒤 B열의 상품명을 가져오려면 다음과 같이 작성합니다.
=XLOOKUP(F2,A2:A10,B2:B10)
이 수식은 F2에 입력된 값을 A2:A10에서 찾고, 같은 행의 B2:B10에 있는 값을 반환합니다.
XLOOKUP의 기본 일치 방식은 정확히 일치이므로 일반적인 상품코드나 사번 조회에서는 별도의 옵션을 넣지 않아도 됩니다.
찾는 범위와 가져오는 범위는 서로 같은 크기의 행 또는 열 범위로 맞추는 것이 좋습니다.
3. 상품코드로 상품명을 찾아오는 방법
가장 쉬운 예제를 직접 만들어보겠습니다.
A열 = 상품코드
B열 = 상품명
C열 = 가격
F2 셀에 조회하려는 상품코드를 입력합니다. 예를 들어 A003이라고 입력했다고 가정하겠습니다.
G2 셀에 다음 수식을 입력합니다.
=XLOOKUP(F2,A2:A10,B2:B10)
그러면 F2의 A003을 A2:A10에서 찾고, 같은 행의 B열 상품명이 표시됩니다.
F2 → A003
G2 → USB허브
상품코드만 바꾸면 상품명도 자동으로 바뀌기 때문에 제품 목록이나 재고표를 관리할 때 편리합니다.
4. 상품코드로 가격까지 자동으로 가져오는 방법
같은 방식으로 가격도 가져올 수 있습니다.
상품코드가 F2에 있고 가격이 C열에 있다면 다음과 같이 작성합니다.
=XLOOKUP(F2,A2:A10,C2:C10)
이제 F2의 상품코드를 바꾸면 가격도 자동으로 바뀝니다.
예를 들어 A003을 입력했다면 C열의 해당 가격이 결과로 표시됩니다.
상품코드 입력 → 상품명 자동 표시
상품코드 입력 → 가격 자동 표시
상품코드 입력 → 재고수량 자동 표시
상품코드 입력 → 거래처 자동 표시
같은 방식으로 표 안에 있는 다른 열의 데이터를 가져올 수도 있습니다.
5. 값을 찾지 못했을 때 오류 대신 문구 표시하기
XLOOKUP으로 값을 찾았는데 검색 대상이 표에 없다면 기본적으로 #N/A 오류가 표시됩니다.
사용자가 보는 화면에서는 이보다 "상품 없음" 같은 문구가 훨씬 이해하기 쉽습니다.
이럴 때는 네 번째 인수인 if_not_found를 사용합니다.
=XLOOKUP(F2,A2:A10,B2:B10,"상품 없음")
이제 F2에 존재하지 않는 상품코드를 입력하면 #N/A 대신 "상품 없음"이라고 표시됩니다.
=XLOOKUP(F2,A2:A10,B2:B10,"등록되지 않은 상품입니다")
고객 목록, 직원 목록, 재고표처럼 검색값이 없을 가능성이 있는 표에서는 이 방법을 사용하는 것이 편리합니다.
6. 왼쪽에 있는 값도 찾아올 수 있을까?
XLOOKUP의 장점 중 하나가 바로 검색하는 열의 왼쪽에 있는 값도 가져올 수 있다는 것입니다.
예를 들어 A열에 사번, B열에 이름이 있다고 가정하겠습니다. 이번에는 이름을 입력하고 사번을 찾고 싶다고 해보겠습니다.
B열 = 이름
F2에 이름을 입력하고 A열의 사번을 가져오려면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(F2,B2:B10,A2:A10)
B열에서 이름을 찾고, 같은 행에 있는 A열의 사번을 가져오는 방식입니다.
검색 범위가 결과 범위의 오른쪽에 있어도 되고 왼쪽에 있어도 됩니다.
따라서 VLOOKUP 때문에 표의 열 순서를 일부러 바꾸는 작업을 줄일 수 있습니다.
7. 한 번에 여러 열의 값을 가져오는 방법
XLOOKUP은 하나의 값만 가져오는 것뿐만 아니라 여러 열의 결과를 한 번에 반환할 수도 있습니다.
예를 들어 A열에 사번, B열에 이름, C열에 부서가 있다면 사번을 기준으로 이름과 부서를 동시에 가져올 수 있습니다.
=XLOOKUP(F2,A2:A10,B2:C10)
이 수식을 입력하면 B열과 C열의 결과가 여러 셀에 자동으로 펼쳐질 수 있습니다.
F2 → 1003
G2 → 홍길동
H2 → 영업팀
따라서 같은 사람의 이름, 부서, 직급처럼 여러 정보를 한 번에 가져와야 할 때 유용합니다.
결과가 여러 셀로 펼쳐지는 영역에 다른 데이터가 이미 들어 있다면 결과를 표시하지 못할 수 있습니다. XLOOKUP을 입력하기 전에 결과가 들어갈 공간을 비워두는 것이 좋습니다.
8. 가장 가까운 값을 찾거나 부분적으로 검색하는 방법
XLOOKUP은 정확하게 같은 값을 찾는 것뿐만 아니라 상황에 따라 근사 일치나 와일드카드 검색도 사용할 수 있습니다.
예를 들어 점수에 따라 등급을 찾는 표가 있다고 해보겠습니다.
0 | F
60 | D
70 | C
80 | B
90 | A
점수가 85점이라면 정확히 85라는 값을 찾는 대신 다음으로 작은 기준값을 이용해 B를 찾는 방식이 필요할 수 있습니다.
=XLOOKUP(F2,A2:A6,B2:B6,,-1)
XLOOKUP의 match_mode를 이용해 정확히 일치하지 않을 때 다음으로 작은 값을 찾도록 설정할 수 있습니다.
또한 와일드카드를 이용하면 일부 문자가 포함된 값을 찾을 수도 있습니다. 예를 들어 "삼성"으로 시작하는 항목을 찾고 싶다면 와일드카드 * 등을 활용할 수 있습니다.
먼저 정확히 일치하는 기본 조회부터 익힌 뒤 근사 일치와 와일드카드를 추가로 사용하는 것이 좋습니다.
9. XLOOKUP 사용할 때 자주 생기는 오류
XLOOKUP 자체는 간단하지만 실제 업무에서는 데이터 형식 때문에 결과가 나오지 않는 경우가 많습니다.
찾는 값이 검색 범위에 없을 가능성이 높습니다.
② 숫자인데 찾지 못하는 경우
한쪽은 숫자이고 다른 쪽은 문자로 저장되어 있을 수 있습니다.
③ 공백 때문에 찾지 못하는 경우
이름이나 코드 앞뒤에 보이지 않는 공백이 들어가 있을 수 있습니다.
④ 결과가 이상한 위치에 표시되는 경우
검색 범위와 반환 범위를 잘못 지정했는지 확인합니다.
⑤ 여러 값 반환이 안 되는 경우
결과가 펼쳐질 영역에 다른 데이터가 들어 있는지 확인합니다.
특히 상품코드나 사번처럼 겉으로는 똑같아 보여도 한쪽이 문자이고 한쪽이 숫자로 저장된 경우 검색이 원하는 대로 되지 않을 수 있습니다.
찾는 값 → 검색 범위 → 반환 범위 → 데이터 형식
순서로 확인해보는 것이 좋습니다.
10. XLOOKUP과 VLOOKUP의 차이
XLOOKUP을 배우면 기존에 많이 사용하던 VLOOKUP과 어떤 차이가 있는지도 궁금해집니다.
검색하는 값이 표의 첫 번째 열에 있어야 하고, 결과를 가져올 열 번호를 지정합니다.
XLOOKUP
검색할 범위와 결과를 가져올 범위를 각각 지정하기 때문에 열의 위치에 대한 제약이 적습니다.
예를 들어 VLOOKUP에서는 상품코드가 왼쪽에 있고 상품명이 오른쪽에 있어야 하는 구조가 일반적입니다. 반면 XLOOKUP은 이름을 찾아 사번을 가져오는 것처럼 왼쪽 방향 조회도 가능합니다.
또한 XLOOKUP은 기본적으로 정확히 일치하는 값을 찾기 때문에 일반적인 조회 작업에서 별도의 FALSE 설정을 넣지 않아도 됩니다.
다만 XLOOKUP은 Excel 2016과 Excel 2019에서는 사용할 수 없습니다. Microsoft 365, Excel 2021, Excel 2024 등 지원되는 버전에서 사용하는 것이 좋습니다.
기존 VLOOKUP을 이미 알고 있다면 XLOOKUP으로 바꿔보면서 두 함수의 차이를 직접 비교해보면 이해하기 쉽습니다.
11. 자주 묻는 질문
A. 상품코드로 상품명이나 가격을 찾거나, 사번으로 직원 정보를 가져오는 것처럼 한 표에서 기준값을 찾고 다른 값을 자동으로 가져올 때 유용합니다.
A. 가능합니다. 검색 범위와 반환 범위를 별도로 지정하기 때문에 검색 열의 왼쪽에 있는 값도 가져올 수 있습니다.
A. 가능합니다. 네 번째 인수에 원하는 문구를 넣으면 됩니다. 예를 들어
=XLOOKUP(F2,A2:A10,B2:B10,"상품 없음")처럼 사용할 수 있습니다.
A. 반환 범위를 여러 열로 지정하면 관련된 여러 결과를 한 번에 반환할 수 있습니다. 결과가 펼쳐질 공간은 비워두는 것이 좋습니다.
A. Microsoft 공식 안내 기준으로 Excel 2016과 Excel 2019에서는 XLOOKUP을 사용할 수 없습니다. Microsoft 365, Excel 2021, Excel 2024 등 지원되는 버전에서 사용할 수 있습니다.
12. 마무리
XLOOKUP은 처음 보면 복잡해 보이지만 기본 구조는 생각보다 간단합니다.
=XLOOKUP(찾을값, 찾을범위, 가져올범위)
상품코드를 찾고 상품명을 가져오고 싶다면 =XLOOKUP(F2,A2:A10,B2:B10)처럼 사용하고, 가격이나 재고처럼 다른 열의 정보를 가져오고 싶다면 반환 범위만 바꾸면 됩니다.
여기에 "상품 없음" 같은 문구를 추가하거나 여러 열을 한 번에 가져오는 기능까지 익히면 실무에서 반복적으로 입력하던 작업을 상당히 줄일 수 있습니다.
특히 VLOOKUP에서 열 순서 때문에 불편했던 경험이 있다면 XLOOKUP을 사용해보면 차이를 쉽게 느낄 수 있습니다.
찾을 값 → 검색 범위 → 반환 범위 이 세 가지를 먼저 이해하면 XLOOKUP의 대부분의 기본 사용법을 익힐 수 있습니다.
댓글 쓰기