Microsoft Excel에서 XLOOKUP 함수를 사용하는 방법

Excel의 새로운 XLOOKUP은 VLOOKUP을 대체하여 Excel의 가장 인기 있는 기능 중 하나를 강력하게 대체합니다. 이 새로운 기능은 VLOOKUP의 일부 제한 사항을 해결하고 추가 기능을 제공합니다. 알아야 할 사항이 있습니다.

XLOOKUP이란 무엇입니까?

새로운 XLOOKUP 기능에는 VLOOKUP의 가장 큰 몇 가지 제한 사항에 대한 솔루션이 있습니다. 또한 HLOOKUP도 대체합니다. 예를 들어 XLOOKUP은 왼쪽을 볼 수 있으며 기본적으로 정확히 일치하며 열 번호 대신 셀 범위를 지정할 수 있습니다. VLOOKUP은 사용하기 쉽지 않거나 다양하지 않습니다. 어떻게 작동하는지 보여드리겠습니다.

현재 XLOOKUP은 Insiders 프로그램의 사용자만 사용할 수 있습니다. 누구나 Insiders 프로그램에 가입하여 최신 Excel 기능이 제공되는 즉시 액세스할 수 있습니다. Microsoft는 곧 모든 Office 365 사용자에게 이 기능을 배포하기 시작할 것입니다.

XLOOKUP 기능을 사용하는 방법

XLOOKUP이 실제로 작동하는 예를 살펴보겠습니다. 아래의 예시 데이터를 가져오세요. A열의 각 ID에 대해 F열의 부서를 반환하려고 합니다.

이것은 고전적인 완전 일치 조회의 예입니다. XLOOKUP 함수에는 세 가지 정보만 필요합니다.

아래 이미지는 6개의 인수가 있는 XLOOKUP을 보여주지만 정확히 일치하려면 처음 3개만 필요합니다. 그래서 그것들에 집중합시다:

• Lookup_value: 찾고 있는 것.
• Lookup_array: 볼 위치입니다.
• Return_array: 반환할 값이 포함된 범위입니다.

이 예에서는 다음 공식이 작동합니다. =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

이제 여기에서 XLOOKUP이 VLOOKUP에 비해 갖는 몇 가지 장점을 살펴보겠습니다.

더 이상 열 인덱스 번호가 없습니다.

VLOOKUP의 악명 높은 세 번째 인수는 테이블 배열에서 반환할 정보의 열 번호를 지정하는 것이었습니다. XLOOKUP을 사용하면 반환할 범위를 선택할 수 있으므로 더 이상 문제가 되지 않습니다(이 예에서는 F 열).

그리고 XLOOKUP은 VLOOKUP과 달리 선택한 셀의 왼쪽 데이터를 볼 수 있음을 잊지 마십시오. 이에 대한 자세한 내용은 아래에서 확인하세요.

또한 새 열을 삽입할 때 더 이상 수식이 깨지는 문제가 발생하지 않습니다. 스프레드시트에서 이러한 일이 발생하면 반환 범위가 자동으로 조정됩니다.

정확히 일치가 기본값입니다.

VLOOKUP을 배울 때 왜 정확히 일치해야 하는지를 지정해야 하는 이유는 항상 혼란스러웠습니다.

다행히도 XLOOKUP은 기본적으로 정확히 일치하도록 설정되어 있습니다. 이는 조회 수식을 사용하는 훨씬 더 일반적인 이유입니다. 이렇게 하면 다섯 번째 인수에 답할 필요가 줄어들고 공식을 처음 접하는 사용자의 실수가 줄어듭니다.

간단히 말해서 XLOOKUP은 VLOOKUP보다 질문을 적게 하고 사용자 친화적이며 내구성이 뛰어납니다.

XLOOKUP은 왼쪽을 볼 수 있습니다.

조회 범위를 선택할 수 있으므로 XLOOKUP은 VLOOKUP보다 더 다양합니다. XLOOKUP에서는 테이블 열의 순서가 중요하지 않습니다.

VLOOKUP은 테이블의 맨 왼쪽 열을 검색한 다음 지정된 수의 열에서 오른쪽으로 반환하여 제한되었습니다.

아래 예에서 ID(열 E)를 조회하고 그 사람의 이름(열 D)을 반환해야 합니다.

다음 공식으로 이를 달성할 수 있습니다. =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

찾을 수 없는 경우 수행할 작업

조회 기능 사용자는 VLOOKUP 또는 MATCH 기능이 필요한 것을 찾을 수 없을 때 표시되는 #N/A 오류 메시지에 매우 익숙합니다. 그리고 종종 여기에는 논리적인 이유가 있습니다.

따라서 사용자는 이 오류가 정확하지 않거나 유용하지 않기 때문에 이 오류를 숨기는 방법을 빠르게 조사합니다. 물론 그렇게 하는 방법도 있습니다.

XLOOKUP에는 이러한 오류를 처리하기 위해 "찾을 수 없는 경우" 인수가 내장되어 있습니다. 이전 예제에서 작동하는 것을 보겠습니다. 그러나 ID가 잘못 입력되었습니다.

