이번 포스팅에서는 데이터 조회의 게임 체인저로 불리는 엑셀 XLOOKUP 함수 사용법을 기본 구조부터, 까다로웠던 VLOOKUP을 완벽하게 대체하는 방법, 보기 싫은 #N/A 오류를 단번에 없애는 법, 그리고 두 가지 이상의 조건을 동시에 찾는 다중 조건 활용법까지 1분 만에 마스터할 수 있도록 완벽하게 정리해 보았습니다.
참고로 아래 글은 아래 테스트 날짜와 기기, 앱 버전에서 작동 여부를 직접 확인 완료 하였습니다.
테스트 날짜 : 26년 6월 19일
윈도우 버전 : Windows 11 Home / 25H2
사용기기 : Microsoft Surface Pro 11th Edition
프로그램명/버전 : Microsoft 365 / Excel / 버전 2510
엑셀(Excel)로 업무를 하다 보면, 특정 값을 기준으로 다른 표에서 원하는 정보를 끌어와야 하는 순간이 반드시 찾아옵니다. 그동안 이 작업은 VLOOKUP 함수의 몫이었는데요, VLOOKUP은 찾을 값이 항상 표의 첫 번째 열에 있어야 하고, 반환할 값의 ‘열 번호’를 일일이 세어 입력해야 하며, 값이 없으면 #N/A 오류를 그대로 뱉어내는 등 은근히 불편한 구석이 많았습니다.
이런 한계를 한 번에 해결하기 위해 마이크로소프트가 새롭게 내놓은 것이 바로 엑셀 XLOOKUP 함수입니다. XLOOKUP은 기존 VLOOKUP, HLOOKUP, LOOKUP을 모두 대체하는 차세대 조회 함수로, 방향에 상관없이 동작하고 기본값으로 정확히 일치하는 값을 찾아주기 때문에 훨씬 쉽고 직관적입니다.
이번 포스팅에서는 엑셀 XLOOKUP 함수 사용법에 대해 정리해보았습니다. 우선 엑셀 XLOOKUP 함수의 기본 구조와 사용법을 알아보고, VLOOKUP을 완벽하게 대체하는 방법, 엑셀 XLOOKUP 함수로 #N/A 오류를 해결하는 방법, 마지막으로 두 가지 이상 조건을 한 번에 찾는 다중 조건 활용법까지 차례대로 정리해보았습니다.
엑셀 XLOOKUP 함수란? VLOOKUP과 무엇이 다를까
엑셀 XLOOKUP 함수는 지정한 범위(또는 배열)에서 값을 검색한 뒤, 가장 먼저 일치하는 값에 해당하는 결과를 반환하는 함수입니다. VLOOKUP과 가장 큰 차이는 ‘찾을 범위’와 ‘반환할 범위’를 따로 지정한다는 점입니다. 덕분에 찾는 기준 열이 어디에 있든, 결과 열이 그 왼쪽에 있든 오른쪽에 있든 상관없이 자유롭게 값을 가져올 수 있습니다.
한 가지 주의할 점은 사용 가능 버전입니다. 엑셀 XLOOKUP 함수는 Microsoft 365 또는 엑셀 2021 이상 버전에서만 사용할 수 있습니다. 엑셀 2016, 2019 이하 버전에서는 함수 목록에 아예 나타나지 않으니 본인의 엑셀 버전을 먼저 확인해 주세요.
엑셀 XLOOKUP 함수의 기본 구조(인수)는 아래와 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) ① lookup_value : 찾으려는 값 (필수) ② lookup_array : 값을 찾을 범위 (필수) ③ return_array : 결과로 반환할 범위 (필수) ④ if_not_found : 값을 못 찾았을 때 표시할 내용 (선택) ⑤ match_mode : 일치 방식 (0=정확히 일치(기본), -1/1=근사값, 2=와일드카드) ⑥ search_mode : 검색 방향 (1=처음부터(기본), -1=마지막부터)
이름처럼 보이는 6개의 인수가 있지만, 실제로 가장 많이 쓰는 것은 앞쪽 3개(찾을 값·찾을 범위·반환할 범위)뿐입니다. 나머지는 필요할 때만 추가로 넣으면 됩니다. 정리하면, 엑셀 XLOOKUP 함수가 VLOOKUP보다 좋은 점은 다음과 같습니다.
- 왼쪽 값 검색 가능 : 찾을 값이 1열에 없어도, 결과가 왼쪽에 있어도 OK
- 열 번호 셀 필요 없음 : 몇 번째 열인지 세지 않고 결과 범위를 바로 지정
- 기본값이 정확히 일치 : VLOOKUP처럼 마지막에 FALSE를 안 붙여도 됨
- 오류 처리 내장 : if_not_found 인수로 #N/A를 깔끔하게 대체
- 여러 값 한 번에 반환 : 결과 범위를 여러 열로 지정해 동시 조회
엑셀 XLOOKUP 함수 기본 사용법 (1분 마스터)
가장 기본적인 엑셀 XLOOKUP 함수 사용법부터 익혀보겠습니다. 예시로 B열부터 F열까지 사번 · 이름 · 부서 · 직급 · 연봉이 정리된 직원 명부가 있다고 가정하겠습니다. 여기서 ‘사번’을 입력하면 해당 직원의 ‘연봉’을 찾아오도록 만들어 보겠습니다.
1. 찾을 값(lookup_value) 선택
결과를 표시할 셀에 =XLOOKUP(를 입력한 뒤, 첫 번째 인수로 찾을 기준값이 입력된 셀(예: 사번이 들어 있는 I2 셀)을 클릭합니다.
2. 찾을 범위(lookup_array) 선택
두 번째 인수로 그 값을 찾아야 할 범위, 즉 사번이 나열된 열(B2:B11)을 드래그해 지정합니다.
3. 반환할 범위(return_array) 선택 후 엔터
세 번째 인수로 실제 결과로 가져올 범위, 즉 연봉이 나열된 열(F2:F11)을 지정하고 괄호를 닫은 뒤 엔터를 누릅니다. 최종 수식은 아래와 같습니다.
=XLOOKUP(I2, B2:B11, F2:F11)
이렇게 단 세 개의 인수만으로 원하는 값을 정확히 찾아옵니다. VLOOKUP처럼 ‘몇 번째 열’인지 숫자를 셀 필요도, 마지막에 정확히 일치(FALSE)를 따로 붙일 필요도 없습니다. 엑셀 XLOOKUP 함수는 별도 설정이 없으면 항상 정확히 일치하는 값을 기본으로 찾기 때문입니다.
엑셀 XLOOKUP 함수로 VLOOKUP 완벽 대체하기 (왼쪽 값 찾기)
VLOOKUP의 가장 큰 약점은 찾을 값이 반드시 범위의 첫 번째 열에 있어야 한다는 것입니다. 그래서 결과 값이 기준 열보다 왼쪽에 있으면 VLOOKUP으로는 값을 가져올 수 없었고, 어쩔 수 없이 INDEX·MATCH 함수를 조합해야 했습니다.
하지만 엑셀 XLOOKUP 함수는 ‘찾을 범위’와 ‘반환할 범위’를 따로 지정하므로 이 문제가 깔끔하게 해결됩니다. 앞선 직원 명부에서 이번에는 ‘이름’으로 그 왼쪽에 있는 ‘사번’을 찾아보겠습니다.
=XLOOKUP(I2, C2:C11, B2:B11)
이름이 들어 있는 C열에서 값을 찾고, 그보다 왼쪽에 있는 B열(사번)을 결과로 반환했습니다. VLOOKUP이라면 불가능했을 작업이 엑셀 XLOOKUP 함수에서는 너무도 간단하게 처리됩니다.
한 걸음 더 나아가, 엑셀 XLOOKUP 함수는 여러 개의 값을 한 번에 가져오는 것도 가능합니다. 반환할 범위를 한 열이 아닌 여러 열로 지정하면, 그 결과가 옆 셀로 자동으로 펼쳐집니다. 예를 들어 사번 하나로 이름·부서·직급을 한 번에 불러오려면 아래와 같이 입력합니다.
=XLOOKUP(I2, B2:B11, C2:E11)
이처럼 엑셀 XLOOKUP 함수 하나로 VLOOKUP과 INDEX·MATCH가 하던 일을 모두 대체할 수 있습니다.
엑셀 XLOOKUP 함수 #N/A 오류 해결 방법 (if_not_found 인수)
조회 함수를 쓸 때 가장 거슬리는 것이 바로 찾는 값이 없을 때 나타나는 #N/A 오류입니다. VLOOKUP에서는 이 오류를 숨기려면 IFERROR 함수로 수식 전체를 한 번 더 감싸야 했는데요, 수식이 길어지고 복잡해지는 단점이 있었습니다.
엑셀 XLOOKUP 함수는 네 번째 인수인 if_not_found를 통해 이 문제를 함수 하나로 해결합니다. 찾는 값이 없을 때 보여줄 내용을 큰따옴표 안에 직접 적어주기만 하면 됩니다.
=XLOOKUP(I2, B2:B11, F2:F11, "조회 결과 없음")
이렇게 입력하면 해당 사번이 명부에 없을 때 #N/A 대신 ‘조회 결과 없음’이라는 안내 문구가 표시됩니다.
참고로 오류 자리에 빈칸을 표시하고 싶다면 큰따옴표 두 개("")를, 숫자 0을 표시하고 싶다면 따옴표 없이 0을 넣으면 됩니다. 이 작은 인수 하나 덕분에 엑셀 XLOOKUP 함수는 보고서나 대시보드를 훨씬 깔끔하게 만들어 줍니다.
엑셀 XLOOKUP 함수 다중 조건 검색 방법 (2개 이상 조건)
실무에서는 단순히 사번 하나가 아니라, ‘이름’과 ‘부서’가 동시에 일치하는 값을 찾아야 하는 경우가 많습니다. 동명이인이 있을 때처럼 말이죠. 이럴 때 엑셀 XLOOKUP 함수로 다중 조건 검색을 구현하는 방법은 크게 두 가지입니다.
1. 조건과 범위를 &(앰퍼샌드)로 결합하는 방법
찾을 값들을 & 기호로 이어 붙이고, 찾을 범위도 똑같이 &로 이어 붙입니다. 이름(I2)과 부서(J2)가 모두 일치하는 연봉을 찾는 수식은 아래와 같습니다.
=XLOOKUP(I2&J2, C2:C11&D2:D11, F2:F11)
2. 조건의 곱(*)을 활용하는 방법
각 조건이 일치하는지(TRUE=1, FALSE=0)를 곱해서, 두 조건이 모두 참(1)인 행을 찾는 방식입니다. 첫 번째 인수에는 찾을 값으로 숫자 1을 넣습니다.
=XLOOKUP(1, (C2:C11=I2)*(D2:D11=J2), F2:F11)
두 방법 모두 별도의 배열 수식 입력(Ctrl+Shift+Enter) 없이 그냥 엔터만 눌러도 동작합니다. 이렇게 엑셀 XLOOKUP 함수 하나로 두 개 이상의 조건을 동시에 만족하는 값까지 손쉽게 찾아낼 수 있습니다.
엑셀 XLOOKUP 함수 활용 개인 노하우
단순한 사용법을 넘어, 엑셀 XLOOKUP 함수를 실무에서 더 안정적으로 쓰기 위한 노하우를 공유합니다.
노하우 1. 수식 복사를 대비한 절대 참조($) 처리
찾을 범위와 반환할 범위는 데이터가 고정되어 있으므로, 수식을 아래로 복사해도 범위가 밀리지 않도록 절대 참조로 고정해 주는 것이 좋습니다. 범위를 지정한 뒤 키보드 F4 키를 누르면 $B$2:$B$11처럼 $ 기호가 붙어 범위가 고정됩니다. 이 습관 하나로 엑셀 XLOOKUP 함수 수식이 깨지는 실수를 크게 줄일 수 있습니다.
노하우 2. 근사값·구간 검색과 마지막 값 찾기
정확히 일치가 아니라 구간(예: 점수 구간별 등급)을 찾고 싶다면 다섯 번째 인수 match_mode에 -1(작거나 같은 근사값) 또는 1(크거나 같은 근사값)을 넣습니다. 또한 중복 값이 여러 개일 때 가장 마지막 값을 찾고 싶다면 여섯 번째 인수 search_mode에 -1을 넣어 검색 방향을 뒤집으면 됩니다.
노하우 3. 표(Table)로 변환해 관리하기
데이터가 계속 늘어나는 명부라면, 데이터 영역을 ‘표 서식(Ctrl+T)’으로 변환해 두는 것을 추천합니다. 표로 만들면 행이 추가되어도 엑셀 XLOOKUP 함수가 참조하는 범위가 자동으로 확장되어 매번 범위를 수정할 필요가 없어집니다.
자주 묻는 질문 (FAQ)
Q1. 함수를 입력하려는데 엑셀 XLOOKUP 함수가 목록에 뜨지 않습니다.
A1. 사용 중인 엑셀 버전 문제일 가능성이 높습니다. 엑셀 XLOOKUP 함수는 Microsoft 365 또는 엑셀 2021 이상에서만 제공되며, 엑셀 2016·2019 이하 버전에서는 지원되지 않습니다. 인수별 상세 설명과 다양한 공식 예제는 Microsoft 공식 XLOOKUP 함수 지원 문서에서 확인하실 수 있습니다.
Q2. 구버전 엑셀을 쓰는 동료와 파일을 공유해도 괜찮을까요?
A2. 주의가 필요합니다. 최신 엑셀에서 엑셀 XLOOKUP 함수로 만든 파일을 2019 이하 버전에서 열면, 해당 셀이 _xlfn.XLOOKUP 또는 #NAME? 형태로 깨져 보일 수 있습니다. 구버전 사용자와 공유가 잦다면 결과 값을 ‘값 붙여넣기’로 고정해 전달하는 것이 안전합니다.
Q3. #N/A 대신 0이나 빈칸으로 표시하고 싶습니다.
A3. 네 번째 인수 if_not_found를 활용하면 됩니다. 빈칸으로 두려면 ""를, 숫자 0으로 표시하려면 0을 입력하세요. 예: =XLOOKUP(I2, B2:B11, F2:F11, “”)
Q4. 다중 조건으로 찾는데 자꾸 0이나 오류만 나옵니다.
A4. 조건의 곱(*)을 사용하는 방식은 두 조건이 ‘정확히’ 일치해야 결과가 나옵니다. 앞뒤 공백이나 셀 서식 차이가 없는지 확인해 보세요. 또한 이 방식은 동적 배열(스필)을 지원하는 Microsoft 365·엑셀 2021 이상에서 가장 안정적으로 동작합니다.
Q5. 같은 값이 여러 개일 때 가장 마지막 항목을 찾고 싶습니다.
A5. 여섯 번째 인수 search_mode에 -1을 넣어 검색 방향을 마지막에서 처음으로 바꾸면 됩니다. 예: =XLOOKUP(I2, B2:B11, F2:F11, , , -1). 중간 인수를 비울 때는 쉼표 자리는 그대로 유지해야 합니다.
핵심 내용 요약
- 사용 버전 : 엑셀 XLOOKUP 함수는 Microsoft 365 또는 엑셀 2021 이상에서만 사용 가능합니다.
- 기본 구조 : =XLOOKUP(찾을 값, 찾을 범위, 반환할 범위) – 앞쪽 3개 인수만으로 대부분 해결됩니다.
- VLOOKUP 대체 : 왼쪽 값도 찾을 수 있고, 결과 범위를 여러 열로 지정하면 여러 값을 한 번에 가져옵니다.
- #N/A 해결 : 네 번째 인수 if_not_found에 표시할 문구를 넣어 오류를 깔끔하게 대체합니다.
- 다중 조건 : 조건과 범위를 &로 결합하거나, 조건의 곱(*)을 활용해 두 개 이상 조건을 동시에 검색합니다.
이번 포스팅에서는 엑셀 XLOOKUP 함수 사용법에 대해 정리해보았습니다. 엑셀 XLOOKUP 함수의 기본 구조와 사용법부터, VLOOKUP을 대체하는 왼쪽 값 찾기, #N/A 오류를 해결하는 방법, 그리고 두 가지 이상 조건을 동시에 찾는 다중 조건 활용법까지 차례대로 정리해보았습니다. 한 번 익혀두면 데이터 정리 속도가 확연히 빨라지니 꼭 활용해 보시기 바랍니다.
앞선 포스팅에서 정리한 엑셀 함수 및 데이터 활용 관련 글은 아래와 같습니다.
Excel 엑셀 소수점 반올림 2가지 방법 (ROUND, MROUND 함수)
엑셀 줄바꿈 안됨? 단축키·자동 줄바꿈 5초 해결 (가장 쉬운 줄바꾸기 설정)
작성자 : Sean Woo
[전공] HCI(인간-컴퓨터 상호작용)·UX(사용자 경험 연구)
[업무] 신기술 기반 사업 전략·기술 기획·디지털 트랜스포메이션 업무
ITmanual.net에서는 실생활, 업무를 진행하면서 느꼈던,
스마트폰(아이폰·갤럭시)·운영체제·각종 앱/프로그램·IT기기 활용 노하우를 실생활 중심 IT 매뉴얼로 정리합니다.