엑셀 XLOOKUP 사용하는 방법: 기본 조회부터 오류 해결까지

XLOOKUP은 한 범위에서 기준값을 찾고, 같은 행이나 열에 있는 다른 범위의 값을 가져오는 엑셀 조회 함수입니다. 엑셀 XLOOKUP 사용하는 방법은 =XLOOKUP(찾을값, 검색범위, 반환범위) 구조만 이해하면 시작할 수 있습니다. 예를 들어 =XLOOKUP(B2,B5:B14,C5:C14)는 B2의 값을 B5:B14에서 찾고, 같은 위치의 C5:C14 값을 반환합니다.

엑셀 XLOOKUP 사용하는 방법: 핵심 요약

• 기본적으로 정확히 일치하는 값을 찾습니다.
• 검색 범위와 반환 범위를 따로 지정하므로 왼쪽 방향 조회도 가능합니다.
• VLOOKUP처럼 반환 열 번호를 세지 않아도 됩니다.
• 검색 결과가 없을 때 표시할 문구를 네 번째 인수로 지정할 수 있습니다.
• Excel 2016 및 Excel 2019에서는 XLOOKUP을 직접 사용할 수 없습니다.

기본 XLOOKUP 사용 방법

예를 들어 다음과 같은 데이터가 있다고 가정합니다.

E2 셀에 상품코드 A002가 입력되어 있고, 상품명을 가져오려면 다음 수식을 입력합니다.

=XLOOKUP(E2,A2:A4,B2:B4)

수식의 의미는 다음과 같습니다.

1. E2: 찾으려는 상품코드입니다.
2. A2:A4: 상품코드를 검색할 범위입니다.
3. B2:B4: 찾은 코드와 같은 위치에서 상품명을 가져올 범위입니다.

결과는 무선 마우스가 됩니다. 가격을 가져오려면 반환 범위만 C열로 바꿉니다.

=XLOOKUP(E2,A2:A4,C2:C4)

XLOOKUP의 기본 구문 이해하기

전체 구문은 다음과 같습니다.

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

대괄호로 표시된 인수는 선택 사항입니다. 따라서 가장 간단한 형태는 다음과 같습니다.

=XLOOKUP(B2,B5:B14,C5:C14)

기본 match_mode는 정확히 일치하는 값만 찾는 0입니다. 일치하는 값을 찾지 못하면 기본적으로 #N/A 오류가 표시됩니다.

검색 결과가 없을 때 문구 표시하기

검색 결과가 없을 때 #N/A 대신 안내 문구를 표시하려면 네 번째 인수를 사용합니다.

=XLOOKUP(E2,A2:A4,B2:B4,"상품 없음")

E2의 값이 A2:A4에 없으면 상품 없음이 표시됩니다. 실무에서는 다음처럼 오류 원인을 알려주는 문구를 넣을 수도 있습니다.

=XLOOKUP(E2,A2:A4,B2:B4,"상품코드를 확인하세요")

문구를 입력할 때는 반드시 큰따옴표로 감싸야 합니다. 숫자나 셀 참조를 반환하려는 경우에는 큰따옴표를 사용하지 않습니다.

왼쪽에 있는 값을 조회하기

VLOOKUP은 검색 기준 열이 반환할 열보다 왼쪽에 있어야 합니다. 반면 XLOOKUP은 검색 범위와 반환 범위를 별도로 지정하므로 반환 범위가 검색 범위의 왼쪽에 있어도 됩니다.

예를 들어 B열에서 상품명을 찾고 A열의 상품코드를 가져오려면 다음과 같이 작성합니다.

=XLOOKUP(E2,B2:B4,A2:A4,"상품 없음")

이때 B2:B4가 검색 범위이고 A2:A4가 반환 범위입니다. 반환 범위의 방향이나 위치 때문에 별도의 열 번호를 계산할 필요가 없습니다.

중복값이 있을 때 마지막 일치값 가져오기

