Excel XLOOKUP 함수 사용하는 방법

들어가기 전에

Excel의 VLOOKUP 함수는 의심할 여지 없이 시트에서 특정 키워드를 수직으로 찾는 가장 강력한 도구 중 하나입니다. 하지만 VLOOKUP도 제한이 있으며, XLOOKUP은 제한을 극복할뿐만 아니라 유연한 값을 조회하기 위해 비교적 최근에 등장한 도구입니다. XLOOKUP 함수를 일상적인 워크북에서 어떻게 사용할 수 있는지 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

---

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

• 원문: How to use the XLOOKUP function in Microsoft Excel with Example
• URL: https://www.how2shout.com/how-to/how-to-use-the-xlookup-function-in-microsoft-excel-example.html

---

XLOOKUP 함수 사용 형식

XLOOKUP 함수의 형식은 다음과 같습니다.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

 

각 인수에 대해 알아보겠습니다.

• looup_value(필수): 레코드에서 검색해야 하는 값을 나타냅니다. 이 값은 직접 입력해야 합니다.
• lookup_array(필수): 입력한 값이 배열에서 검색됩니다. lookup_value 값이 있어야 할 행 또는 열을 선택하기만 하면 됩니다.
• return_array(필수): 일치하는 항목이 발견되면 일치하는 레코드에 대한 이 배열의 값이 반환됩니다.
• if_not_found(선택 사항): 일치하는 항목을 찾을 수 없는 경우 사용자 지정 오류 메시지를 표시할 수 있습니다.
• match_mode(선택 사항): 0, -1, 1, 2 사이의 네 가지 값을 입력하여 일치 유형을 선택해야 합니다.
  
  • 0: 정확히 일치하는 항목을 찾습니다. 일치하는 항목을 찾지 못하면 선택한 오류 메시지가 표시됩니다.
  • -1: 정확히 일치하는 항목을 찾을 수 없는 경우 검색에 따라 바로 작은 값을 반환합니다.
  • 1: 정확히 일치하는 항목을 찾지 못하면 검색을 기준으로 가장 큰 값을 바로 반환합니다.
  • 2: 와일드카드 매치에 사용할 수 있으며, '?', '*', '~'는 다른 기능을 합니다.
    • 물음표(?): 입력된 모든 문자를 찾습니다. 예를 들어 Ro?y를 입력하면 Rony, Roxy, Romy 등을 검색합니다.
    • 별표(*): 입력한 길이와 상관없이 모든 문자를 검색합니다. 예를 들어 south*을 입력하면 southeast, southwest 또는 앞에 south가 있는 다른 모든 결과를 검색합니다.
    • 물결표(~)는 물음표, 별표, 틸드를 의미합니다.
• search_mode(선택 사항): 1, -1, 2, -2의 네 가지 검색 모드 중에서 선택할 수 있습니다.
  • 1: 첫 번째 항목부터 검색을 시작합니다. 검색 모드를 입력하지 않으면 기본값이 됩니다.
  • -1: 마지막 항목부터 검색을 시작합니다.
  • 2: 배열을 오름차순으로 정렬해야 하는 이진 검색을 시작합니다.
  • -2: 배열을 내림차순으로 정렬해야 하는 이진 검색을 시작합니다.
  • *2와 -2 모두 배열이 필요에 따라 정렬되지 않으면 잘못된 결과를 얻게 됩니다.
    
    

위의 설명을 통해 XLOOKUP이 VLOOKUP에 비해 얼마나 강력한지 짐작할 수 있습니다. 다양한 작업을 수행하므로 다소 복잡해 보이는 측면이 있습니다만, 아래에서 예제를 통해 자세하게 알아봅니다.

 

예제를 통한 설명

이제 Excel의 XLOOKUP 함수를 더 잘 이해하는 데 도움이 되는 데이터 집합을 살펴보겠습니다. 이 데이터 세트에는 직원 ID, 이름, 부서 등을 포함하여 회사의 일부 직원에 대한 데이터가 포함되어 있습니다.

 

이미지: how2shout

 

이제 XLOOKUP 함수를 사용하여 직원 이름을 기준으로 직원 ID, 현재 국가 이름, 도시를 가져오고자 합니다. 이 경우에도 VLOOKUP 함수를 사용할 수 있지만, 나중에 설명하겠지만 한계가 있습니다. 셀 S1에 이름을 입력하면 해당 직원 ID, 국가, 도시 이름이 자동으로 셀 S2, S3, S4에 입력됩니다.

 

S2, S3, S4 셀에는 다음과 같은 수식을 작성합니다.

S2: =XLOOKUP(S1,B2:B1001,A2:A1001,"Not found")
S3: =XLOOKUP(S1,B2:B1001,N2:N1001,"Not found")
S4: =XLOOKUP(S1,B2:B1001,O2:O1001,"Not found")

 

이미지: how2shout

 

이해를 돕기 위해 스크린샷에서 "=" 기호를 제거했습니다. XLOOKUP을 구현하기 전에 "="를 사용해야 합니다. 여기서는 "If_not_found" 인수를 제외하고는 추가 인수를 사용하지 않았습니다. 하지만 하나를 사용할 수 있습니다.

 

이미지: how2shout

 

XLOOKUP 함수의 파워

지금까지 XLOOKUP 사용법에 대한 기본 가이드였습니다. 이러한 기본 구현 외에도, XLOOKUP은 여러 기능을 하나의 우산 아래에 제공하는 강력한 새 Microsoft Excel 조회 기능입니다. 이 기능이 왜 그렇게 강력한지, 그리고 VLOOKUP이나 HLOOKUP보다 우위에 있는지 살펴보겠습니다.

• 검색 값을 찾을 수 없는 경우 오류 메시지를 표시하려면 함수에 IFERROR를 중첩해야 하는 VLOOKUP 또는 HLOOKUP과 달리 XLOOKUP을 사용하여 오류 메시지를 표시하도록 설정할 수 있습니다.
• Excel에서 조회하려는 방식에 따라 VLOOKUP 또는 HLOOKUP을 사용해야 합니다. 그러나 XLOOKUP을 사용하면 값을 검색하고 반환해야 하는 배열의 주소만 지정하면 되므로 수직 및 수평 검색이 모두 가능합니다.
• VLOOKUP은 값을 찾은 위치의 오른쪽에서만 검색할 수 있습니다. 그러나 XLOOKUP의 경우 배열의 주소만 지정하면 되기 때문에 좌우 모두 검색할 수 있습니다.
• 시트에 새 열 또는 행을 삽입하는 경우, VLOOKUP 또는 HLOOKUP과 달리 작업으로 인해 배열이 이동하더라도 XLOOKUP의 결과는 변경되지 않습니다.

 

마치며

XLOOKUP 함수는 Excel 2019에 도입된 Microsoft Excel의 비교적 새로운 함수입니다. 이상으로 XLOOKUP 함수의 기본적인 기능에 대해 알아보았습니다. XLOOKUP은 VLOOKUP 함수가 할 수 없는 다양한 작업을 처리할 수 있습니다.

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.