VLOOKUP 대신 XLOOKUP: 엑셀 검색 함수의 새로운 표준

엑셀 검색 함수의 진화: VLOOKUP의 한계와 XLOOKUP의 등장

엑셀에서 특정 데이터를 찾아오는 작업은 실무에서 가장 빈번하게 사용되는 기능 중 하나입니다. 오랫동안 VLOOKUP 함수는 이 작업을 위한 표준으로 자리매김해 왔지만, 몇 가지 고질적인 한계점 때문에 사용자들은 INDEX와 MATCH 함수를 조합하여 복잡한 수식을 사용해야만 했습니다. 2019년 엑셀 365(현재 Microsoft 365) 및 엑셀 2021부터 도입된 XLOOKUP 함수는 이러한 VLOOKUP의 단점을 해결하고 더욱 강력하고 유연한 검색 기능을 제공하며, 이제는 엑셀 검색 함수의 새로운 표준으로 자리 잡고 있습니다.

이 글에서는 VLOOKUP의 주요 한계를 살펴보고, XLOOKUP이 어떻게 이 문제들을 해결하는지, 그리고 실제 업무에서 VLOOKUP 수식을 XLOOKUP으로 전환하는 방법과 흔히 발생하는 오류를 처리하는 노하우를 상세히 안내해 드립니다.

VLOOKUP의 주요 한계점 이해하기

VLOOKUP 함수는 특정 값을 찾아 해당 값과 같은 행에 있는 다른 열의 데이터를 반환하는 데 사용됩니다. 하지만 이 과정에서 몇 가지 제약이 따릅니다.

1. 조회 방향의 제약: 항상 오른쪽만 검색

VLOOKUP의 가장 큰 한계 중 하나는 조회하려는 값이 반드시 참조 범위의 첫 번째 열에 있어야 하며, 반환하려는 값은 첫 번째 열의 오른쪽에 위치해야 한다는 점입니다. 즉, VLOOKUP은 왼쪽 방향으로 값을 조회할 수 없습니다. 만약 찾는 키 값이 첫 번째 열이 아니라 다른 열에 있고, 그 키 값의 왼쪽에 있는 데이터를 가져와야 한다면 VLOOKUP 단독으로는 불가능합니다. 이 경우 INDEX와 MATCH 함수를 조합하는 복잡한 수식을 사용해야 했습니다.

2. 열 번호에 대한 의존성

VLOOKUP 함수는 반환할 데이터가 있는 열의 번호를 숫자로 직접 지정해야 합니다. 이 방식은 데이터 구조가 변경될 때 문제를 일으킵니다. 예를 들어, 참조 범위 내에서 열을 삽입하거나 삭제하면, 기존에 지정했던 열 번호가 틀어져서 잘못된 값을 가져오거나 오류가 발생할 수 있습니다. 이로 인해 수식을 수동으로 수정해야 하는 번거로움이 있었습니다.

3. 기본 검색 모드의 문제점

VLOOKUP 함수의 일치 옵션(네 번째 인수)의 기본값은 ‘유사 일치(TRUE)’입니다. 실무에서는 대부분 ‘정확히 일치(FALSE 또는 0)’를 사용하여 정확한 값을 찾는 경우가 많으므로, 이 부분을 명시적으로 지정하지 않으면 의도치 않은 결과가 발생할 수 있습니다. 특히 대량의 데이터를 다룰 때 이로 인한 오류는 치명적일 수 있습니다.

4. 오류 처리의 번거로움

VLOOKUP 함수는 찾으려는 값이 없을 때 #N/A 오류를 반환합니다. 이 오류를 사용자 친화적인 메시지로 바꾸려면 IFNA 또는 IFERROR 함수를 별도로 사용하여 수식을 중첩해야 했습니다.

5. 중복 값 처리의 한계

