XLOOKUP: 엑셀 데이터 검색의 새로운 표준
엑셀을 사용하며 데이터를 검색해야 할 때, 가장 먼저 떠올리는 함수는 VLOOKUP일 것입니다. 하지만 VLOOKUP은 몇 가지 명확한 한계를 가지고 있습니다. 검색 대상 열이 반드시 참조 범위의 첫 번째 열이어야 하고, 왼쪽으로 검색이 불가능하며, 열을 삽입하거나 삭제하면 오류가 발생하기 쉽다는 점 등이 대표적입니다. 이러한 VLOOKUP의 단점을 완벽하게 보완하며 데이터 검색의 효율성을 극대화하는 함수가 바로 XLOOKUP입니다. XLOOKUP은 VLOOKUP보다 훨씬 유연하고 강력한 기능을 제공하며, 엑셀 사용자라면 반드시 숙지해야 할 필수 함수로 자리 잡고 있습니다.
XLOOKUP, 왜 VLOOKUP을 대체하는가?
XLOOKUP이 VLOOKUP을 대체하는 이유는 명확합니다. 첫째, 검색 방향의 제약이 없습니다. VLOOKUP과 달리, XLOOKUP은 검색 대상이 포함된 열이 반환하려는 값이 포함된 열의 왼쪽에 있더라도 문제없이 검색할 수 있습니다. 둘째, 고정된 참조 범위를 사용할 필요가 없습니다. XLOOKUP은 검색 배열과 반환 배열을 개별적으로 지정하므로, 열을 삽입하거나 삭제해도 함수가 자동으로 조정되어 오류 발생 가능성이 현저히 줄어듭니다. 셋째, 기본값(if_not_found) 기능을 제공합니다. 검색 결과가 없을 경우 특정 값을 반환하도록 설정할 수 있어, IFNA 함수를 별도로 사용할 필요가 없습니다. 넷째, 일치 모드(match_mode)와 검색 모드(search_mode)를 통해 더욱 정교한 검색이 가능합니다. 정확히 일치하는 값뿐만 아니라 근사값 검색, 역순 검색 등 다양한 옵션을 활용할 수 있습니다.
XLOOKUP 함수의 기본 구조와 필수 인수
XLOOKUP 함수의 기본 구문은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
각 인수에 대해 자세히 살펴보겠습니다.
- lookup_value (필수): 찾으려는 값입니다. 특정 셀 참조, 텍스트 문자열, 숫자 등이 될 수 있습니다.
- lookup_array (필수): 찾으려는 값이 들어 있는 배열 또는 범위입니다. 검색 대상 열입니다.
- return_array (필수): 찾으려는 값이 발견되었을 때 반환하려는 값이 들어 있는 배열 또는 범위입니다. 반환 대상 열입니다.
- [if_not_found] (선택): lookup_value를 찾지 못했을 때 반환할 값입니다. 이 인수를 생략하면 #N/A 오류가 반환됩니다.
- [match_mode] (선택): 일치 유형을 지정합니다.
- 0: 정확히 일치 (기본값)
- -1: 정확히 일치 또는 다음으로 작은 항목
- 1: 정확히 일치 또는 다음으로 큰 항목
- 2: 와일드카드 문자 일치
- [search_mode] (선택): 검색 방향을 지정합니다.
- 1: 처음부터 검색 (기본값)
- -1: 마지막부터 검색
- 2: 오름차순으로 정렬된 배열에서 이진 검색 (첫 번째 일치 항목 반환)
- -2: 내림차순으로 정렬된 배열에서 이진 검색 (첫 번째 일치 항목 반환)
XLOOKUP의 강력한 기능 활용 사례
1. 좌측 검색: VLOOKUP의 고정관념을 깨다
VLOOKUP의 가장 큰 제약 중 하나는 검색 대상 열이 항상 참조 범위의 첫 번째 열이어야 한다는 점이었습니다. 하지만 XLOOKUP은 이러한 제약을 완전히 해소합니다.
예시: ‘직원ID’ 열(B열)을 기준으로 ‘이름’ 열(A열)의 값을 찾고 싶을 때.
=XLOOKUP(B2, A1:A10, B1:B10)
이 경우, B2 셀의 직원ID를 A1:A10 범위에서 찾아, 일치하는 행의 B1:B10 범위 값을 반환합니다. VLOOKUP으로는 불가능했던 좌측 검색이 XLOOKUP으로는 매우 간단하게 구현됩니다.
2. 검색 결과 없음 시 기본값 설정
데이터베이스에서 찾으려는 값이 없을 경우, #N/A 오류 대신 특정 메시지나 값을 표시하고 싶을 때가 많습니다. XLOOKUP의 if_not_found 인수를 활용하면 이 기능을 쉽게 구현할 수 있습니다.
예시: ‘제품코드'(C열)를 기준으로 ‘가격'(D열)을 찾되, 해당 제품코드가 없으면 “미등록 제품”이라고 표시하고 싶을 때.
=XLOOKUP(C2, C1:C10, D1:D10, "미등록 제품")
이처럼 if_not_found 인수에 원하는 텍스트나 값을 직접 입력하면, 검색 결과가 없을 때 해당 값이 자동으로 표시됩니다.
3. 근사값 검색: 숫자 데이터 검색의 유연성
숫자 데이터의 경우, 정확히 일치하는 값이 아닌 가장 가까운 값(크거나 작은)을 찾고 싶을 때가 있습니다. XLOOKUP의 match_mode 인수를 사용하면 근사값 검색을 손쉽게 할 수 있습니다.
match_mode= -1 (정확히 일치 또는 다음으로 작은 항목): 예를 들어, 점수에 따른 등급을 찾을 때 유용합니다. 80점을 찾는데 80점이 없으면 80점 이하의 가장 큰 값을 반환합니다.match_mode= 1 (정확히 일치 또는 다음으로 큰 항목): 예를 들어, 대출 이자율을 계산할 때, 특정 금액 구간에 해당하는 이자율을 찾을 때 사용될 수 있습니다. 100만원을 찾는데 100만원이 없으면 100만원 이상의 가장 작은 값을 반환합니다.
예시: ‘판매량'(E열)을 기준으로 ‘할인율'(F열)을 찾되, 정확히 일치하는 판매량이 없으면 그보다 적은 판매량의 할인율을 적용하고 싶을 때.
=XLOOKUP(E2, E1:E10, F1:F10, , -1)
여기서 if_not_found 인수는 비워두고 -1만 지정하여 정확히 일치하거나 다음으로 작은 값을 찾도록 설정했습니다.
4. 역순 검색: 최신 데이터 우선 적용
데이터가 시간 순서대로 정렬되어 있고, 가장 최근의 데이터를 찾고 싶을 때 XLOOKUP은 search_mode 인수를 통해 이를 지원합니다. search_mode를 -1로 설정하면 배열의 마지막 항목부터 검색을 시작합니다.
예시: ‘주가 변동'(G열) 데이터가 시간 순으로 나열되어 있고, 가장 최근의 주가 변동 값을 찾고 싶을 때.
=XLOOKUP("변동", G1:G10, H1:H10, , , -1)
이 함수는 “변동”이라는 텍스트를 G열에서 거꾸로 검색하여, 가장 마지막에 나타나는 “변동”에 해당하는 H열의 값을 반환합니다.
5. 와일드카드 문자 활용
match_mode를 2로 설정하면 와일드카드 문자를 사용하여 검색할 수 있습니다. 와일드카드 문자는 다음과 같습니다.
*(별표): 임의의 문자 수 (0개 포함)?(물음표): 임의의 한 글자~(틸드): 물음표, 별표, 틸드 자체를 검색할 때 사용 (예:~?는 물음표를 검색)
예시: ‘제품명'(I열)에 “노트북”이라는 단어가 포함된 첫 번째 제품의 ‘가격'(J열)을 찾고 싶을 때.
=XLOOKUP("*노트북*", I1:I10, J1:J10, , 2)
"*노트북*"는 “노트북”이라는 텍스트가 앞뒤에 어떤 문자가 오든 포함된 경우를 모두 찾도록 지정합니다.
6. 동적 반환 범위: SUM, AVERAGE 등 배열 함수와 결합
XLOOKUP은 반환 배열을 지정할 때, 단순한 단일 열이나 행뿐만 아니라 여러 열 또는 행을 반환할 수 있습니다. 이를 통해 SUM, AVERAGE와 같은 배열 함수와 결합하여 더욱 강력한 분석이 가능해집니다.
예시: ‘영업팀'(K열)의 ‘매출'(L열) 총합을 구하고 싶을 때.
=SUM(XLOOKUP("영업팀", K1:K10, L1:L10))
이 함수는 XLOOKUP을 사용하여 “영업팀”에 해당하는 모든 매출액을 반환하고, SUM 함수가 이 값들을 모두 더합니다.
XLOOKUP과 다른 엑셀 함수의 조합
XLOOKUP은 단독으로도 강력하지만, 다른 엑셀 함수들과의 조합을 통해 그 활용도를 더욱 높일 수 있습니다.
XLOOKUP + IF
if_not_found 인수를 사용하면 되지만, 더 복잡한 조건에 따라 다른 값을 반환해야 할 경우 IF 함수와 결합할 수 있습니다.
=IF(XLOOKUP(A1, B:B, C:C)="오류", "확인 필요", XLOOKUP(A1, B:B, C:C))
XLOOKUP + CHOOSE
여러 개의 반환 값을 조건에 따라 선택해야 할 때 CHOOSE 함수와 함께 사용할 수 있습니다.
XLOOKUP + FILTER
FILTER 함수와 함께 사용하면 특정 조건을 만족하는 여러 행을 반환하고, XLOOKUP은 그 중에서 특정 값을 찾아오는 데 활용될 수 있습니다.
XLOOKUP 사용 시 주의사항 및 팁
- 데이터 정렬:
match_mode를 1 또는 -1로 설정하여 근사값 검색을 사용할 때는,lookup_array가 반드시 오름차순 또는 내림차순으로 정렬되어 있어야 정확한 결과를 얻을 수 있습니다. - 배열의 크기:
lookup_array와return_array의 크기는 동일해야 합니다. 크기가 다를 경우 #VALUE! 오류가 발생할 수 있습니다. - 대소문자 구분: XLOOKUP은 기본적으로 대소문자를 구분하지 않습니다. 대소문자를 구분해야 하는 경우에는 별도의 함수 조합이 필요합니다.
- 와일드카드 문자의 이스케이프: 검색하려는 값 자체에 와일드카드 문자(
*,?,~)가 포함된 경우,match_mode를 2로 설정하고 앞에~를 붙여 이스케이프 처리해야 합니다. (예:~*) - 성능: 매우 큰 데이터셋에서 XLOOKUP을 사용할 경우, 성능 저하가 발생할 수 있습니다. 이 경우, Power Query 등을 활용하는 것이 더 효율적일 수 있습니다.
결론: XLOOKUP으로 엑셀 데이터 검색의 효율성을 높이세요
XLOOKUP 함수는 VLOOKUP의 모든 단점을 보완하고, 좌측 검색, 동적 반환 범위, 기본값 설정, 다양한 검색 모드 등 훨씬 강력하고 유연한 기능을 제공합니다. 복잡한 데이터 검색 및 분석 작업에서 XLOOKUP을 적극적으로 활용한다면, 작업 시간 단축은 물론이고 오류 발생 가능성을 줄여 업무의 효율성과 정확성을 크게 향상시킬 수 있습니다. 지금 바로 XLOOKUP을 익히고 엑셀 데이터 검색의 새로운 표준을 경험해 보세요.
자주 묻는 질문 (FAQ)
Q1: XLOOKUP 함수는 모든 엑셀 버전에서 사용할 수 있나요?
A1: XLOOKUP 함수는 Microsoft 365 구독 버전, Excel 2021 및 Excel for the web에서 사용할 수 있습니다. 이전 버전의 엑셀에서는 사용할 수 없으므로, 호환성을 고려해야 합니다.
Q2: XLOOKUP에서 여러 조건을 만족하는 값을 찾고 싶습니다.
A2: XLOOKUP 자체만으로는 다중 조건 검색이 어렵습니다. 이 경우, FILTER 함수와 함께 사용하거나, 검색 기준이 되는 열들을 연결하여 하나의 열로 만든 후 XLOOKUP을 적용하는 방법을 고려할 수 있습니다. 예를 들어, =XLOOKUP(A1&B1, C:C&D:D, E:E) 와 같이 사용할 수 있습니다.
Q3: XLOOKUP으로 찾은 값이 텍스트인지 숫자인지 구분하는 방법이 있나요?
A3: XLOOKUP 함수 자체는 값의 데이터 유형을 구분하지 않고 반환합니다. 반환된 값이 텍스트인지 숫자인지 확인하려면 ISTEXT() 또는 ISNUMBER() 함수를 함께 사용할 수 있습니다. 예를 들어, =IF(ISTEXT(XLOOKUP(...)), "텍스트", "숫자") 와 같이 활용할 수 있습니다.
EXTERNAL_LINKS: Microsoft XLOOKUP 함수