전체 행 또는 열 반환하기

지금까지 XLOOKUP은 하나의 값을 반환했습니다. 하지만 데이터의 전체 행이나 열도 한 번에 반환할 수 있습니다.

수식이 여러 값을 반환하면 값이 인접한 셀로 자동으로 펼쳐집니다. 따라서 하나의 XLOOKUP으로 작은 전체 기록을 채울 수 있습니다.

비결은 반환 배열이 여러 열에 걸치도록 만드는 것입니다. B2:B20만 반환하는 대신 B2:E20을 반환합니다.

XLOOKUP은 일치하는 행을 찾은 다음 반환 배열에서 해당 행의 모든 열을 돌려줍니다. 하나의 수식으로 네 가지 결과를 얻는 것입니다.

예를 들어 F2와 같은 하나의 셀에 수식을 입력하고 Enter 키를 누르세요. 값이 F2, G2, H2, I2에 걸쳐 표시됩니다.

옅은 파란색 테두리가 자동 확장 범위를 둘러쌉니다. 왼쪽 위 셀만 편집하면 되며, 나머지 셀은 자동으로 채워져 직접 변경할 수 없습니다.

직원 표의 A열에는 ID가 있고 B열부터 E열까지 이름, 부서, 직무, 급여가 있다고 가정해 보겠습니다.

D2에 ID를 입력하면 하나의 XLOOKUP으로 전체 기록을 반환합니다. ID를 바꾸면 전체 행이 즉시 업데이트되므로, 하나의 수식으로 만든 작은 조회 도구가 됩니다.

같은 방식은 세로 방향으로도 작동합니다. 헤더 행을 가로로 검색하면 결과의 전체 열을 반환할 수 있습니다.

여기서는 XLOOKUP이 헤더 행 B1:E1에서 D2의 레이블을 검색하고, 일치하는 헤더 아래의 전체 열 B2:E50을 따라 결과를 아래로 펼칩니다.

수식이 자동으로 펼쳐진 후에는 셀 뒤에 해시 기호를 붙여 전체 자동 확장 범위를 참조할 수 있습니다. 예를 들어 F2#처럼 사용합니다.

이 기능은 강력합니다. 행의 너비가 바뀌어도 자동 확장된 행에 SUM을 적용한 결과가 계속 올바른데, F2#가 항상 "F2에서 시작하는 전체 자동 확장 범위"를 의미하기 때문입니다.

결과가 배열이므로 범위를 허용하는 함수에 직접 전달할 수 있습니다.

예를 들어 조회 결과를 SUM으로 감싸 반환된 월별 수치를 모두 합산할 수 있습니다. 보조 셀 없이 하나의 수식으로 처리됩니다.

자동으로 펼쳐지는 수식에는 값을 채울 빈 셀이 필요합니다. 자동 확장 경로에 이미 데이터가 들어 있는 셀이 하나라도 있으면 XLOOKUP은 #SPILL! 오류를 반환합니다.

해결 방법은 간단합니다. 방해하는 셀의 내용을 지우거나 수식을 비어 있는 영역으로 옮기세요. 확장 범위는 완전히 비어 있어야 합니다.

완성도 높은 조회 카드를 만들려면 필드 헤더도 함께 자동으로 펼칠 수 있습니다.

데이터 행을 반환하는 XLOOKUP 하나를 배치하고, 그 위에서 헤더 범위를 참조하세요. 자동 확장 범위의 폭이 넓어지거나 좁아져도 열 이름이 반환된 열과 계속 맞습니다.

한 XLOOKUP을 다른 XLOOKUP 안에 중첩할 수도 있습니다. 안쪽 XLOOKUP은 전체 열을 반환하고, 바깥쪽 XLOOKUP은 그중 하나의 셀을 선택합니다.

이렇게 하면 XLOOKUP만으로 행과 열을 모두 일치시키는 진정한 양방향 조회가 가능합니다. INDEX-MATCH-MATCH를 대신할 수 있는 깔끔한 방법입니다.

이제 XLOOKUP이 둘 이상의 값을 반환할 수 있다는 것을 알게 되었습니다.

• 여러 열로 이루어진 반환 배열은 전체 행을 자동으로 펼칩니다
• 여러 행으로 이루어진 반환 배열은 전체 열을 자동으로 펼칩니다
• # 접미사를 사용해 자동 확장 범위를 참조합니다
• #SPILL!을 피하려면 방해하는 셀의 내용을 지웁니다

이 기능을 사용하면 하나의 수식이 완전한 기록 조회 화면이 됩니다.

XLOOKUP 과정을 마치며 결과를 자동으로 펼치는 방법을 배웠습니다.

• 반환 배열을 넓혀 전체 행이나 열을 자동으로 펼칩니다
• 자동 확장 결과는 비어 있는 인접 셀을 채우고 파란색 테두리로 표시됩니다
• F2#처럼 # 접미사를 사용해 자동 확장 범위를 참조합니다
• 확장 영역을 비워 두어 #SPILL!을 피합니다

구문, 대체값, 방향, 자동 확장 기능을 모두 익히면 XLOOKUP으로 작성하는 기존 조회 수식의 거의 전부를 대체할 수 있습니다.