VLOOKUP 대체 - 엑셀 XLOOKUP 함수 완벽 가이드

 

 

📚 XLOOKUP 함수란?

엑셀에서 XLOOKUP은 Excel 2019부터 도입된 고급 검색 함수로, 기존의 VLOOKUP, HLOOKUP, LOOKUP 함수의 한계를 극복하고 더 강력한 기능을 제공합니다. 세로 또는 가로 방향으로 배열을 검색하여 일치하는 항목을 찾고, 해당 위치에서 원하는 결과를 반환합니다.

 

🔍 함수 구문

 

XLOOKUP(검색_값, 검색_배열, 반환_배열, [찾을_수_없음], [일치_모드], [검색_모드])

 

 

 

📋 함수 인수 설명

인수	필수/선택	설명
검색_값	필수	검색하려는 값
검색_배열	필수	검색할 배열 또는 범위
반환_배열	필수	결과를 반환할 배열 또는 범위
찾을_수_없음	선택	일치하는 항목이 없을 때 반환할 값 (생략 시 #N/A 오류 반환)
일치_모드	선택	0: 정확히 일치, -1: 정확히 일치하거나 그 다음으로 작은 값, 1: 정확히 일치하거나 그 다음으로 큰 값, 2: 와일드카드 사용 가능 (기본값: 0)
검색_모드	선택	1: 처음부터 검색, -1: 끝에서부터 검색, 2: 이진 검색(오름차순), -2: 이진 검색(내림차순) (기본값: 1)

📝 NOTE:

- XLOOKUP은 행과 열 모두에서 검색이 가능합니다 (VLOOKUP은 열만 가능).

- 검색배열과 반환배열은 반드시 같은 크기일 필요가 없습니다.

- XLOOKUP은 왼쪽에 있는 데이터를 검색할 수 있어 VLOOKUP의 INDEX+MATCH 조합을 대체할 수 있습니다.

- Excel 2019 이상 또는 Microsoft 365 구독자만 사용 가능합니다.

 

💻 함수 사용 예제

예제 1: 기본 검색

엑셀 XLOOKUP 함수를 사용하는 가장 기본적인 방법입니다.
직책 데이터에서 직책으로 직원의 수당을 찾는 예제입니다.

 

=XLOOKUP(B4, $C$13:$C$15, $D$13:$D$15, "직책을 찾을수 없음")

 

 

예제1. xlookup 직책별 수당 구하기

 

수식 작동 방식 설명:

1. 검색_값(B4): 조회할 직책 셀인 B4에 '과장'이 입력되어 있습니다.
2. 검색_배열(C13:C15): 직책이 나열된 C13부터 C15까지의 범위에서 검색합니다.
3. 반환_배열(D13:D15): 검색된 위치에 대응하는 D13:D15 범위(수당)의 값을 반환합니다.
4. 찾을_수_없음: "직책을 찾을수 없음" - 일치하는 사원번호가 없을 경우 이 텍스트를 반환합니다.

교번 으로 직책 정보를 가져오려면 아래와 같이 반환 배열만 변경하면 됩니다.

=XLOOKUP(A4,$A$4:$A$11,$B$4:$B$11)

 

 

 

참고로, VLOOKUP 함수를 사용한다면 G3 셀의 수식은 다음과 같을 것입니다.

=VLOOKUP( B4, $C$13:$D$15, 2, 0 )

 

 

📝 NOTE:

여기서 XLOOKUP 의 기본적인 '일치 모드' 는 '정확하게 일치' 입니다. VLOOKUP 함수는 4번째 인수로 0 을 설정해야 합니다. 이점이 아주 편리합니다.

예제 2: 여러 열 동시에 반환하기

엑셀 XLOOKUP 함수로 여러 열의 데이터를 한 번에 반환하는 방법입니다.

 

=XLOOKUP(A4, $A$4:$A$11, $A$4:$C$11, "찾는 사원 없음")

 

 

예제2. 여러 행이나 열 반환

 

수식 작동 방식 설명:

1. 수식을 작성할 F4:H11 셀 범위를 선택합니다. 이 범위는 결과가 반환될 위치입니다.
2. 검색_값(A4): 교번 셀인 A4로 검색을 시작합니다. 상대 참조 되어 있어 행이 바뀌면 검색값도 바뀝니다.
3. 검색_배열(A4:A11): 교번이 있는 A4부터 A11까지의 범위에서 검색합니다. '검색_값' 과 '검색_배열' 이 중첩되어도 상관없습니다
4. 반환_배열(A4:C11): 이전 예제와 달리, 여기서는 A열부터 C열까지 여러 열을 반환 배열로 지정했습니다.
5. 찾을_수_없음: "찾는 사원 없음" - 일치하는 교번이 없을 경우 이 텍스트를 반환합니다.

이 수식의 특별한 점은 반환 배열 수식이고, 여러 열을 반환할 수 있다는 것입니다. 배열 수식 이기게 이 결과를 얻으려면 Excel에서 배열 공식으로 입력해야 합니다.

📝 NOTE:
수식을 입력한 후 마지막에 Ctrl+Shift+Enter를 누릅니다. (Excel 365에서는 자동으로 처리됨). 이렇게 하면 하나의 공식으로 여러 셀에 결과가 표시됩니다.

 

이 기능은 하나의 키 값으로 여러 관련 데이터를 한 번에 가져와야 할 때 매우 유용합니다.

 

예제 3: 근사치 검색 (Approximate Match)

기본급으로 교번을 찾아 봅시다.

 

=XLOOKUP(1700000, C4:C11, A4:A11, ,-1)

 

 

예제3. xlookup 근사치 검색

 

수식 작동 방식 설명:

1. 검색_값: 조회할 검색_값으로 1700000 (170만원) 이 입력되어 있습니다.
2. 검색_배열(C2:C11): 기본급이 있는 C4부터 C11까지의 범위에서 검색합니다.
3. 반환_배열(A4:A11): 검색된 위치에 대응하는 A4:A11범위(교번)의 값을 반환합니다.
4. 찾을_수_없음: 이번 수식에는 이 인수가 입력되지 않았습니다. 입력이 싫은 옵션 인수는 이렇게 비워둡니다.
5. 일치_모드(-1): 이 매개변수가 이 예제 3 의 핵심입니다.
   • -1은 '정확히 일치하거나 그 다음으로 작은 값'을 의미합니다.
   • 즉, 정확히 170만원이 없다면, 1700000 보다 작은 값 중 가장 큰 값 1600000 을 찾습니다.

근사치 검색 과정:

1. 검색_값에 170만원이 입력되어 있습니다.
2. 기본급에서 170만원 을 검색하지만 정확히 일치하는 값이 없습니다.
3. 일치_모드가 -1이므로, 170보다 작은 값 중 가장 큰 값인 160 만원을 찾습니다.
4. 160 만원에 해당하는 교번인 10315 를 반환합니다.

📝 주의! 여기서 주의할 점은 일치_모드(-1)의 정확한 작동 방식입니다. 이 설정은 검색 값보다 작거나 같은 가장 큰 값을 찾습니다. 즉, 검색 배열이 오름차순으로 정렬되어 있어야 올바르게 작동합니다. 검색 배열이 정렬되어 있지 않으면 예상치 못한 결과가 나올 수 있습니다.

 

이 기능은 가격대별 할인율, 성적에 따른 등급 부여, 세금 구간 계산 등 구간별로 다른 값을 적용해야 하는 경우에 매우 유용합니다.

 

⚠️ XLOOKUP 함수 사용 시 발생하는 오류

오류	원인	해결 방법
#N/A	검색 값이 검색 배열에 없을 때	[찾을_수_없음] 인수에 기본값 지정
#VALUE!	인수 유형이 잘못되었을 때	인수 형식 확인 및 수정
#REF!	참조한 셀이 유효하지 않을 때	올바른 범위 참조 확인
#NAME?	함수 이름을 인식할 수 없을 때	Excel 버전 확인 (Excel 2019 이상 필요)
#CALC!	계산 오류가 발생했을 때	공식 확인 및 단순화

 

🌟 XLOOKUP의 장점

1. 양방향 검색 가능: 왼쪽에서 오른쪽, 오른쪽에서 왼쪽 모두 가능
2. 여러 값 반환: 여러 열이나 행의 데이터를 한 번에 반환 가능
3. 다양한 검색 옵션: 정확한 일치, 근사치, 와일드카드 등 다양한 검색 방식 지원
4. 오류 처리 간소화: [찾을_수_없음] 인수로 쉽게 오류 대체 가능
5. 배열 수식 지원: 단일 XLOOKUP으로 복잡한 작업 수행 가능

 

📝 마무리

XLOOKUP 함수는 Excel에서 데이터를 검색하고 추출하는 가장 강력한 도구 중 하나입니다.

기존의 VLOOKUP과 HLOOKUP의 제한사항을 극복하고, 더 직관적이고 다양한 기능을 제공합니다. 이 함수를 마스터하면 복잡한 데이터 분석 작업을 훨씬 효율적으로 수행할 수 있습니다. 함수의 다양한 옵션을 실험해보고 여러분의 Excel 업무에 활용해보세요.

항상 기억하세요 - Excel을 배우는 가장 좋은 방법은 직접 실습해보는 것입니다!

 

---

작성자: 마늘빵