XLOOKUP 여러 열 반환
이 튜토리얼에서는 Excel에서 XLOOKUP을 사용하여 여러 열을 반환하는 방법을 보여드립니다. 사용 중인 Excel 버전이 XLOOKUP을 지원하지 않는 경우 대신 VLOOKUP을 사용하여 여러 열을 반환하는 방법을 읽어보세요.
연속된 열을 반환하는 XLOOKUP
XLOOKUP 함수의 한 가지 장점은 연속된 여러 열을 한 번에 반환할 수 있다는 것입니다. 이렇게 하기 위해선, 반환 배열에 연속 열의 범위를 입력하면 모든 열이 반환됩니다.
=XLOOKUP(F3,B3:B7,C3:D7)
여기서 반환 배열은 두 개의 열(C와 D)이므로 두 개의 열이 반환됩니다.
인접하지 않은 열 반환하는 XLOOKUP-FILTER
XLOOKUP 함수는 연속된 여러 열을 반환할 수 있지만 인접하지 않은 열은 반환할 수 없습니다. 인접하지 않은 열을 반환하려면 FILTER 함수를 사용할 수 있습니다.
=XLOOKUP(F3,C3:C7,FILTER(B3:D7,{1,0,1}))
수식을 살펴봅시다.
FILTER 함수
FILTER 함수는 일반적으로 행을 필터링하는데 사용되지만 열을 필터링하는 데에도 사용할 수 있습니다. 이를 위해 배열 입력(첫 번째 인수)에서 열 개수와 동일한 1(TRUE)과 0(FALSE)의 가로 배열을 입력합니다. 0과 일치하는 열은 필터링되고 1과 일치하는 열은 반환됩니다.
=FILTER(B3:D7,{1,0,1})
XLOOKUP 함수
그런 다음 FILTER 함수의 출력이 XLOOKUP의 반환 배열로 사용됩니다
=XLOOKUP(F3,C3:C7,H3:I7)
모든 함수를 결합하면 원래의 수식이 됩니다.
=XLOOKUP(F3,C3:C7,FILTER(B3:D7,{1,0,1}))인접하지 않은 열 반환하는 XLOOKUP-FILTER-COUNTIF
필터링할 열을 수동으로 선택하는 대신 COUNTIF 함수를 사용하여 반환 및 필터링할 열을 자동으로 결정할 수 있습니다.
=XLOOKUP(F3,C3:C7,FILTER(B3:D7,COUNTIF(G2:H2,B2:D2)))
수식을 살펴봅시다.
COUNTIF 함수
먼저, 출력 헤더(예: M2:N2)에서 각 소스 헤더(예: B2:D2)의 인스턴스를 카운트하기 위해 COUNTIF 함수를 사용해 보겠습니다.
=COUNTIF(M2:N2,B2:D2)
FILTER 함수
그런 다음 COUNTIF 함수의 배열 결과를 필터 조건으로 사용하여 열을 필터링합니다.
=FILTER(B3:D7,H3:J3)
XLOOKUP 함수
마지막으로, XLOOKUP 함수에 FILTER 함수의 결과를 사용하여 원하는 열을 반환할 수 있습니다.
=XLOOKUP(F3,C3:C7,K3:L7)
모든 함수를 결합하면 원래의 수식이 됩니다.
=XLOOKUP(F3,C3:C7,FILTER(B3:D7,COUNTIF(G2:H2,B2:D2)))XLOOKUP-다른 순서로 반환 열 선택하기
XLOOKUP-FILTER-COUNTIF 수식은 인접하지 않은 열을 반환할 수 있지만 다른 순서로 열을 반환하려면 어떻게 해야 할까요? 이 문제를 해결하기 위해 CHOOSE 함수를 사용해 보겠습니다.
=XLOOKUP(F3,B3:B7,CHOOSE({2,1},C3:C7,D3:D7))
수식을 살펴봅시다.
CHOOSE 함수
먼저 CHOOSE 함수를 사용하여 지정된 순서대로 열을 하나의 배열로 조인합니다.
=CHOOSE({2,1},C3:C7,D3:D7)
참고: CHOOSE 함수에 원하는 열을 개별적으로 입력하고(예: C3:C7, D3:D7), index_num(첫 번째 인수)을 사용하여 열의 순서를 지정합니다.
XLOOKUP 함수
그런 다음 CHOOSE 함수의 결과 배열이 반환 배열로 사용됩니다.
=XLOOKUP(F3,B3:B7,H3:I7)
모든 함수를 결합하면 원래의 수식이 됩니다.
=XLOOKUP(F3,B3:B7,CHOOSE({2,1},C3:C7,D3:D7))다른 순서로 열을 반환하는 XLOOKUP-CHOOSECOLS
반환할 열이 많으면 XLOOKUP-CHOOSE 수식이 길고 지루해집니다. 다행히도 CHOOSE 함수보다 더 편리한 새로운 배열 함수(현재 Excel 365 베타 버전에 있음)인 CHOOSECOLS 함수가 있습니다.
=XLOOKUP(F3,B3:B7,CHOOSECOLS(B3:D7,3,2))
수식을 살펴봅시다.
CHOOSECOLS 함수
지정된 순서로 열을 조인하는 CHOOSE 함수와 달리 CHOOSECOLS 함수는 지정한 순서대로 선택한 열을 반환합니다.
=CHOOSECOLS(B3:D7,3,2)
참고: 두 번째 인수부터 시작하는 col_num 인수에 지정하는 숫자는 지정된 배열에서 열의 상대 열 번호입니다.(예: B3:D7)
XLOOKUP 함수
마지막으로, CHOOSECOLS 함수의 결과와 함께 XLOOKUP 함수를 사용할 수 있습니다.
=XLOOKUP(F3,B3:B7,H3:I7)
모든 함수를 결합하면 원래의 수식이 됩니다.
=XLOOKUP(F3,B3:B7,CHOOSECOLS(B3:D7,3,2))다른 순서로 열을 반환하는 XLOOKUP-CHOOSECOLS-MATCH 함수
열과 열의 순서를 수동으로 선택하는 대신 MATCH 함수를 사용하여 작업에 필요한 열과 열의 순서를 자동으로 결정할 수 있습니다.
=XLOOKUP(F3,B3:B7,CHOOSECOLS(B3:D7,MATCH(G2:H2,B2:D2,0)))
수식을 살펴보겠습니다.
MATCH 함수
먼저 MATCH 함수를 사용하여 소스 헤더에서 각 출력 헤더의 상대적인 열 위치를 결정합니다.
=MATCH(L2:M2,B2:D2,0)
CHOOSECOLS 함수
이제 선택한 열의 위치를 알았으므로 CHOOSECOLS 함수를 사용하여 선택한 열을 하나의 배열로 반환할 수 있습니다.
=CHOOSECOLS(B3:D7,H3:I3)
XLOOKUP 함수
마지막 단계는 CHOOSECOLS 함수의 결과를 XLOOKUP 함수의 반환 배열에 공급하는 것입니다.
=XLOOKUP(F3,B3:B7,J3:K7)
모든 함수를 결합하면 원래의 수식이 됩니다.
=XLOOKUP(F3,B3:B7,CHOOSECOLS(B3:D7,MATCH(G2:H2,B2:D2,0))))
