LibreOffice Calc의 VLOOKUP과 XLOOKUP: 초보자를 위한 함수 가이드

LibreOffice Calc는 표에서 값을 찾고 관련 데이터를 반환하는 여러 가지 방법을 제공합니다. 오랫동안 VLOOKUP 함수가 가장 널리 사용되어 왔습니다. 이제 Calc에는 VLOOKUP의 여러 제약을 해결하고 유지 관리가 더 쉬운 새로운 조회 함수인 XLOOKUP 도 포함되어 있습니다 .

이 가이드는 기본적인 개념부터 시작하여 간단한 스프레드시트에 복사하여 사용할 수 있는 실용적인 수식을 단계별로 설명합니다. 또한 INDEX/MATCH 함수가 여전히 유용한 경우, 발생할 수 있는 오류, 그리고 새 통합 문서에 적합한 함수에 대해서도 설명합니다.

조회 함수를 사용하기 전에 무엇을 알아야 할까요?

조회 기능은 제품 ID와 같은 키를 사용하여 특정 범위를 검색한 다음 제품 가격과 같은 관련 값을 반환합니다. 검색 키는 텍스트, 숫자, 날짜 또는 셀 참조일 수 있습니다.

현재 LibreOffice 도움말에서는 XLOOKUP이 VLOOKUP, HLOOKUP 및 LOOKUP을 대체하는 최신 함수로 설명되어 있습니다. XLOOKUP은 LibreOffice 24.8 버전부터 사용할 수 있습니다. 현재 LibreOffice 26.8 도움말에서는 정확한 일치 및 근사 일치, 와일드카드 및 정규 표현식 일치, 역방향 검색 및 이진 검색에 대해 설명합니다. 자세한 내용은 LibreOffice 공식 XLOOKUP 문서를 참조하십시오 .

Microsoft Excel 사용자들이 종종 놀라는 점 중 하나는 LibreOffice의 수식 예제에서 인수 사이에 세미콜론(;)을 사용하는 경우가 많다는 것입니다. 예를 들어, =XLOOKUP(F2;A2:A6;D2:D6)Calc는 사용자의 지역 설정에 따라 구분 기호를 변경할 수 있으므로 설치된 프로그램에서 허용하는 구분 기호를 따르십시오.

VLOOKUP과 XLOOKUP 비교 (간략히)

간단한 연습용 테이블을 준비하세요

A열부터 D열까지 각각 '제품 ID' , '제품명' , '카테고리' , '가격' 이라는 제목의 열을 만드세요 . P001부터 P005까지와 같이 몇 개의 ID를 추가하세요. 검색할 ID를 F2 셀에 입력하고, 수식을 G2 셀에 입력하세요.

Calc에서 수식은 등호(=)로 시작합니다. 수식은 셀에 직접 입력하거나 수식 입력줄에 입력할 수 있습니다. LibreOffice 공식 수식 입력 안내서에서 두 가지 방법을 모두 설명하고 있습니다.

1단계: 정확한 일치 검색을 위해 VLOOKUP 함수를 사용합니다.

예를 들어 F2 셀에 P004가 있고 D 열의 가격을 알고 싶다면 다음과 같이 입력하세요.

=VLOOKUP(F2;$A$2:$D$6;4;0)

인수의 의미는 F2 셀의 값을 찾고, A2:D6 범위의 첫 번째 열을 검색하고, 해당 표의 네 번째 열 값을 반환하며, 정확히 일치하는 값을 찾아야 한다는 것입니다. 달러 기호($)는 절대 참조를 생성하여 수식을 다른 셀에 복사하더라도 조회 범위가 고정되도록 합니다.

LibreOffice 문서에서는 VLOOKUP 함수를 세로 검색으로 설명하며, 조회 값은 테이블 배열의 첫 번째 열에 있어야 하고 반환 값은 그 오른쪽 열에서 가져온다고 명시합니다. 또한 마지막 인수를 생략하거나 TRUE로 설정하면 함수가 정렬된 데이터를 가정하고 근사값을 반환할 수 있다고 경고합니다. VLOOKUP 함수의 근사적인 동작에 의존하기 전에 LibreOffice 스프레드시트 함수 참조 문서를 확인하십시오 .