VLOOKUP은 참조 범위 내에 찾으려는 값이 여러 개 있을 경우, 가장 먼저 발견되는 첫 번째 값만 반환합니다. 만약 중복되는 모든 값을 가져와야 한다면 VLOOKUP 단독으로는 해결할 수 없으며, FILTER 함수(Excel 365/2021 이상)나 배열 수식(INDEX, SMALL, IF, ROW 조합) 또는 VBA 코딩 등 다른 복잡한 방법을 사용해야 합니다.

XLOOKUP: VLOOKUP의 한계를 뛰어넘는 차세대 검색 함수

XLOOKUP 함수는 VLOOKUP의 이러한 고질적인 문제점들을 해결하고, 더욱 직관적이고 강력한 기능을 제공합니다.

1. 유연한 조회 방향 (좌우, 상하 모두 가능)

XLOOKUP은 VLOOKUP과 달리 조회 방향에 제약이 없습니다. 찾을 값의 위치에 상관없이 왼쪽, 오른쪽, 위, 아래 어느 방향으로든 데이터를 조회할 수 있습니다. 이는 VLOOKUP의 가장 큰 단점이었던 ‘왼쪽 조회 불가’ 문제를 해결하며, INDEX+MATCH 조합을 대체할 수 있게 합니다.

예를 들어, 제품명을 기준으로 제품 코드를 가져오는 역조회(Left Lookup)가 XLOOKUP으로는 간단하게 가능합니다.

=XLOOKUP("마우스", 제품명_범위, 제품코드_범위)

2. 범위 참조를 통한 구조적 안정성

XLOOKUP은 반환할 열의 번호 대신, 반환할 값의 범위(return_array)를 직접 지정합니다. 이는 열을 삽입하거나 삭제해도 수식이 깨지지 않아 훨씬 안정적입니다.

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

여기서 lookup_array는 찾을 값이 있는 범위, return_array는 반환할 값이 있는 범위를 의미하며, 이 두 범위의 크기는 동일해야 합니다.

3. 정확히 일치가 기본값

XLOOKUP은 match_mode 인수의 기본값이 ‘정확히 일치(0)’입니다. 덕분에 대부분의 실무 상황에서 추가적인 인수 지정 없이 정확한 값을 찾을 수 있어 편리하며, VLOOKUP에서 발생하기 쉬웠던 근사값 오류를 줄여줍니다.

match_mode 인수는 다음과 같은 옵션을 제공합니다.

• 0 (기본값): 정확히 일치. 값이 없으면 #N/A 반환.
• -1: 정확히 일치하거나 다음으로 작은 값 검색.
• 1: 정확히 일치하거나 다음으로 큰 값 검색.
• 2: 와일드카드 문자 검색.

4. 내장된 오류 처리 기능

XLOOKUP은 if_not_found 인수를 제공하여, 찾으려는 값이 없을 때 #N/A 오류 대신 사용자 지정 메시지나 특정 값을 반환할 수 있습니다. 이로 인해 별도의 IFNA나 IFERROR 함수를 중첩할 필요가 없어 수식이 훨씬 간결해집니다.

=XLOOKUP("찾을값", 찾을_범위, 반환_범위, "찾는 데이터 없음")

5. 다양한 검색 모드 및 고급 기능

XLOOKUP은 search_mode 인수를 통해 다양한 검색 방식을 지원합니다.

• 1 (기본값): 첫 번째 항목부터 검색.
• -1: 마지막 항목부터 역방향 검색.
• 2: 오름차순으로 정렬된 범위에서 이진 검색 (매우 빠름).
• -2: 내림차순으로 정렬된 범위에서 이진 검색.

특히 ‘이진 검색’은 데이터 양이 많을수록 VLOOKUP보다 10배 이상 빠른 성능을 보여줄 수 있습니다. 또한, XLOOKUP은 결과값으로 배열을 반환하는 동적 배열 기능(Excel 365)을 지원하여, 하나의 수식으로 여러 결과를 동시에 가져오거나 다른 함수와 유연하게 결합할 수 있습니다. 이를 통해 부분 문자열 검색이나 다중 기준 검색 등 고급 활용이 가능합니다.

VLOOKUP 수식을 XLOOKUP으로 전환하는 실무 예시

