엑셀 XLOOKUP 함수 사용법 완벽 정리, VLOOKUP 차이까지

한 번의 수식으로 왼쪽·오른쪽 검색, 정확히 일치 검색, 오류 처리까지 가능한 최신 엑셀 검색 함수입니다. XLOOKUP은 VLOOKUP과 HLOOKUP을 대부분 대체할 수 있으며, 실무에서 가장 많이 쓰이는 함수 중 하나입니다.

엑셀 XLOOKUP 함수 사용법 완벽 정리, VLOOKUP 차이까지

엑셀에서 거래처명으로 전화번호를 찾거나, 제품 코드로 가격을 조회하는 작업은 정말 자주 합니다.

예전에는 대부분 VLOOKUP 함수를 사용했지만, 열 순서가 바뀌면 수식을 다시 수정해야 하고 왼쪽 값을 찾지 못하는 불편함이 있었습니다.

이런 문제를 해결하기 위해 Microsoft가 새롭게 만든 함수가 바로 XLOOKUP입니다.

XLOOKUP은 원하는 값을 기준으로 범위 어디서든 데이터를 찾아 결과를 반환하는 최신 검색 함수입니다.

이번 글에서는 XLOOKUP 기본 문법부터 실무 예제, VLOOKUP과의 차이, 자주 발생하는 오류 해결 방법까지 차근차근 정리해보겠습니다.

XLOOKUP 함수가 왜 중요한가

XLOOKUP은 단순히 새로운 함수가 아니라, 기존 검색 함수의 단점을 거의 모두 보완한 함수입니다.

특히 Microsoft 365와 Excel 2021 이후 버전에서는 기본 검색 함수처럼 사용되고 있습니다.

VLOOKUP의 대표적인 불편함

열 번호를 직접 입력해야 해서 표 구조가 바뀌면 오류가 발생하기 쉽습니다.

• 열 삽입 시 결과가 달라질 수 있음

• 왼쪽 방향 검색 불가능

• 정확히 일치 옵션을 자주 빼먹음

• 오류 발생 시 IFERROR를 따로 써야 함

 

VLOOKUP이 아직도 많이 쓰이는데 꼭 바꿔야 할까요?

기존 파일 유지에는 문제가 없지만, 새로 만드는 문서는 XLOOKUP이 훨씬 관리하기 쉽습니다.

XLOOKUP이 해결한 부분

검색 방향과 오류 처리까지 한 번에 해결됩니다.

• 왼쪽·오른쪽 모두 검색 가능

• 정확히 일치가 기본값

• 값을 못 찾으면 원하는 문구 표시 가능

• 수식이 더 읽기 쉬움

XLOOKUP 기본 문법 먼저 익히기

처음에는 인수가 많아 보이지만, 실제로 자주 사용하는 것은 앞의 3~4개입니다.

가장 기본 형태

찾을 값, 찾을 범위, 반환 범위만 기억하면 대부분의 검색이 가능합니다.

=XLOOKUP(찾을값, 찾을범위, 반환범위)

 

예를 들어 A열에 사번, B열에 이름이 있을 때 사번 1003의 이름을 찾으려면 다음과 같이 입력합니다.

=XLOOKUP(1003, A2:A10, B2:B10)

값을 못 찾았을 때 문구 표시

네 번째 인수에 안내 문구를 넣으면 #N/A 대신 친절한 메시지를 보여줄 수 있습니다.

=XLOOKUP(1003, A2:A10, B2:B10, "사번 없음")

 

이렇게 하면 값이 없을 때 “사번 없음”이라고 표시됩니다.

실무에서는 보고서를 보는 사람이 오류 코드보다 안내 문구를 훨씬 이해하기 쉽습니다.

실무에서 가장 많이 쓰는 예제

설명만 보면 감이 잘 안 오기 때문에, 실제 업무 형태로 살펴보겠습니다.

제품 코드로 단가 찾기

제품 코드만 입력하면 단가를 자동으로 불러오는 가장 대표적인 활용 예제입니다.

제품코드	단가
P100	5,000
P200	7,500
P300	12,000

E2 셀에 제품 코드를 입력했다면 단가는 다음 수식으로 조회합니다.

=XLOOKUP(E2, A2:A4, B2:B4, "코드 없음")

 

제품 코드만 바꾸면 단가가 자동으로 변경됩니다.

고객명으로 전화번호 검색

이름을 기준으로 연락처를 즉시 조회할 수 있어 고객 관리 파일에서 유용합니다.

=XLOOKUP(H2, A2:A100, C2:C100, "등록되지 않은 고객")

 