같은 검색값이 여러 번 나타나면 기본적으로 검색 범위의 위쪽에서부터 처음 발견한 값을 반환합니다. 같은 값 중 마지막 항목을 가져오려면 여섯 번째 인수인 search_mode에 -1을 지정합니다.

=XLOOKUP(E2,A2:A10,C2:C10,"없음",0,-1)

여기서 0은 정확히 일치하는 검색이고, -1은 마지막 항목부터 역방향으로 검색한다는 뜻입니다. 같은 상품코드에 여러 기록이 있고 가장 최근에 입력된 행을 사용하려는 경우 유용합니다. 단, 데이터가 최신순인지 또는 원하는 순서로 정리되어 있는지 먼저 확인해야 합니다.

근사 일치와 와일드카드 사용하기

match_mode를 지정하면 정확히 같은 값이 없어도 조건에 맞는 값을 찾을 수 있습니다.

예를 들어 점수별 등급표처럼 기준값에 따라 구간을 찾을 때는 근사 일치를 사용할 수 있습니다.

=XLOOKUP(E2,A2:A10,B2:B10,"등급 없음",-1)

와일드카드 검색에서는 *가 여러 문자, ?가 한 문자를 의미합니다. 예를 들어 상품명에 무선이 포함된 항목을 찾으려면 다음과 같이 작성할 수 있습니다.

=XLOOKUP("*무선*",B2:B10,C2:C10,"검색 결과 없음",2)

와일드카드가 실제 문자로 들어 있는 데이터를 검색해야 한다면 물결표 ~를 사용해 와일드카드 의미를 해제해야 합니다.

다른 시트의 데이터 조회하기

검색 범위와 반환 범위가 다른 시트에 있다면 시트 이름과 범위를 함께 입력합니다.

=XLOOKUP(A2,상품목록!A:A,상품목록!C:C,"상품 없음")

시트 이름에 공백이 있으면 작은따옴표로 감쌉니다.

=XLOOKUP(A2,'상품 목록'!A:A,'상품 목록'!C:C,"상품 없음")

전체 열을 지정하는 방식은 간단하지만 데이터가 매우 많으면 계산량이 커질 수 있습니다. 가능하면 실제 데이터 범위나 엑셀 표의 열을 지정하는 편이 관리와 성능 측면에서 유리합니다.

XLOOKUP이 작동하지 않을 때

#N/A가 표시되는 경우

다음 원인을 확인합니다.

• 검색값과 검색 범위의 값이 실제로 다른 경우
• 숫자와 숫자처럼 보이는 텍스트가 섞인 경우
• 앞뒤에 불필요한 공백이 포함된 경우
• 오타나 숨은 문자가 있는 경우
• 정확히 일치하는 기본 설정에서 근사 일치를 기대한 경우

검색 실패 문구를 표시하려면 네 번째 인수를 추가합니다.

=XLOOKUP(A2,D2:D100,E2:E100,"일치하는 데이터 없음")

숫자와 텍스트 형식이 섞인 문제는 셀 서식만 바꾸는 것으로 해결되지 않을 수 있습니다. 실제 값의 형식을 통일하고, 필요하면 VALUE, TEXT, TRIM 같은 정리 함수를 사용해야 합니다.

#VALUE!가 표시되는 경우

검색 범위와 반환 범위의 크기나 방향이 맞지 않는지 확인합니다. 예를 들어 검색 범위가 A2:A10인데 반환 범위가 C2:C8처럼 서로 다른 행 수를 가지면 문제가 발생할 수 있습니다. 두 범위가 같은 데이터 행을 가리키는지 확인합니다.

#NAME?가 표시되는 경우

사용 중인 Excel 버전에서 XLOOKUP을 지원하지 않을 가능성이 있습니다. Microsoft 안내에 따르면 Excel 2016 및 Excel 2019에서는 XLOOKUP을 직접 사용할 수 없습니다. 이 경우 사용 중인 Excel 버전을 확인하고, 지원되는 환경에서 열거나 기존 조회 함수 등 다른 방법을 검토해야 합니다.

수식이 결과가 아니라 글자로 보이는 경우

