XLOOKUP 함수를 사용하여 테이블 또는 범위에서 행별로 항목을 찾습니다. 예를 들어 부품 번호로 자동차 부품의 가격을 찾거나 직원 ID를 기반으로 직원 이름을 찾습니다. XLOOKUP을 사용하면 반환 열이 어느 쪽에 있는지에 관계없이 한 열에서 검색어를 검색하고 다른 열의 같은 행에서 결과를 반환할 수 있습니다.
참고
XLOOKUP은 Excel 2016 및 Excel 2019에서 사용할 수 없습니다. 그러나 다른 사람이 최신 버전의 Excel을 사용하여 통합 문서를 만든 경우 XLOOKUP 함수가 포함된 통합 문서를 Excel 2016 또는 Excel 2019에서 사용하는 상황이 발생할 수 있습니다.
구문
XLOOKUP 함수는 범위 또는 배열을 검색한 다음 검색된 첫 번째 일치 항목에 해당하는 항목을 반환합니다. 일치하는 항목이 없는 경우 XLOOKUP 함수는 가장 가까운(대략적인) 일치 항목을 반환할 수 있습니다.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| 인수 | 설명 |
|---|---|
|
lookup_value 필수* |
검색할 값 *생략하면 XLOOKUP은 lookup_array에서 찾은 빈 셀을 반환합니다. |
|
lookup_array 필수 |
검색할 배열 또는 범위 |
|
return_array 필수 |
반환할 배열 또는 범위 |
|
[if_not_found] 선택 사항 |
유효한 일치 항목을 찾을 수 없는 경우 제공한 [if_not_found] 텍스트를 반환합니다. 유효한 일치가 발견되지 않고 [if_not_found]이 누락된 경우 #N/A 가 반환됩니다. |
|
[match_mode] 선택 |
일치 유형을 지정합니다. 0 - 정확히 일치합니다. 아무것도 찾을 수 없으면 #N/A를 반환합니다. 기본값입니다. -1 - 정확히 일치합니다. 아무것도 찾지 못하면 다음으로 작은 항목을 반환합니다. 1 - 정확히 일치합니다. 아무것도 찾지 못하면 다음으로 큰 항목을 반환합니다. 2 - *, ? 및 ~가 특별한 의미를 갖는 와일드카드 매치입니다. |
|
[search_mode] 선택 사항 |
사용할 검색 모드를 지정합니다. 1 - 첫 번째 항목부터 검색을 수행합니다. 기본값입니다. -1 - 마지막 항목부터 역방향 검색을 수행합니다. 2 - 오름차순 으로 정렬되는 lookup_array에 의존하는 이진 검색을 수행합니다. 정렬하지 않으면 잘못된 결과가 반환됩니다. 2 - 오름차순으로 정렬되는 lookup_array를 사용하는 이진 검색을 실행합니다. 정렬하지 않으면 잘못된 결과가 반환됩니다. |
예제
예제 1 은 XLOOKUP을 사용하여 범위에서 국가 이름을 조회한 다음 해당 전화 국가 코드를 반환합니다. 여기에는 lookup_value (셀 F2), lookup_array (범위 B2:B11) 및 return_array (범위 D2:D11) 인수가 포함됩니다. XLOOKUP은 기본적으로 정확한 일치 항목을 생성하므로 match_mode 인수는 포함되지 않습니다.
참고
XLOOKUP은 조회 배열과 반환 배열을 사용하는 반면, VLOOKUP은 단일 테이블 배열 뒤에 열 인덱스 번호를 사용합니다. 이 경우에 해당하는 VLOOKUP 수식은 =VLOOKUP(F2,B2:D11,3,FALSE)입니다.
———————————————————————————
예제 2 는 직원 ID 번호를 기반으로 직원 정보를 조회합니다. VLOOKUP과 달리 XLOOKUP은 여러 항목이 있는 배열을 반환할 수 있으므로 단일 수식이 C5:D14 셀에서 직원 이름과 부서를 모두 반환할 수 있습니다.
———————————————————————————
예제 3 에서는 앞의 예제에 if_not_found 인수를 추가합니다.
———————————————————————————
예제 4는 C 열에서 셀 E2에 입력한 개인 소득을 찾고 B 열에서 일치하는 세율을 찾습니다. 아무것도 찾을 수 없으면 반환 0 (영)으로 if_not_found 인수를 설정합니다. match_mode 인수는 로 설정됩니다. 1즉, 함수는 정확히 일치하는 항목을 찾고, 찾을 수 없으면 다음으로 큰 항목을 반환합니다. 마지막으로 search_mode 인수는 1로 설정됩니다. 즉 함수가 첫 번째 항목부터 마지막 항목까지 검색합니다.
참고
XARRAY의 lookup_array 열은 return_array 열의 오른쪽에 있는 반면 VLOOKUP은 왼쪽에서 오른쪽으로만 볼 수 있습니다.
———————————————————————————
예제 5 에서는 중첩된 XLOOKUP 함수를 사용하여 세로 및 가로 일치를 모두 수행합니다. 먼저 B 열에서 매출총이익 을 찾은 다음 테이블의 맨 위 행(범위 C5:F5)에서 Qtr1 을 찾은 다음 마지막으로 둘이 교차하는 값을 반환합니다. 이는 INDEX 함수와 MATCH 함수를 함께 사용하는 것과 비슷합니다.
팁
XLOOKUP을 사용하여 HLOOKUP 함수를 바꿀 수도 있습니다.
참고
D3:F3 셀의 수식은 =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)))입니다.
———————————————————————————
예제 6 에서는 SUM 함수와 두 개의 중첩된 XLOOKUP 함수를 사용하여 두 범위 사이의 모든 값의 합계를 구합니다. 이 경우 포도와 바나나의 값을 합산하고 배를 포함하려고 합니다. 둘 사이에 있습니다.
셀 E3의 수식은 다음과 같습니다. =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
어떻게 작동하나요? XLOOKUP은 범위를 반환하므로 계산할 때 수식은 다음과 =SUM($E$7:$E$9)같이 끝납니다. 이와 유사한 XLOOKUP 수식이 있는 셀을 선택한 다음 수식,>수식 감사>,수식 평가, 계산을 차례로 선택하여 계산을 단계별로 실행하여 그 방식을 직접 확인할 수 있습니다.
참고
이 예제를 제안해 주신 Microsoft Excel MVP인 Bill Jelen에게 감사드립니다.
———————————————————————————