• H2: 찾을 고객명

• A열: 고객명 목록

• C열: 전화번호 목록

고객 관리 파일이나 상담 이력 관리 시 매우 유용합니다.

VLOOKUP과 XLOOKUP 차이 한눈에 비교

실무에서 어떤 함수를 써야 할지 고민될 때 아래 표만 기억해도 충분합니다.

항목	VLOOKUP	XLOOKUP
검색 방향	오른쪽만	양방향 가능
열 번호 입력	필요	불필요
정확히 일치 기본	아님	예
오류 처리	IFERROR 별도	함수 내 가능
수식 가독성	보통	좋음

특히 “열 번호 입력”이 없다는 점이 가장 큰 차이입니다.

표 구조가 바뀌어도 수식을 수정할 일이 크게 줄어듭니다.

왼쪽 값도 검색할 수 있다

이 기능 때문에 많은 사용자가 XLOOKUP으로 넘어왔습니다.

VLOOKUP으로는 안 되는 경우

기준 열보다 왼쪽에 있는 값을 찾아야 할 때 XLOOKUP이 가장 간단합니다.

이름	사번
김철수	1001
이영희	1002

사번 1002로 이름을 찾고 싶다면:

=XLOOKUP(1002, B2:B3, A2:A3)

 

찾을 범위(B열)보다 반환 범위(A열)가 왼쪽에 있어도 정상 동작합니다.

데이터 구조를 바꿀 필요가 없다

표 순서를 억지로 수정하지 않아도 되어 원본 데이터를 그대로 유지할 수 있습니다.

예전에는 INDEX + MATCH 조합을 사용했지만, 이제는 XLOOKUP 하나로 해결됩니다.

수식 길이도 짧아지고 다른 사람이 파일을 봐도 이해하기 쉬워집니다.

자주 발생하는 오류와 해결 방법

처음 사용할 때 가장 많이 만나는 문제들입니다.

#NAME? 오류가 뜨는 경우

사용 중인 엑셀 버전이 XLOOKUP을 지원하지 않을 가능성이 큽니다.

• Excel 2016 이하에서는 지원되지 않음

• Microsoft 365 또는 Excel 2021 이상 필요

• 회사 PC 버전 확인 필요

 

회사에서는 안 되고 집에서는 됩니다. 왜 그런가요?

회사 PC가 영구 라이선스 구버전일 가능성이 높습니다. 파일 자체 문제는 아닙니다.

#N/A 오류가 뜨는 경우

찾는 값과 실제 데이터 형식이 다른 경우가 많습니다.

• 숫자가 문자로 저장됨

• 앞뒤 공백 포함

• 코드 길이가 다름

 

이럴 때는 TRIM 함수로 공백을 제거하거나, 셀 서식을 동일하게 맞춰보세요.

=XLOOKUP(TRIM(E2), A2:A100, B2:B100)

여러 조건으로 검색하는 방법

실무에서는 “제품코드 + 날짜”, “부서 + 이름”처럼 조건이 두 개 이상인 경우가 많습니다.

XLOOKUP은 배열 계산을 이용하면 복잡한 함수 없이도 다중 조건 검색이 가능합니다.

부서와 이름이 모두 일치하는 값 찾기

조건 두 개를 동시에 만족하는 행에서 원하는 값을 반환할 수 있습니다.

예를 들어 A열은 부서, B열은 이름, C열은 내선번호라고 가정해보겠습니다.

=XLOOKUP(1, (A2:A100="영업팀")*(B2:B100="김철수"), C2:C100)

 

• 두 조건이 모두 참이면 1이 됩니다.

• XLOOKUP이 1을 찾아 해당 행의 내선번호를 반환합니다.

• 조건을 셀 참조로 바꾸면 더 실용적으로 사용할 수 있습니다.

셀 입력값으로 조건 관리하기

검색 조건을 셀에 입력하도록 만들면 보고서나 조회 화면처럼 사용할 수 있습니다.

=XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100, "검색 결과 없음")

 

F2에는 부서명, G2에는 이름을 입력하면 됩니다.

검색 조건을 바꿔도 수식은 그대로 유지되기 때문에 유지보수가 훨씬 편합니다.

마지막 값 검색도 가능하다

같은 상품이 여러 번 판매된 경우 “가장 최근 판매가”를 가져와야 할 때가 있습니다.

이럴 때 XLOOKUP의 검색 방향 옵션이 유용합니다.

아래에서 위로 검색하기

여섯 번째 인수를 -1로 지정하면 마지막으로 일치하는 값을 찾습니다.

