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

Windows 및 Mac의 Office 365 구독자용 Excel 2016은 이제 새로운 XLOOKUP 기능을 지원합니다. 이 기능은 매우 인기 있는(그러나 종종 악의적인) 수직 조회 기능인 VLOOKUP(XLOOKUP의 X가 무엇인지 모릅니다 확장, 아마도?).

VLOOKUP(SUM 및 AVERAGE 바로 뒤에서 세 번째로 많이 사용되는 함수로 간주)에 아직 익숙하지 않은 사용자를 위해 이 함수는 지정된 조회 테이블의 가장 왼쪽 열에 있는 행을 기준으로 위에서 아래로 값을 찾을 때까지 수직으로 검색합니다. 찾고 있는 것과 일치하거나 초과하는 오프셋 번호로 지정된 조회 열. 워크시트에 있는 데이터 테이블의 긴 목록이나 열에서 특정 항목을 찾는 데 매우 유용하지만 VLOOKUP 함수에는 XLOOKUP과 같이 이 새로운 조회 함수에서 공유하지 않는 몇 가지 제한 사항이 있습니다.

• 조회 범위에서 조회 값과 정확히 일치하는 항목을 찾는 것이 기본값입니다.
• 테이블에서 세로(행 기준) 및 가로(열 기준) 검색이 가능하므로 가로로 열을 검색할 때 HLOOKUP 함수를 사용할 필요가 없습니다.
• 함수가 작동하기 위해 조회 테이블의 조회 범위가 반환 범위로 지정된 열의 왼쪽 열에 위치할 필요가 없도록 왼쪽 또는 오른쪽으로 검색할 수 있습니다.
• 정확히 일치 기본값을 사용하면 조회 범위의 값이 특정 순서로 정렬되지 않은 경우에도 작동합니다.
• 선택적 검색 모드 인수를 사용하여 조회 배열 범위의 맨 아래 행에서 맨 위로 검색할 수 있습니다.

XLOOKUP 함수에는 다음 구문을 사용하여 5개의 가능한 인수가 있으며 그 중 처음 3개는 필수이고 마지막 2개는 선택 사항입니다.

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

필수 lookup_value 인수는 검색할 값이나 항목을 지정합니다. 필요한 look_up 배열 인수는 이 조회 값을 검색할 셀 범위를 지정하고 return_array 인수는 Excel에서 정확히 일치하는 항목을 찾았을 때 반환하려는 값이 포함된 셀 범위를 지정합니다.

* XLOOKUP 함수에서 lookup_array 및 return_array 인수를 지정할 때 두 범위의 길이가 같아야 합니다. 그렇지 않으면 Excel에서 #VALUE를 반환합니다! 수식에 오류가 있습니다. 이러한 인수를 지정하거나 셀 참조를 입력하는 대신 지정된 데이터 테이블의 범위 이름이나 열 이름을 사용하는 이유가 여기에 있습니다 .

선택적 match_mode 인수는 다음 네 가지 값 중 하나를 포함할 수 있습니다.

• 정확히 일치하는 경우 0 (기본값, match_mode 인수가 지정 되지 않은 경우와 동일 )
• 정확히 일치하거나 다음으로 작은 값의 경우 -1
• 1 일치 또는 다음으로 큰 값
• lookup_value 인수의 셀 참조에 결합된 와일드카드 문자를 사용한 부분 일치의 경우 2

선택적 search_mode 인수는 다음 네 가지 값 중 하나를 포함할 수 있습니다.

• 1 처음부터 마지막으로, 즉 위에서 아래로 검색(기본값, search_mode 인수가 지정 되지 않은 경우와 동일 )
• -1: 마지막에서 처음으로, 즉 아래에서 위로 검색
• 2 오름차순 이진 검색의 경우
• -2 내림차순 이진 검색의 경우

새로운 XLOOKUP 기능의 강력함과 다용성을 이해하는 가장 좋은 방법은 Excel 워크시트에서 작동하는 모습을 보는 것입니다. 다음 그림에는 국가별로 정렬된 간단한 2019년 판매 데이터 테이블이 있는 워크시트가 있습니다. XLOOKUP을 사용하여 워크시트의 D4 셀에 입력한 국가를 기준으로 E4 셀에 있는 이 테이블의 총 판매액을 반환하려면 다음 단계를 수행합니다.

워크시트의 E4 셀에 셀 커서를 놓습니다.

수식 탭에서 조회 및 참조 옵션을 클릭한 다음 드롭다운 메뉴 하단 근처에 있는 XLOOKUP을 클릭하여 함수 인수 대화 상자를 엽니다.

워크시트에서 D4 셀을 클릭하여 Lookup_value 인수 텍스트 상자에 해당 셀 참조를 입력합니다.