2단계: 반환 열이 왼쪽에 있을 때 XLOOKUP 함수를 사용합니다.

이제 제품 이름이 A열에, 제품 ID가 C열에 오도록 표를 재배열하세요. VLOOKUP 함수를 사용하면 이 레이아웃은 불편합니다. 함수가 표 배열의 첫 번째 열만 검색하고 오른쪽 열의 값을 반환하기 때문입니다.

XLOOKUP 함수는 검색 배열 과 결과 배열을 분리합니다 . C열에서 P003을 찾고 A열에서 제품 이름을 반환하려면 다음과 같이 사용합니다.

=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")

네 번째 인수는 일치하는 항목이 없을 때 반환할 값입니다. 이렇게 하면 흔히 발생하는 이 경우에 대해 별도의 오류 처리 함수로 조회를 감싸는 것보다 수식을 더 읽기 쉽게 만들 수 있습니다.

3단계: #N/A를 노출하지 않고 누락된 ID를 처리합니다.

사용자가 존재하지 않는 ID를 입력하면 일반적인 정확한 조회는 일반적으로 오류를 반환합니다 #N/A. XLOOKUP 함수에서는 네 번째 인수에 보기 편한 결과를 넣어주세요.

=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)

일치 0모드는 정확히 일치하는 항목을 명시적으로 요구하지만, XLOOKUP 함수의 기본값은 이미 정확히 일치하는 항목입니다. 그럼에도 불구하고, 수식의 의도를 시트를 검토하는 사람이 쉽게 이해할 수 있도록 하려면 일치 모드를 사용하는 것이 유용할 수 있습니다.

모든 오류를 자동으로 숨기지 마세요. 특정 ID가 항상 존재해야 하는 경우, 해당 ID #N/A는 잘못되었거나 불완전한 원본 데이터를 드러내므로 오히려 유용할 수 있습니다. 누락된 키가 예상되는 경우에는 대체 방법을 사용하고, 데이터 품질 문제를 나타내는 경우에는 사용하지 마세요.

4단계: 가장 최근에 일치하는 항목을 찾으려면 아래쪽부터 검색합니다.

키가 두 번 이상 나타나는 경우가 있습니다. 예를 들어, 거래 기록에는 고객 C001에 대한 여러 행이 포함될 수 있습니다. XLOOKUP 함수에는 검색 모드 인수가 있습니다. 이 인수를 지정하지 않으면 -1마지막 항목에서 첫 번째 항목 방향으로 검색합니다.

F2 셀에 고객 ID가 포함된 마지막 행의 금액을 반환하려면 다음을 사용하십시오.

=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)

XLOOKUP 공식 참조 문서에서는 검색 모드를 1'처음부터 마지막까지'와 ' -1마지막부터 처음까지'로 정의합니다. 또한 이진 검색 모드도 제공 2하지만 -2, 이러한 모드는 데이터가 올바르게 정렬되어 있어야 합니다. 정렬되지 않은 데이터에 이진 검색을 사용하면 잘못된 결과가 반환될 수 있습니다.

INDEX와 MATCH는 언제 여전히 사용해야 할까요?

Calc 24.8에 XLOOKUP 함수가 도입되기 전에는 INDEX 함수와 MATCH 함수를 조합하는 것이 일반적인 대안이었습니다 . MATCH 함수는 키의 상대적인 위치를 찾고, INDEX 함수는 해당 위치의 값을 반환합니다. 예를 들면 다음과 같습니다.

=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))

이 기능은 이전 LibreOffice 설치 버전과의 호환성이 필요하거나 기존 통합 문서에서 이미 INDEX/MATCH 함수를 일관되게 사용하고 있는 경우에 유용합니다. 공식 스프레드시트 함수 참조 문서에는 INDEX와 MATCH 함수에 대한 설명이 모두 나와 있습니다 .