=XLOOKUP(E2, A2:A100, B2:B100, "없음", 0, -1)

 

• E2: 찾을 제품코드

• A열: 제품코드 목록

• B열: 판매금액

• -1: 아래에서 위로 검색

같은 코드가 여러 번 있어도 가장 마지막 행의 값을 반환합니다.

언제 유용할까

최신 데이터가 아래로 계속 추가되는 업무 파일에서 특히 효과적입니다.

• 최근 거래 금액 조회

• 마지막 재고 수량 확인

• 최신 담당자 정보 조회

• 가장 최근 상태값 가져오기

 

정렬이 꼭 되어 있어야 하나요?

아닙니다. XLOOKUP은 지정한 방향으로 순차 검색하기 때문에 정렬되지 않은 데이터에서도 동작합니다.

XLOOKUP과 함께 쓰면 좋은 함수

XLOOKUP만 알아도 충분하지만, 몇 가지 함수를 함께 사용하면 훨씬 강력해집니다.

IFERROR 대신 기본 오류 처리 활용

XLOOKUP 자체에서 오류 메시지를 처리할 수 있어 수식이 더 간단합니다.

기존 방식:

=IFERROR(VLOOKUP(E2,A:B,2,FALSE), "없음")

 

XLOOKUP 방식:

=XLOOKUP(E2, A:A, B:B, "없음")

수식 길이가 짧고 읽기도 쉽습니다.

FILTER 함수와 조합하기

조건에 맞는 여러 행을 한 번에 추출할 수 있습니다.

=FILTER(A2:C100, A2:A100=E2, "데이터 없음")

 

XLOOKUP은 한 개의 결과를 반환하고, FILTER는 여러 개의 결과를 반환합니다.

두 함수를 함께 익혀두면 데이터 조회 업무 속도가 크게 빨라집니다.

버전별 지원 여부 확인하기

회사에서 만든 파일이 집에서는 열리는데, 반대로 회사 PC에서 오류가 나는 경우가 있습니다.

대부분 엑셀 버전 차이 때문입니다.

지원되는 엑셀 버전

Microsoft 365와 Excel 2021 이상에서 안정적으로 사용할 수 있습니다.

엑셀 버전	지원 여부
Excel 2016	지원 안 함
Excel 2019	일부 환경
Excel 2021	지원
Microsoft 365	완전 지원

내 버전 확인 방법

파일 메뉴의 계정 화면에서 제품 정보를 확인하면 됩니다.

파일 → 계정 메뉴 클릭

제품 정보 확인

Microsoft 365 또는 Excel 2021 이상인지 체크

버전이 낮다면 XLOOKUP 대신 INDEX + MATCH 조합을 사용해야 할 수 있습니다.

처음 배운다면 이 순서로 연습하세요

한꺼번에 모든 기능을 외우려고 하면 오히려 헷갈립니다.

아래 순서대로 10분씩만 연습해보세요.

1단계: 기본 검색

찾을 값, 찾을 범위, 반환 범위만으로 원하는 결과를 가져오는 연습입니다.

=XLOOKUP(E2, A2:A10, B2:B10)

제품명이나 사번처럼 단순한 값을 조회해보세요.

2단계: 오류 메시지 추가

검색 실패 시 안내 문구를 표시해 사용자 친화적인 결과를 만듭니다.

=XLOOKUP(E2, A2:A10, B2:B10, "데이터 없음")

보고서 품질이 좋아집니다.

3단계: 왼쪽 검색

기준 열의 왼쪽 값을 찾아오는 연습만 해도 XLOOKUP의 장점을 바로 체감할 수 있습니다.

 여기까지 익히면 실무의 대부분 검색 작업을 처리할 수 있습니다.

실무에서는 결국 XLOOKUP으로 정리됩니다

처음에는 VLOOKUP이 더 익숙해서 계속 사용하게 됩니다.

하지만 표에 열을 하나 추가하는 순간 수식이 틀어지는 경험을 몇 번 하고 나면, 자연스럽게 XLOOKUP으로 넘어오게 됩니다.

특히 거래처 관리, 재고 관리, 견적서, 매출 집계처럼 검색이 반복되는 문서에서는 차이가 큽니다.

수식 수정 시간이 줄고, 다른 사람이 파일을 이어받아도 구조를 이해하기 쉽기 때문입니다.

지금 새로 만드는 엑셀 파일이라면 처음부터 XLOOKUP을 사용하는 것을 추천합니다.

한 번 익혀두면 앞으로 몇 년 동안은 가장 자주 사용하는 엑셀 함수가 될 가능성이 높습니다.