Excel의 XLOOKUP: 구문, 예제, 그리고 VLOOKUP보다 나은 점
By Mirko Peters · · Updated
Excel XLOOKUP을 정리합니다. 구문과 각 인수, 정확히 일치하는 값, 와일드카드 일치, 마지막 일치 찾기, 여러 열 반환 예제를 다루고 VLOOKUP과 어떻게 다른지도 설명합니다.
Automatically translated with AI from the original post. Read the English original
Excel의 XLOOKUP은 한 범위에서 값을 찾고 다른 범위에서 일치하는 항목을 반환합니다: =XLOOKUP(lookup_value, lookup_array, return_array). 직원 ID를 입력하고 ID 열을 지정한 다음 이름 열을 지정하면 이름이 반환됩니다. 이 글은 습관적으로 VLOOKUP을 쓰는 Excel 사용자나 동료의 파일에서 XLOOKUP을 처음 본 분들을 위한 글입니다. 구문, 여섯 개의 인수, 실무에서 자주 쓰는 경우의 예제, VLOOKUP과의 비교, 그리고 자주 마주치는 오류 목록을 다룹니다.
핵심 포인트
- XLOOKUP은 찾을 값, 조회 범위, 반환 범위를 받습니다: =XLOOKUP(lookup_value, lookup_array, return_array). 첫 번째 일치 항목을 찾아 반환 범위에서 해당 항목을 반환합니다.
- 조회 열의 왼쪽도 검색할 수 있고, 기본값이 정확한 일치입니다. VLOOKUP은 이 두 가지를 할 수 없거나 기본으로 하지 않습니다.
- 네 번째 인수인 if_not_found는 #N/A 오류를 사용자가 지정한 텍스트로 바꿉니다. 이 인수를 생략하면 일치하는 값이 없을 때 #N/A가 반환됩니다.
- match_mode를 2로 설정하면 와일드카드가 켜지고, -1과 1은 각각 다음으로 작은 항목 또는 다음으로 큰 항목을 찾습니다. search_mode가 -1이면 마지막 항목부터 첫 번째 항목까지 검색합니다.
- Microsoft 문서에는 XLOOKUP이 Microsoft 365, 2021, 2024에서 지원된다고 나와 있으며, Excel 2016과 2019에 대한 설명은 서로 맞지 않습니다. 따라서 공유 파일에 의존하기 전에 반드시 테스트하세요.
아래의 구문과 옵션은 Microsoft의 XLOOKUP 함수 문서를 따릅니다. 해당 문서에 없는 패턴은 본문에서 그렇다고 밝혀 두었습니다. 이 함수는 Excel의 함수이며, VLOOKUP 도움말 페이지를 열면 Microsoft가 직접 더 나은 선택으로 안내하는 함수이기도 합니다.
구문과 여섯 개의 인수
전체 수식은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
처음 세 개의 인수는 필수이고, 마지막 세 개는 선택 사항입니다.
lookup_value는 찾으려는 값입니다. 예를 들어 D2 셀의 ID가 해당됩니다. 이 인수를 생략하면 XLOOKUP은 조회 배열에서 찾은 빈 셀을 반환합니다.lookup_array는 검색할 범위 또는 배열입니다. 예: A2:A100.return_array는 값을 반환할 범위 또는 배열입니다. 예: B2:B100.if_not_found는 유효한 일치 항목이 없을 때 표시할 텍스트입니다. 생략하면 #N/A가 표시됩니다.match_mode는 일치 방식을 설정합니다.0은 정확한 일치이며 기본값입니다.-1은 정확한 일치 또는, 일치하는 값이 없으면 다음으로 작은 항목을 뜻합니다.1은 정확한 일치 또는 다음으로 큰 항목입니다.2는 와일드카드 일치이며, 이때*,?,~는 특별한 의미를 가집니다.search_mode는 검색 방향을 설정합니다.1은 첫 번째 항목부터 마지막 항목까지 검색하며 기본값입니다.-1은 마지막 항목부터 첫 번째 항목까지 검색합니다.2와-2는 각각 오름차순 또는 내림차순으로 정렬된 데이터에 대한 이진 검색입니다. Microsoft는 정렬되지 않은 데이터에 이진 모드를 사용하면 잘못된 결과가 반환된다고 경고합니다.
조회 배열과 반환 배열의 크기를 같게 유지하십시오. 조회 배열이 2행부터 100행까지라면 반환 배열도 2행부터 100행까지여야 합니다. Microsoft 문서에 이것이 규칙으로 명시되어 있지는 않지만, 범위가 맞지 않으면 조회 결과가 잘못된 행을 가리키거나 오류가 나는 것이 보통의 원인입니다.
기본 조회와 찾지 못한 경우의 인수
직원 ID가 A2:A100에 있고 이름이 B2:B100에 있다고 가정하겠습니다. D2에 찾고자 하는 ID를 입력합니다.
- 결과가 표시될 셀, 예를 들어 E2를 클릭합니다.
=XLOOKUP(D2,A2:A100,B2:B100)를 입력합니다.- Enter를 누릅니다.
ID에 해당하는 이름이 나타납니다. XLOOKUP은 처음 찾은 일치 항목을 반환합니다. ID가 없으면 #N/A가 표시됩니다.
보다 친숙한 메시지를 표시하려면 네 번째 인수를 추가합니다.
=XLOOKUP(D2,A2:A100,B2:B100,"No record found")
이제 목록에 없는 ID는 "No record found"를 표시합니다. 다른 사람이 읽는 시트에서는 이 방법을 사용하십시오. 한 가지 주의할 점은 이런 메시지가 실제 문제를 가릴 수 있다는 것입니다. 모든 행에 "No record found"가 표시된다면 데이터가 아니라 범위에 문제가 있는 것이므로, 먼저 값을 알고 있는 ID로 테스트해 보십시오.
왼쪽 방향으로 조회하기
VLOOKUP은 조회 열의 왼쪽에 있는 열의 값을 반환할 수 없습니다. XLOOKUP은 조회 범위와 반환 범위가 분리되어 있으므로 이것이 가능합니다. 이름이 C열에 있고 가져오려는 ID가 A열에 있다면 다음과 같이 입력합니다.
=XLOOKUP(F2,C2:C100,A2:A100)
Microsoft 문서에도 이렇게 명시되어 있습니다. 조회 열은 반환 열의 왼쪽이나 오른쪽 어디에나 있을 수 있습니다.
일치 모드: 와일드카드와 근사값
와일드카드 일치
와일드카드를 사용하려면 match_mode를 2로 설정합니다. 별표(*)는 임의의 문자 수를, 물음표(?)는 정확히 한 글자를 나타냅니다. 물결표(~)를 사용하면 별표나 물음표 문자 자체를 검색할 수 있습니다.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)
이 수식은 "Jan"으로 시작하는 첫 번째 항목을 반환합니다. 2를 빼먹으면 Excel은 "Jan*"이라는 문자 그대로의 텍스트를 찾으며, 결과로 찾지 못함 메시지를 반환합니다. 이것이 와일드카드에서 가장 흔한 실수입니다. XLOOKUP은 첫 번째 일치 항목만 반환하므로 와일드카드는 가능한 한 구체적으로 지정하십시오. 느슨한 패턴은 의도하지 않은 항목과 일치할 수 있습니다.
바로 아래 또는 위의 항목 찾기
모드 -1과 1은 세율, 수수료 구간, 가격 구간처럼 단계가 있는 기준표에 사용합니다. 기준값을 A2:A6에, 요율을 B2:B6에 두고 E2에서 소득을 조회한다고 가정하겠습니다.
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
-1을 사용하면 Excel은 정확히 일치하는 값을 반환하고, 일치하는 값이 없으면 그보다 작은 다음 기준값을 반환합니다. -1 앞의 빈 인수 자리는 찾지 못한 경우 인수를 건너뛰는 역할을 합니다. Microsoft의 근사 일치 예제에서는 1을 사용해 다음으로 큰 소득 값에 해당하는 세율을 반환하므로, 표의 구조에 맞는 방향을 선택하십시오. 이전 방식의 근사 일치 VLOOKUP과 달리, 이 모드들은 기준값을 정렬하지 않아도 됩니다. 정렬이 필요한 것은 이진 검색 모드입니다.
아래에서부터 검색하기: 마지막 일치 항목
같은 값이 여러 번 나타나면 XLOOKUP은 처음 만나는 항목을 반환합니다. 마지막 항목을 가져오려면 search_mode를 -1로 설정하여 끝에서부터 검색하십시오.
=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)
시트에 주문이 날짜순으로 정렬되어 있다면, 이 수식은 고객별 가장 최근 항목을 반환합니다. 0은 일치 방식을 정확히 일치로 유지합니다. "최신 가격", "마지막 연락" 등 VLOOKUP으로는 다루기 까다로웠던 질문에 쓰는 패턴입니다.
여러 열을 한 번에 반환하기
Microsoft 문서에 따르면, VLOOKUP과 달리 XLOOKUP은 여러 열을 하나의 배열로 반환할 수 있습니다. 반환 범위를 넓히면 됩니다.
=XLOOKUP(D2,A2:A100,B2:D100)
이 수식은 B열에서 찾은 값을 셀에 넣고, C열과 D열의 값은 오른쪽 셀로 확장(spill)하여 표시합니다. 따라서 그만큼 공간을 비워 두어야 합니다. 오른쪽 셀에 이미 데이터가 있으면 Excel은 덮어쓰지 않고 확장 오류를 표시합니다. Microsoft 예제에서도 하나의 수식으로 직원 이름과 부서를 함께 반환합니다.
양방향 조회
Microsoft는 두 개의 XLOOKUP 함수를 중첩하여 행과 열이 교차하는 지점의 값을 찾는 방법을 보여 줍니다. 문서에 나온 패턴은 다음과 같습니다.
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
내부 함수는 C3의 머리글과 일치하는 열을 고릅니다. 그런 다음 외부 함수가 행 레이블에서 D2를 찾고, 그 열에서 값을 반환합니다. Microsoft는 이를 INDEX와 MATCH 조합에 비유합니다. 제품은 세로로, 월은 가로로 배치된 보고서가 있다면, 이 방식이 더 복잡한 수식을 대체할 수 있습니다.
여러 조건
XLOOKUP은 조회 값을 하나만 받으며, Microsoft 문서 페이지에는 여러 조건을 사용하는 예제가 없습니다. 흔히 쓰이는 방법은 Excel이 두 조건을 한 번에 검사하도록 하는 것입니다. 범주가 A열에, 상태가 B열에, 가격이 C열에 있다고 가정해 보겠습니다. F2의 범주와 G2의 상태에 해당하는 가격을 찾으려면 다음과 같이 합니다.
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")
각 비교는 TRUE 또는 FALSE를 반환합니다. 이들을 곱하면 두 조건이 모두 참인 행에서만 1이 되며, XLOOKUP은 첫 번째 1을 찾습니다. 현재 Microsoft 365 버전에서 작동하는 방식이지만 Microsoft의 함수 문서 페이지에는 나와 있지 않으므로, 작은 샘플 데이터로 먼저 테스트해 보십시오. 두 필드를 결합한 도우미 열을 만드는 것은 이전 방식의 대안입니다. 값 사이에 구분 기호를 넣고, 예를 들어 =A2&"|"&B2처럼 작성한 뒤, 같은 방식으로 결합된 텍스트를 조회하십시오.
XLOOKUP과 VLOOKUP 비교
Microsoft의 VLOOKUP 페이지에서는 구문을 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])로 제시하며, 조회 값이 범위의 첫 번째 열에 있어야 한다고 설명합니다. range_lookup을 생략하면 VLOOKUP은 근사 일치를 수행하며, 이때 첫 번째 열이 정렬되어 있다고 가정합니다. 정확한 일치를 원하면 FALSE로 설정하십시오. 같은 페이지에서는 XLOOKUP 사용을 권장하며, XLOOKUP은 어느 방향으로든 작동하고 기본적으로 정확한 일치를 반환한다고 설명합니다.
A열에 ID, B열에 이름이 있을 때 두 함수로 같은 작업을 하는 예는 다음과 같습니다.
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
일상적인 사용에서 중요한 차이점은 다음과 같습니다.
- VLOOKUP은 열 번호로 결과를 참조하므로, 표에 열을 삽입하면 그 번호가 가리키는 대상이 바뀝니다. XLOOKUP은 반환 범위를 직접 참조합니다.
- VLOOKUP은 왼쪽을 조회할 수 없습니다. XLOOKUP은 조회할 수 있습니다.
- VLOOKUP의 기본값은 근사 일치입니다. XLOOKUP의 기본값은 정확한 일치입니다.
- XLOOKUP에는 찾지 못했을 때의 인수와 검색 방향이 내장되어 있습니다.
기존 통합 문서를 다시 작성할 필요는 없습니다. VLOOKUP이 제대로 작동한다면 그대로 두십시오. 새로운 수식이나 왼쪽 조회, 마지막 일치, 여러 반환 열이 필요한 조회에는 XLOOKUP을 사용하십시오.
XLOOKUP을 지원하는 Excel 버전
여기서는 주의가 필요합니다. Microsoft 문서 페이지에는 Windows 및 Mac용 Microsoft 365 Excel, Excel 2024, Excel 2021, 그리고 iPad, iPhone, Android 앱이 XLOOKUP을 지원하는 것으로 나와 있습니다. 같은 페이지의 참고 사항에는 Excel 2016 및 2019에서는 사용할 수 없지만 최신 버전에서 만든 통합 문서에는 나타날 수 있다고 적혀 있습니다. 그런데 적용 대상 항목에는 2016과 2019가 포함되어 있습니다. 내용이 서로 모순되므로, Excel 2016과 2019를 안전한 것으로 여기지 마십시오. 또한 해당 페이지에는 Excel 웹 버전이 나와 있지 않습니다.
이전 버전을 사용하는 사람에게 통합 문서를 보내면, 값이 표시되어야 할 자리에 수식 오류가 나타날 수 있습니다. 수신자가 어떤 버전을 쓰는지 확인하고, 여러 사람에게 배포하는 파일이라면 현재 사용 중인 가장 오래된 버전에서 테스트하십시오. 테스트할 수 없다면 해당 파일에는 기존 조회 함수를 사용하십시오.
흔한 실수와 오류
- 와일드카드를 사용할 때
match_mode를 2로 설정하지 않으면, 검색이 와일드카드 문자를 그대로 찾습니다. - 정렬되지 않은 데이터에 이진
search_mode(2 또는 -2)를 사용하는 경우입니다. Microsoft에 따르면 이 경우 결과는 잘못된 것입니다. - 조회 범위와 반환 범위의 크기가 다른 경우입니다. 두 범위의 길이를 같게 맞추십시오.
- 보이지 않는 공백이나 텍스트로 저장된 숫자가 원인인 경우입니다. "100"과 100은 서로 다르므로 조회가 실패하고 #N/A가 표시됩니다. TRIM 함수로 데이터를 정리하거나 숫자로 변환하십시오.
- 찾지 못했을 때 친절한 메시지를 표시해 문제를 가리는 경우입니다. 결과를 신뢰하기 전에 값을 하나 정해 알려진 데이터로 테스트하십시오.
- 결과가 여러 셀에 펼쳐져야 하는데 반환 배열이 이미 데이터가 있는 셀을 가리키는 경우입니다.
- 매우 큰 시트에서 전체 열 참조를 사용하는 경우입니다. 범위를 제한하거나 Excel 표를 사용하면 Excel이 처리하는 셀 수가 줄어듭니다.
함께 살펴볼 관련 주제
- XLOOKUP과 INDEX 및 MATCH 비교. 두 방식 모두 왼쪽 조회를 할 수 있으며, INDEX와 MATCH 조합은 이전 Excel 버전에서도 작동합니다. Excel 2016 또는 2019 사용자와 파일을 공유한다면 이 내용을 확인해 보십시오.
- 여러 결과. XLOOKUP은 첫 번째 일치 항목만 반환합니다. 일치하는 모든 항목을 찾으려면 FILTER 함수를 살펴보십시오.
- Excel 표와 XLOOKUP. 구조적 참조를 사용하면 데이터가 늘어날 때 범위도 함께 늘어나며, 수식을 읽기도 쉽습니다.
- Power Query 데이터. 데이터를 표에 로드한 다음에는 XLOOKUP으로 다른 표와 똑같이 조회할 수 있습니다. 먼저 쿼리를 새로 고치고, 이후에 결과를 확인하십시오.
- 웹용 Excel. Microsoft의 XLOOKUP 문서 페이지에는 이 환경이 나와 있지 않습니다. 파일을 공유하기 전에 본인의 환경에서 동료들이 이 함수를 사용할 수 있는지 직접 확인하십시오.
다음 단계
현재 통합 문서에서 VLOOKUP 하나를 골라 XLOOKUP으로 다시 작성해 보십시오. 찾을 수 없을 때 표시할 메시지를 추가하고, 잘 아는 ID 몇 개로 두 결과를 비교하십시오. 결과가 같다면 Excel에서 새로운 XLOOKUP을 작성할 때마다 재사용할 수 있는 패턴을 갖게 된 것입니다.