XLOOKUP 함수 완벽 정복: VLOOKUP의 한계를 넘는 다중 조건과 역방향 검색

1. VLOOKUP을 버리고 XLOOKUP을 써야 하는 이유

엑셀 실무자들에게 가장 친숙한 함수 중 하나는 단연 VLOOKUP입니다. 하지만 수만 행의 고객 데이터나 재고 관리 장부를 다루다 보면, VLOOKUP 특유의 구조적 결함 때문에 골머리를 앓는 경우가 빈번합니다:

1. 좌측 열 검색 불가: 기준 열(Key)의 오른쪽에 있는 데이터만 조회할 수 있으며, 기준 열 왼쪽에 있는 값은 별도의 열 복사 없이는 가져올 수 없습니다.
2. 열 삽입 시 참조 파괴: 세 번째 인수로 열 번호(Index)를 직접 숫자로 입력하기 때문에, 원본 표 중간에 새 열을 하나라도 삽입하면 수식이 엉뚱한 데이터를 반환합니다.
3. #N/A 에러 처리의 번거로움: 값이 없을 때 깔끔하게 공백이나 대체 문구를 표시하려면 IFERROR나 IFNA 함수로 전체 수식을 감싸야 했습니다.

Microsoft 365 및 최신 엑셀 환경에서 기본 지원되는 XLOOKUP은 이러한 고질적 문제를 단 하나의 함수로 완벽하게 해결합니다.

---

2. XLOOKUP의 기본 구문과 인수 분석

XLOOKUP은 필수 인수 3개와 선택 인수 3개로 구성되어 있어, 기본 사용법은 매우 직관적입니다.

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

인수 이름	필수 여부	기능 및 실무 활용 팁
lookup_value	필수	찾고자 하는 기준 값 (예: 직원 ID, 제품 코드, 거래처명)
lookup_array	필수	기준 값을 검색할 단일 열 또는 행 범위
return_array	필수	결과로 반환받고 싶은 실제 데이터 열 또는 행 범위
if_not_found	선택	값이 일치하지 않을 때 반환할 대체 텍스트 (예: "조회 불가", "")
match_mode	선택	0: 정확히 일치(기본값), -1: 일치 항목 또는 다음으로 작은 항목, 1: 다음으로 큰 항목, 2: 와일드카드 문자 일치
search_mode	선택	1: 처음부터 끝까지 검색(기본값), -1: 끝에서부터 처음으로 검색(최신 데이터 추출 시 유용)

[!TIP] 기존 VLOOKUP에서는 정확한 일치를 위해 마지막 인수에 반드시 FALSE 또는 0을 넣어야 했지만, **XLOOKUP은 기본값이 ‘정확히 일치(0)’**이므로 번거로운 마지막 옵션을 생략해도 안전합니다.

---

3. 실무 예제: 좌측 검색(Reverse Lookup) 단번에 끝내기

아래와 같은 사원 명부 표가 있다고 가정해 봅시다:

• A열: 부서명
• B열: 사원명
• C열: 사번 (기준 키)
• D열: 입사일자

사번(C열)을 입력받아 해당 직원의 **부서명(A열)**을 조회해야 하는 경우, 기존 VLOOKUP으로는 검색이 불가능했습니다. 하지만 XLOOKUP을 사용하면 다음과 같이 단 한 줄로 해결됩니다.

=XLOOKUP(F2, C2:C100, A2:A100, "사번 불일치")

• F2: 검색할 사번 셀
• C2:C100: 사번이 위치한 범위
• A2:A100: 반환하고자 하는 부서명 범위
• "사번 불일치": 등록되지 않은 사번을 입력했을 때 에러 대신 노출할 문구

이제 원본 표의 B열과 C열 사이에 새로운 열(예: 직급)을 삽입하더라도, XLOOKUP은 범위 참조 방식을 취하므로 수식이 깨지지 않고 안전하게 유지됩니다.

---

4. 다중 조건(AND 검색) XLOOKUP 구현 테크닉

실무에서는 단일 기준이 아니라 **“부서가 ’영업팀’이면서 직급이 ’과장’인 사람의 기본급”**처럼 두 개 이상의 조건을 동시에 만족하는 데이터를 찾아야 할 때가 많습니다.

과거에는 보조열을 만들어 텍스트를 결합(A2&B2)하거나 복잡한 배열 수식을 작성해야 했지만, XLOOKUP에서는 논리 연산자 *(AND)를 활용해 깔끔하게 처리할 수 있습니다.

=XLOOKUP(1, (A2:A100="영업팀") * (B2:B100="과장"), D2:D100, "해당자 없음")

작동 원리 분석

1. (A2:A100="영업팀"): 부서가 영업팀이면 TRUE(1), 아니면 FALSE(0)의 배열을 생성합니다.
2. (B2:B100="과장"): 직급이 과장이면 TRUE(1), 아니면 FALSE(0)의 배열을 생성합니다.
3. 두 배열을 곱하면(*), 둘 다 일치하는 행만 1 * 1 = 1이 되고 나머지는 모두 0이 됩니다.
4. XLOOKUP의 첫 번째 인수로 1을 찾게 지정하면, 두 조건을 완벽히 만족하는 첫 번째 행의 D2:D100 값을 가져옵니다.

---

5. 최신 데이터 역순 검색 (Search Last to First)

주문 내역이나 고객 상담 로그처럼 날짜순으로 아래로 계속 누적되는 데이터베이스에서 **“특정 고객의 가장 마지막(최신) 구매 일자”**를 조회해야 하는 상황이 많습니다.

XLOOKUP의 6번째 인수 search_mode에 -1을 지정하면 표의 맨 아래(끝)부터 위로 역방향 검색을 시작하므로 가장 최근 로그를 즉시 찾아낼 수 있습니다.

=XLOOKUP("김철수", B2:B1000, C2:C1000, "구매 이력 없음", 0, -1)

이 수식은 B열에서 “김철수”를 맨 아래 행부터 위로 거슬러 올라가며 찾아, 가장 최근에 기록된 C열의 일자 데이터를 즉시 반환합니다.

---

6. 결론 및 실무 적용 권장 가이드

비교 항목	기존 VLOOKUP	최신 XLOOKUP	실무상 이점
검색 방향	오른쪽 방향만 가능	좌우/상하 전방향 가능	데이터 원본 구조를 재배열할 필요 없음
열 삽입 영향	열 번호 고정으로 수식 오류 발생	셀 범위 참조로 열 삽입 시에도 자동 보정	서식 수정 시 유지보수 공수 대폭 절감
기본 일치 방식	근사치(True)가 기본	정확히 일치(0)가 기본	실수로 인한 데이터 오조회 원천 차단
에러 핸들링	별도 IFERROR 감싸기 필요	자체 인수 [if_not_found] 내장	수식 길이 간소화 및 가독성 향상

현재 팀 내에서 Microsoft 365나 Excel 2021 이상을 표준으로 사용하고 있다면, 신규 작성하는 모든 보고서와 분석 시트에서 VLOOKUP을 전면 배제하고 XLOOKUP을 표준 수식으로 채택하는 것을 강력히 권장합니다.

클락가든 실무 가이드 품질 원칙

본 저널의 모든 가이드는 Microsoft 365, Google Workspace, Notion 최신 빌드에서 직접 테스트를 거쳐 집필됩니다. 수식 오류나 버전 호환성 제보가 있으실 경우 연구팀 문의를 통해 의견을 남겨주시면 정정 정책에 따라 24시간 이내에 반영합니다.