Tab 키를 눌러 Lookup_array 인수 텍스트 상자를 선택한 다음 셀 A4를 클릭하고 Shift 키를 누른 상태에서 Ctrl-아래쪽 화살표를 눌러 A4:A8을 검색 범위로 선택합니다(A3:B8 범위는 Excel 데이터 테이블로 정의되어 있으므로, Table1[국가]는 A4:A8 범위 대신 텍스트 상자에 나타납니다.

Tab 키를 눌러 Return_array 인수 텍스트 상자를 선택한 다음 B4 셀을 클릭하고 Shift 키를 누른 상태에서 Ctrl-아래쪽 화살표를 눌러 검색 결과( Table1[Total Sales](텍스트 상자).

확인을 클릭하여 E4 셀에 XLOOKUP 수식을 입력합니다.

셀 D4에 입력한 국가를 기반으로 매출을 반환하는 셀 E4에 XLOOKUP을 사용하여 수식을 만듭니다.

Excel은 워크시트의 E4 셀에 XLOOKUP 수식을 입력하고 결과로 4900을 반환합니다. 코스타리카는 현재 조회 셀 D4에 입력되어 있고 2019년 판매 테이블에서 볼 수 있듯이 이는 실제로 이 국가에서 발생한 총 판매액이기 때문입니다.

XLOOKUP은 왼쪽에서 오른쪽과 마찬가지로 오른쪽에서 왼쪽으로 작동하므로 이 함수를 사용하여 특정 판매 수치를 기반으로 이 판매 테이블에서 국가를 반환할 수도 있습니다. 다음 그림은 이 작업을 수행하는 방법을 보여줍니다. 이번에는 D4 셀에 XLOOKUP 수식을 만들고 E4 셀에 입력한 값(이 경우 11,000)을 lookup_value 인수로 지정합니다.

또한 match_mode 인수로 -1을 입력하여 함수의 정확히 일치 기본값을 재정의하여 Excel에서 조회 셀 E4에 입력한 판매 값과 정확히 일치하는 국가 또는 다음으로 낮은 총 판매액(멕시코 이 테이블에 총 판매액이 $11,000인 국가가 없으므로 이 경우 $10,000). 이 수식에 대해 match_mode 인수를 지정하지 않으면 이 판매 테이블에 $11,000와 정확히 일치하는 항목이 없기 때문에 Excel은 결과로 #NA를 반환합니다.

셀 E4에 입력한 매출을 기반으로 국가를 반환하는 셀 D4에 XLOOKUP을 사용하여 수식 만들기

XLOOKUP 함수는 세로로 행을 검색하는 것과 마찬가지로 가로로 열을 검색하는 것이 편하기 때문에 이를 사용하여 양방향 조회를 수행하는 수식을 만들 수 있습니다(다음과 같이 INDEX 및 MATCH 기능을 결합하는 수식을 만들 필요가 없습니다. 과거에). 4월부터 12월까지 부품 번호 AB-100부터 AB-103까지의 2019년 생산 일정 테이블이 포함된 다음 그림은 이 작업이 어떻게 수행되는지 보여줍니다.

특정 월에 부품에 대해 생산된 단위 수를 반환하는 중첩된 XLOOKUP 함수가 있는 수식 만들기

B12 셀에서 다음 수식을 만들었습니다.

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

이 공식은 생산 테이블의 셀 범위 $A$3:$A$6에 있는 part_lookup(이 경우 셀 B10)이라는 셀에서 만들어진 부품 항목과 정확히 일치하는 항목을 행별로 수직으로 검색하는 XLOOKUP 함수를 정의하는 것으로 시작됩니다. . 그러나 이 원래 LOOKUP 함수에 대한 return_array 인수는 그 자체로 두 번째 XLOOKUP 함수입니다.

이 두 번째 중첩된 XLOOKUP 함수는 date_lookup이라는 셀(이 경우 셀 B11)에 작성된 날짜 항목과 정확히 일치하는지 열을 기준으로 셀 범위 $B$2:$J$2를 수평으로 검색합니다. 이 두 번째 중첩 XLOOKUP 함수의 return_array 인수는 테이블에 있는 모든 생산 값의 셀 범위인 $B$3:$J$6입니다.

이 수식이 작동하는 방식은 Excel이 먼저 수평 검색을 수행하여 두 번째 중첩 XLOOKUP 함수의 결과를 계산하는 것입니다. 이 경우에는 Jun-19 열의 D3: D6 셀 범위에 있는 배열을 반환합니다(값: 438, 153, 306, 779) 그 결과입니다. 이 결과는 차례로 셀 B11(part_lookup이라는 이름의)에 작성된 부품 번호 항목과 정확히 일치하는지 행별로 수직 검색을 수행하는 원래 XLOOKUP 함수에 대한 return_array 인수가 됩니다. 이 예에서 이 part_lookup 셀에 AB-102가 포함되어 있기 때문에 수식은 다음 XLOOKUP 함수의 두 번째 결과에서 6월 19일 생산 값 306만 반환합니다.

당신은 그것을 가지고 있습니다! VLOOKUP 및 HLOOKUP 함수로 수행되는 단일 값 조회뿐만 아니라 INDEX 및 MATCH 기능도 있습니다.

* 불행하게도, XLOOKUP 기능은 없습니다 만 아직 엑셀 2019과 엑셀 온라인으로 자신의 조회 기능 중 하나를 포함하지 않는 최신 버전으로 VLOOKUP과 HLOOKUP 함수 또는 호환을 지원하는 이전 버전의 Microsoft Excel과 호환 . 즉, 이 새로운 조회 기능이 포함되지 않은 Excel 버전을 사용하는 동료 또는 클라이언트와 XLOOKUP 수식이 포함된 통합 문서를 공유하면 이러한 모든 수식이 #NAME? 워크시트를 열 때 오류 값.