Calc VLOOKUP과 XLOOKUP: 적합한 조회 함수 선택하기

LibreOffice 24.8 이상 버전에서 통합 문서를 열 경우 대부분의 새 조회에는 XLOOKUP 함수를 사용하십시오. XLOOKUP은 기본적으로 정확히 일치하는 값을 찾고 검색 범위와 결과 범위를 별도로 지정할 수 있습니다. 이전 Calc 버전과의 호환성이 필요하거나 조회 값이 간략화된 표의 첫 번째 열에 있는 경우에는 VLOOKUP 함수를 사용하십시오. VLOOKUP 함수를 계속 사용하는 경우, 정확히 일치하는 값을 찾으려면 네 번째 인수를 0 또는 FALSE로 설정하십시오.

이 함수들은 모두 동일한 기본 문제를 해결합니다. 즉, 제품 코드와 같은 키를 찾고 다른 셀에서 관련 정보를 반환하는 것입니다. 중요한 차이점은 각 함수가 범위를 정의하는 방식, 옵션이 생략되었을 때 가정하는 값, 그리고 파일이 이전 버전의 소프트웨어에서도 작동해야 하는지 여부입니다.

실질적인 차이점은 무엇인가요?

VLOOKUP 함수는 직사각형 테이블의 첫 번째 열을 따라 검색하고 그 오른쪽의 번호가 매겨진 열에서 값을 반환합니다. XLOOKUP 함수는 검색 범위와 반환 범위를 분리하므로 반환 값은 키의 양쪽에 있을 수 있습니다. 또한 XLOOKUP 함수는 일치하지 않는 항목에 대한 사용자 지정 값을 허용하며 역순으로 검색할 수도 있습니다.

예를 들어, A2:A5 셀에 제품 ID가 있고 B2:B5 셀에 가격이 있다고 가정해 보겠습니다. F2 셀에는 찾을 ID가 있습니다. Calc에서 정확히 일치하는 항목을 찾는 VLOOKUP 함수는 다음과 같습니다.

=VLOOKUP(F2;A2:B5;2;0)

이에 해당하는 XLOOKUP 함수는 다음과 같습니다.

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

두 함수 모두 일치하는 가격을 반환합니다. Calc는 일반적으로 로케일 설정에 따라 함수 인수 사이에 세미콜론을 표시합니다. 설치된 프로그램의 수식에서 쉼표를 사용하는 경우 Calc에서 예상하는 구분 기호를 사용하십시오. 예제에서는 한 셀에 결과를 표시하고 1차원 조회 범위를 사용합니다.

XLOOKUP 함수는 언제 선택해야 할까요?

수신자가 LibreOffice 24.8 이상 버전을 사용하는 경우 새 통합 문서에 XLOOKUP 함수를 사용하는 것이 좋습니다. 공식 XLOOKUP 도움말 페이지 에 따르면 이 함수는 LibreOffice 24.8부터 사용할 수 있습니다 . 기본 일치 모드는 정확한 일치이므로 정확한 키가 없는 경우 인접한 값을 자동으로 반환하지 않습니다.

함수 패턴은 다음과 같습니다 =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). 처음 세 개의 인수는 키, 검색할 1차원 범위, 그리고 반환할 범위를 지정합니다. 네 번째 인수(예: )는 일치하는 항목 "Not found"이 없을 때 일반적인 #N/A 결과를 대체합니다.

왼쪽 열의 값을 반환합니다.

VLOOKUP 함수는 첫 번째 테이블 열의 왼쪽에 있는 값을 반환할 수 없습니다. 하지만 XLOOKUP 함수는 검색 범위와 결과 범위가 독립적이므로 가능합니다. 예를 들어, ID가 C2:C100 범위에 있고 부서 이름이 A2:A100 범위에 있는 경우 다음과 같이 사용합니다.

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

이러한 레이아웃은 불안정한 열 인덱스를 방지합니다. 누군가 VLOOKUP 테이블에 열을 삽입하면 하드 코딩된 인덱스가 다른 필드를 가리킬 수 있습니다. XLOOKUP은 결과 범위를 명시적으로 지정하므로 의도한 반환 열을 더 쉽게 확인할 수 있습니다.

가장 최근에 일치하는 항목을 반환합니다.

중복된 키가 있고 마지막으로 일치하는 항목을 찾으려면 XLOOKUP 함수는 검색 모드를 통해 역방향 검색을 지원합니다. 세로 범위의 경우 마지막 인수는 -1마지막에서 처음 순서로 검색합니다.

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

이 함수는 마지막으로 정확히 일치하는 결과를 반환합니다. 행 순서에 명확한 의미가 있는 경우(예: 최신 레코드가 마지막에 표시되는 경우)에만 사용하십시오. 중복을 오류로 처리해야 하는 경우에는 하나의 일치 항목을 암묵적으로 선택하는 대신 데이터를 정리하거나 유효성을 검사하십시오.

VLOOKUP 함수가 여전히 더 나은 선택인 경우는 언제일까요?

파일을 XLOOKUP 함수가 포함되지 않은 이전 버전의 LibreOffice에서 열어야 하거나 대상 응용 프로그램에서 XLOOKUP 함수를 인식하지 못할 수 있는 경우 VLOOKUP 함수를 유지하십시오. Calc 공식 설명서에는 VLOOKUP 함수의 테이블 및 인덱스 인수와 선택적 정렬 범위 인수에 대한 설명이 나와 있습니다. 자세한 내용은 공식 스프레드시트 함수 참조를 확인하십시오 .