이제 VLOOKUP 수식을 XLOOKUP으로 전환하는 구체적인 예시를 통해 그 차이점과 이점을 살펴보겠습니다.

기본 VLOOKUP 수식

다음과 같은 제품 데이터가 있다고 가정해 봅시다.

제품코드	제품명	단가	재고
A101	노트북	1,200,000	50
A102	마우스	25,000	200
A103	키보드	70,000	120
B201	모니터	350,000	80

제품코드 “A102″를 기준으로 제품명을 찾으려면 VLOOKUP은 다음과 같이 사용합니다.

=VLOOKUP("A102", A:D, 2, FALSE)

이 수식은 A열에서 “A102″를 찾아, 해당 행의 2번째 열(제품명) 값을 정확히 일치시켜 반환합니다.

XLOOKUP으로 전환

동일한 작업을 XLOOKUP으로 수행하면 다음과 같습니다.

=XLOOKUP("A102", A:A, B:B)

여기서 “A102″는 lookup_value, A:A는 lookup_array(제품코드를 찾을 범위), B:B는 return_array(제품명을 반환할 범위)입니다. VLOOKUP과 달리 열 번호 대신 범위를 직접 지정하여 훨씬 직관적입니다.

왼쪽 조회 예시 (VLOOKUP의 한계)

만약 제품명 “마우스”를 기준으로 제품코드를 찾아야 한다면 VLOOKUP으로는 직접 불가능합니다. (제품명이 첫 번째 열이 아니므로)

하지만 XLOOKUP으로는 간단하게 가능합니다.

=XLOOKUP("마우스", B:B, A:A)

제품명 B열에서 “마우스”를 찾아, 해당 행의 A열(제품코드) 값을 반환합니다.

오류 메시지 사용자 지정 예시

찾으려는 제품코드가 없을 때 “제품 없음”이라는 메시지를 표시하려면 XLOOKUP의 if_not_found 인수를 활용합니다.

=XLOOKUP("C300", A:A, B:B, "제품 없음")

만약 “C300” 제품코드가 없다면, #N/A 대신 “제품 없음”이라는 텍스트가 표시됩니다.

XLOOKUP 사용 시 발생할 수 있는 오류 및 해결 방법

XLOOKUP은 강력하지만, 잘못 사용하면 오류가 발생할 수 있습니다. 일반적인 오류와 해결 방법을 알아봅시다.

1. #NAME? 오류: XLOOKUP 함수를 찾을 수 없을 때

이 오류는 주로 XLOOKUP 함수를 지원하지 않는 엑셀 버전에서 파일을 열었을 때 발생합니다. XLOOKUP은 Microsoft 365 또는 Excel 2021 이상 버전에서만 사용할 수 있습니다.

• 해결 방법:
  • 사용 중인 엑셀 버전이 XLOOKUP을 지원하는지 확인합니다.
  • Microsoft 365 구독자인 경우, 엑셀이 최신 버전으로 업데이트되었는지 확인합니다.
  • 다른 사람과 파일을 공유할 때는 상대방의 엑셀 버전도 고려해야 합니다.

2. #VALUE! 오류: 반환 범위 크기 불일치

lookup_array(찾을 범위)와 return_array(반환할 범위)의 크기가 다를 때 발생합니다.

• 해결 방법:
  • lookup_array와 return_array의 행 또는 열 개수를 일치시켜야 합니다. 예를 들어, A:A와 B:B와 같이 전체 열을 지정하거나 A1:A100과 B1:B100과 같이 동일한 크기의 범위를 지정해야 합니다.

3. #N/A 오류: 찾는 값이 없을 때

기본적으로 XLOOKUP은 lookup_array에서 lookup_value를 찾지 못하면 #N/A 오류를 반환합니다.

• 해결 방법:
  • XLOOKUP의 네 번째 인수 [if_not_found]를 사용하여 사용자 지정 메시지를 지정합니다.
  • 데이터에 오타가 있는지, 데이터 형식이 일치하는지(숫자가 텍스트로 저장된 경우 등) 확인합니다.