셀 서식이 텍스트로 설정되어 있거나 수식 앞에 작은따옴표가 들어간 경우입니다.

1. 셀 서식을 일반으로 바꿉니다.
2. 수식을 다시 입력하거나 F2를 누른 뒤 Enter를 누릅니다.
3. 수식 앞에 작은따옴표가 있다면 삭제합니다.
4. 수식 표시 모드가 켜져 있는지 확인합니다.

사용할 때 주의할 점

• 검색값이 중복되면 기본적으로 처음 일치하는 값만 반환됩니다.
• 마지막 일치값이 필요하면 search_mode를 -1로 지정해야 합니다.
• 근사 일치를 사용할 때는 기준 데이터의 정렬 상태와 조건을 반드시 확인합니다.
• 검색 범위와 반환 범위는 서로 대응하는 같은 행이나 열을 가리켜야 합니다.
• 넓은 전체 열을 여러 수식에서 반복 참조하면 파일 계산이 느려질 수 있습니다.
• 빈 셀을 검색할 때는 실제 빈 셀인지, 빈 문자열을 반환하는 수식인지 구분해야 합니다.
• Excel 2016 및 Excel 2019 환경에서는 수식이 인식되지 않을 수 있으므로 파일을 공유하기 전에 사용자의 Excel 버전을 확인하는 것이 좋습니다.

작업 전에는 엑셀 XLOOKUP 사용하는 방법의 적용 범위와 원본 상태를 먼저 확인하는 것이 좋습니다.

자주 묻는 질문

XLOOKUP과 VLOOKUP 중 무엇을 사용해야 하나요?

XLOOKUP을 사용할 수 있는 환경이라면 검색 범위와 반환 범위를 분리해 지정할 수 있고, 기본적으로 정확히 일치하는 값을 찾기 때문에 관리가 편합니다. VLOOKUP은 검색 열이 반환 열보다 왼쪽에 있어야 하고 반환 열 번호를 지정해야 합니다. 다만 Excel 2016 및 Excel 2019처럼 XLOOKUP을 지원하지 않는 환경에서는 VLOOKUP 등 다른 함수를 고려해야 합니다.

XLOOKUP으로 여러 열의 결과를 한 번에 가져올 수 있나요?

반환 범위에 여러 열을 지정하면 결과가 인접한 셀로 펼쳐질 수 있습니다. 예를 들어 =XLOOKUP(E2,A2:A10,B2:D10)처럼 작성하면 일치하는 행의 B열부터 D열까지 가져오는 방식입니다. 결과가 들어갈 주변 셀에 다른 데이터가 있으면 펼쳐지지 않을 수 있습니다.

검색값이 없을 때 빈칸으로 표시하려면 어떻게 하나요?

네 번째 인수에 빈 문자열을 입력합니다.

=XLOOKUP(E2,A2:A10,C2:C10,"")

다만 결과가 실제 빈 셀인지, 검색 실패로 빈 문자열이 표시된 것인지는 구분되지 않을 수 있습니다.

XLOOKUP에서 대소문자를 구분할 수 있나요?

기본 XLOOKUP만으로는 대소문자를 구분하는 검색을 직접 설정하지 않습니다. 대소문자 구분이 꼭 필요하다면 EXACT와 다른 함수의 조합을 검토해야 합니다.

마지막으로 입력된 값을 찾으려면 어떻게 하나요?

정확히 일치하는 검색을 유지하면서 search_mode에 -1을 지정합니다.

=XLOOKUP(A2,D2:D100,E2:E100,"없음",0,-1)

단, 데이터가 실제로 원하는 시간 순서나 입력 순서로 정렬되어 있어야 합니다.

마무리

기본적인 XLOOKUP은 찾을 값, 검색 범위, 반환 범위 세 부분으로 시작합니다. 먼저 =XLOOKUP(B2,B5:B14,C5:C14) 형태로 정확히 일치하는 조회를 익힌 뒤, 필요할 때 네 번째 인수로 오류 문구를 추가하고 match_mode와 search_mode로 근사값이나 마지막 일치값을 처리하면 됩니다.