다음 수식은 오류 메시지 대신 "Incorrect ID"라는 텍스트를 표시합니다. =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

범위 조회에 XLOOKUP 사용

정확한 일치만큼 일반적이지는 않지만 조회 수식의 매우 효과적인 사용은 범위에서 값을 찾는 것입니다. 다음 예를 들어보세요. 사용한 금액에 따라 할인을 반환하고 싶습니다.

이번에는 특정 값을 찾지 않습니다. B열의 값이 E열의 범위에 속하는 위치를 알아야 합니다. 그러면 할인이 결정됩니다.

XLOOKUP에는 일치 모드라는 선택적 다섯 번째 인수(정확한 일치로 기본 설정됨)가 있습니다.

XLOOKUP은 VLOOKUP보다 근사치로 일치하는 기능이 더 크다는 것을 알 수 있습니다.

(-1)보다 작거나 (1)보다 큰 가장 가까운 일치를 찾는 옵션이 있습니다. ?와 같은 와일드카드 문자(2)를 사용하는 옵션도 있습니다. 아니면 그 *. 이 설정은 VLOOKUP에서와 같이 기본적으로 켜져 있지 않습니다.

이 예의 수식은 정확히 일치하는 항목이 없는 경우 찾은 값보다 가장 작은 값을 반환합니다. =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

그러나 #N/A 오류가 반환되는 C7 셀에 오류가 있습니다('찾을 수 없는 경우' 인수가 사용되지 않음). 지출 64가 할인 기준에 도달하지 않았기 때문에 0% 할인이 반환되어야 합니다.

XLOOKUP 함수의 또 다른 장점은 VLOOKUP처럼 조회 범위가 오름차순일 필요가 없다는 것입니다.

룩업 테이블 하단에 새 행을 입력한 다음 공식을 엽니다. 모서리를 클릭하고 끌어 사용 범위를 확장합니다.

수식은 즉시 오류를 수정합니다. 범위의 맨 아래에 "0"이 있는 것은 문제가 아닙니다.

개인적으로 여전히 조회 열을 기준으로 테이블을 정렬합니다. 맨 아래에 "0"이 있으면 나를 미치게 만들 것입니다. 그러나 공식이 깨지지 않았다는 사실은 훌륭합니다.

XLOOKUP은 HLOOKUP 기능도 대체합니다.

언급했듯이 XLOOKUP 함수는 HLOOKUP을 대체하기 위해 여기에도 있습니다. 하나의 기능이 두 개를 대체합니다. 훌륭한!

HLOOKUP 함수는 행을 따라 검색하는 데 사용되는 수평 조회입니다.

형제 VLOOKUP만큼 잘 알려져 있지는 않지만 헤더가 A열에 있고 데이터가 4행과 5행에 있는 아래와 같은 예에 유용합니다.

XLOOKUP은 아래쪽 열과 행을 따라 양방향으로 볼 수 있습니다. 더 이상 두 가지 다른 기능이 필요하지 않습니다.

이 예에서 수식은 셀 A2의 이름과 관련된 판매 값을 반환하는 데 사용됩니다. 4행을 따라 이름을 찾고 5행의 값을 반환합니다. =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP은 상향식에서 볼 수 있습니다

일반적으로 값의 첫 번째(종종만) 발생을 찾으려면 목록을 검색해야 합니다. XLOOKUP에는 검색 모드라는 여섯 번째 인수가 있습니다. 이렇게 하면 조회를 맨 아래에서 시작하도록 전환하고 대신 목록을 조회하여 값의 마지막 항목을 찾을 수 있습니다.

아래 예에서 A열의 각 제품에 대한 재고 수준을 찾고 싶습니다.

조회 테이블은 날짜 순서로 되어 있으며 제품당 여러 개의 재고 확인이 있습니다. 마지막으로 확인한 시간(제품 ID의 마지막 항목)의 재고 수준을 반환하려고 합니다.

XLOOKUP 함수의 여섯 번째 인수는 네 가지 옵션을 제공합니다. 우리는 "마지막에서 먼저 검색" 옵션을 사용하는 데 관심이 있습니다.

완성된 공식은 다음과 같습니다. =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

이 수식에서 네 번째 및 다섯 번째 인수는 무시되었습니다. 선택 사항이며 정확한 일치의 기본값을 원했습니다.

모으다

XLOOKUP 함수는 VLOOKUP 및 HLOOKUP 함수 모두에 대한 간절히 기다리고 있는 후속 기능입니다.

이 문서에서는 XLOOKUP의 장점을 보여주기 위해 다양한 예제를 사용했습니다. 그 중 하나는 XLOOKUP을 시트, 통합 문서 및 테이블에서 사용할 수 있다는 것입니다. 예제는 우리의 이해를 돕기 위해 기사에서 간단하게 유지되었습니다.

동적 배열이 곧 Excel에 도입되기 때문에 다양한 값을 반환할 수도 있습니다. 이것은 확실히 더 탐구할 가치가 있는 것입니다.

VLOOKUP의 날짜는 번호가 매겨져 있습니다. XLOOKUP이 여기에 있으며 곧 사실상의 조회 공식이 될 것입니다.