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

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

XLOOKUP이란 무엇입니까?

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

현재 XLOOKUP은 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가 잘못 입력되어 작동하는 모습을 살펴 보겠습니다.

다음 수식은 오류 메시지 대신 "잘못된 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)

그러나 C7 셀에 # N / A 오류가 반환되는 오류가 있습니다 ( 'if not found'인수가 사용되지 않음). 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이 여기에 있으며 곧 사실상의 조회 공식이 될 것입니다.