4. 데이터 형식 불일치

숫자 값을 텍스트 값과 비교하거나 그 반대의 경우 오류가 발생할 수 있습니다.

• 해결 방법:
  • 찾는 값과 찾을 범위의 데이터 형식을 일치시킵니다. 텍스트로 저장된 숫자는 숫자로 변환하고, 그 반대의 경우도 마찬가지입니다.

전문가 팁: XLOOKUP 활용 극대화 및 예방

XLOOKUP을 더욱 효과적으로 사용하고 잠재적인 문제를 예방하기 위한 전문가 팁입니다.

• 데이터 정렬과 이진 검색 활용: 대규모 데이터에서 빠른 조회가 필요할 경우, lookup_array를 오름차순 또는 내림차순으로 정렬한 뒤 search_mode를 2 또는 -2로 설정하여 이진 검색을 활용하십시오. 이는 성능 향상에 크게 기여합니다.
• 와일드카드 검색: match_mode를 2로 설정하고 *(여러 문자)나 ?(단일 문자)와 같은 와일드카드를 사용하여 부분 문자열 검색을 수행할 수 있습니다. 예를 들어, 특정 단어가 포함된 제품을 찾을 때 유용합니다.
• 동적 배열과의 연계: Excel 365 사용자는 XLOOKUP이 동적 배열을 반환하는 기능을 활용하여 하나의 수식으로 여러 결과를 동시에 가져오거나 다른 동적 배열 함수와 결합하여 복잡한 데이터 분석을 간소화할 수 있습니다.
• 다중 기준 검색: 여러 조건을 동시에 만족하는 값을 찾고 싶을 때는 XLOOKUP과 다른 함수를 조합하거나, 보조 열을 활용하여 다중 기준을 만들 수 있습니다.

자주 묻는 질문

Q1: VLOOKUP과 XLOOKUP 중 어떤 함수를 사용하는 것이 더 좋나요?

특별한 이유가 없다면 XLOOKUP을 사용하는 것이 좋습니다. XLOOKUP은 VLOOKUP의 모든 기능을 포함하면서도 왼쪽 조회, 열 삽입/삭제에 대한 안정성, 내장된 오류 처리, 다양한 검색 모드 등 훨씬 강력하고 유연한 기능을 제공합니다. 다만, 파일을 공유하는 상대방이 엑셀 2019 이전 버전을 사용한다면 #NAME? 오류가 발생할 수 있으니 주의해야 합니다.

Q2: XLOOKUP이 VLOOKUP보다 항상 빠른가요?

일반적으로 XLOOKUP이 더 빠르고 효율적이지만, 특정 환경에서는 VLOOKUP이 더 빠를 수도 있습니다. 특히 정렬된 대규모 데이터에서 XLOOKUP의 이진 검색 모드를 사용하면 VLOOKUP보다 훨씬 빠른 성능을 보입니다. 하지만 데이터 정렬 여부, 데이터 세트 크기, 동적 배열 사용 여부 등 벤치마크 조건에 따라 결과는 달라질 수 있습니다.

Q3: XLOOKUP 함수는 엑셀 어떤 버전부터 사용할 수 있나요?

XLOOKUP 함수는 Microsoft 365 구독자 또는 Excel 2021 이상 버전 사용자부터 사용할 수 있습니다. 엑셀 2019 버전에서도 도입되었다는 정보도 있으나, 안정적인 사용을 위해서는 Microsoft 365 또는 엑셀 2021 이상을 권장합니다.

XLOOKUP은 엑셀 검색 함수의 패러다임을 바꾼 강력한 도구입니다. VLOOKUP의 한계를 극복하고 더 효율적이고 안정적인 데이터 조회를 가능하게 하므로, 아직 VLOOKUP에 머물러 있다면 지금 바로 XLOOKUP 전환을 시도해 보시길 강력히 권장합니다. 처음에는 익숙하지 않을 수 있지만, 몇 번의 연습만으로도 XLOOKUP의 진가를 경험하고 업무 효율성을 크게 향상시킬 수 있을 것입니다.