LibreOffice 24.8 버전부터 Calc는 XMATCH 기능을 제공합니다. XMATCH는 XLOOKUP과 유사한 일치 및 검색 모드를 제공하며, 역방향 검색도 지원합니다. 자세한 내용은 LibreOffice 공식 XMATCH 문서를 참조하십시오 .

흔히 저지르는 실수들을 피하는 방법

정확히 일치하는 결과를 얻으려면 VLOOKUP 함수의 마지막 인수를 비워 두세요.

VLOOKUP 함수에서 마지막 인수를 생략하면 정렬된 범위로 동작합니다. 제품 ID, 직원 번호, SKU 및 기타 개별 식별자의 경우, 0근사 일치가 의도적인 설계 요소가 아닌 한 정확한 일치를 위해서는 FALSE를 사용하거나 사용하지 마십시오.

잘못된 VLOOKUP 열 인덱스를 계산함

VLOOKUP 함수의 열 인덱스는 스프레드시트의 열 문자가 아닌 테이블 배열의 첫 번째 열부터 계산됩니다. 예를 들어 A2:D6 범위에서 D열은 인덱스 4입니다. 나중에 해당 테이블에 열을 추가하면 기존의 인덱스가 의도한 필드를 더 이상 참조하지 못할 수 있습니다.

XLOOKUP에서 길이가 다른 범위를 사용하는 방법

검색 범위와 결과 범위는 행 단위 또는 열 단위로 정렬되어야 합니다. A2:A100과 D2:D90과 같은 검색 범위는 대응하는 위치가 동일한 레코드를 포함하지 않으므로 설계 오류입니다.

정렬되지 않은 데이터에 이진 탐색을 적용하기

XLOOKUP 검색 모드 2는 오름차순 정렬을, -2는 내림차순 정렬을 요구합니다. LibreOffice 설명서에서는 정렬되지 않은 데이터는 잘못된 결과를 초래할 수 있다고 명시적으로 경고합니다. 정렬을 제어할 수 없는 경우 일반적인 순방향 또는 역방향 검색 모드를 사용하십시오.

와일드카드와 정규 표현식을 혼동하는 경우

Calc는 와일드카드 및 정규 표현식 일치를 지원하지만, XLOOKUP 함수에서는 두 가지 일치 모드가 별도로 사용됩니다. 와일드카드 모드는 *쉼표(, ?)와 같은 문자를 사용하고, 정규 표현식 모드는 Calc의 정규 표현식 엔진을 사용합니다. 패턴이 자동으로 해석될 것이라고 가정하지 말고, 일치 모드를 신중하게 선택하십시오.

초보자는 어떤 조회 함수를 선택해야 할까요?

LibreOffice 24.8 이상 버전에서 새 스프레드시트를 만들 때는 XLOOKUP 함수부터 사용하는 것이 좋습니다. 검색 범위와 결과 범위가 분리되어 가독성이 뛰어나고, 정확히 일치하는 결과가 기본값이며, 왼쪽 반환 방식이 간편하고, 찾을 수 없음 및 역방향 검색 옵션이 내장되어 있어 수식을 이용한 복잡한 수정 작업이 필요 없습니다.

기존 통합 문서를 관리할 때, XLOOKUP 지원 여부가 불확실한 환경과 공유할 때, 또는 제한 사항이 중요하지 않은 간단하고 안정적인 표를 사용할 때는 VLOOKUP 함수를 사용하십시오. 이전 LibreOffice 버전과의 호환성이 중요하거나 통합 문서가 이미 INDEX/MATCH 함수를 사용하는 경우에는 INDEX/MATCH 함수를 사용하십시오.

가장 중요한 습관은 모든 인수를 암기하는 것이 아닙니다. 대신, 수식을 작성하기 전에 다음 네 가지를 명확히 하세요. 어떤 값을 찾을 것인지, 어디에서 찾을 것인지, 어떤 범위에서 값을 가져올 것인지, 그리고 원하는 값이 없을 경우 어떻게 처리할 것인지입니다 . 이 네 가지가 명확해지면 VLOOKUP, XLOOKUP, INDEX/MATCH 함수 중에서 어떤 것을 선택할지 훨씬 쉬워집니다.