정확히 일치하는 항목을 찾으려면 다음 패턴을 사용합니다 =VLOOKUP(key;table;column_index;0). 인덱스는 선택한 테이블의 왼쪽 끝에서부터 열을 세는데, 1은 첫 번째 열을, 2는 두 번째 열을 의미합니다. 조회 키는 첫 번째 열에 있어야 하며, 반환되는 필드는 해당 열의 오른쪽에 있어야 합니다.

네 번째 인수는 중요합니다. 이 인수를 생략하거나 TRUE로 설정하면 VLOOKUP 함수는 근사 일치를 사용하며 첫 번째 열이 오름차순으로 정렬되어 있을 것으로 예상합니다. 이러한 동작은 각 행이 하한선을 나타내는 세금 구간과 같은 범위에서 유용할 수 있습니다. 하지만 ID, 이름 및 정렬되지 않은 목록에는 적합하지 않습니다. 이러한 경우에는 0 또는 FALSE로 지정해야 합니다.

일치 동작과 오류 동작은 어떻게 다른가요?

정확한 일치: XLOOKUP 함수는 기본적으로 정확한 일치 검색을 사용합니다. VLOOKUP 함수는 정확한 검색을 보장하려면 네 번째 인수를 0 또는 FALSE로 설정해야 합니다.
누락된 키: XLOOKUP 함수는 네 번째 인수에 사용자가 지정한 메시지 또는 값을 반환할 수 있습니다. VLOOKUP 함수는 일반적으로 #N/A를 반환합니다. 사용자 지정 메시지가 필요한 경우 IFNA로 감싸십시오.
근사 일치: 두 방식 모두 범위 기반 일치를 지원하지만, 데이터와 선택된 모드가 의도된 규칙에 부합해야 합니다. 필요에 따라 데이터를 정렬하고 경계 사례를 확인하십시오.
검색 방향: XLOOKUP 함수는 search-mode 인수를 사용하여 아래에서부터 검색할 수 있습니다. VLOOKUP 함수는 일반적인 위에서 아래로 검색할 때 첫 번째 일치하는 행을 반환합니다.

VLOOKUP 함수에서 사용자 지정 누락 키 응답을 제공하려면 `--match`를 사용하세요 =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). 이렇게 하면 정확히 일치하는 설정은 그대로 유지되고 사용할 수 없는 오류만 처리됩니다. 범위 참조 오류와 같은 관련 없는 문제도 숨기려는 경우가 아니라면 수식을 포괄적인 오류 처리기로 감싸지 마세요.

워크북을 공유하기 전에 무엇을 확인해야 할까요?

LibreOffice 도움말에 따르면 XLOOKUP 함수는 버전 24.8부터 사용할 수 있습니다. 또한 XLOOKUP 함수는 OpenDocument 버전 1.3의 OpenFormula 표준에 포함되지 않으며, 상호 운용성을 위해 Microsoft 함수 네임스페이스를 사용한다고 명시되어 있습니다. 즉, 파일 형식만으로는 모든 이전 버전 또는 타사 스프레드시트 프로그램에서 해당 함수를 계산할 수 있다는 보장이 없습니다. 여러 소프트웨어 환경이 혼합된 경우, 가장 오래된 대상 프로그램에서 통합 문서를 테스트하거나, 형식이 적합한 경우 정확한 일치 VLOOKUP 함수와 같은 기존 함수를 사용하는 것이 좋습니다.

또한, XLOOKUP 함수의 결과 배열이 셀 범위인 경우, 함수를 배열 수식으로 입력해야 한다는 점을 유의하십시오. 위의 간단한 예제는 단일 대응값을 반환합니다. 수식이 여러 셀의 값을 반환하도록 하려면, 해당 버전에 대한 도움말을 참조하고 대상 애플리케이션이 배열 결과를 처리하는 방식을 확인하십시오.

결과를 어떻게 확인할 수 있나요?

존재하는 키를 테스트하고 반환된 값을 원본 행과 비교합니다.
존재하지 않는 키를 테스트합니다. XLOOKUP 함수가 선택한 대체 값을 표시하는지, 또는 VLOOKUP 함수가 예상되는 #N/A 또는 IFNA 메시지를 반환하는지 확인합니다.
VLOOKUP 함수를 사용하여 정확히 일치하는 값을 찾으려면 마지막 인수가 0 또는 FALSE인지 확인하십시오. 근사 일치를 의도적으로 사용하는 경우 정렬 순서와 경계 바로 위, 경계선, 경계선 바로 아래의 값을 확인하십시오.
왼쪽에 반환 열이 있는 XLOOKUP 함수 또는 역방향 검색의 경우, 알려진 결과가 있는 키를 테스트하고 중복이 있는 경우 어떤 항목을 반환해야 하는지 확인하십시오.
통합 문서를 저장한 다음 수신자가 사용하는 가장 오래된 Calc 또는 스프레드시트 프로그램에서 다시 여십시오. 컴퓨터에서 올바르게 작동한다고 해서 이전 버전에서도 동일한 기능이 지원되는 것은 아닙니다.

대부분의 새로운 Calc 시트에서는 XLOOKUP 함수가 더 명확한 범위와 안전한 정확한 일치 기본값을 제공합니다. 이전 버전과의 호환성이 중요하거나 첫 번째 열을 오른쪽으로 표시하는 레이아웃이 이미 적절한 경우에는 VLOOKUP 함수를 사용하고 일치 모드를 명시적으로 지정하십시오.