엑셀 동적 배열 및 다중 조건 참조 함수 완벽 가이드 (XLOOKUP, FILTER, SORT)

1. VLOOKUP과 INDEX MATCH의 한계, 그리고 동적 배열의 등장 배경

VLOOKUP과 INDEX MATCH는 오랫동안 엑셀 실무의 표준으로 자리잡아 왔지만 두 함수 모두 근본적으로 하나의 값만 반환한다는 공통된 한계를 가지고 있습니다. 예를 들어 특정 지점에서 판매된 노트북 매출 건이 여러 건 존재할 때, VLOOKUP은 조건에 맞는 첫 번째 행만 찾아서 반환할 뿐 나머지 건은 무시해 버립니다. 이런 경우 실무자들은 어쩔 수 없이 보조열을 만들어 일련번호를 부여하거나, 배열 수식을 복잡하게 조합하는 우회 방법을 써야 했습니다.

또한 여러 조건을 동시에 만족하는 데이터를 찾아야 할 때도 VLOOKUP과 INDEX MATCH는 조건을 하나의 텍스트로 억지로 합쳐주는 보조열 방식에 의존해야 했습니다. 이런 방식은 원본 데이터를 건드려야 한다는 부담이 있고, 조건이 세 개 이상으로 늘어나면 수식이 급격히 복잡해집니다.

이런 구조적 한계를 근본적으로 해결한 것이 바로 동적 배열 함수입니다. XLOOKUP은 기존 VLOOKUP의 방향 제약과 오류 처리 불편함을 해결했고, FILTER는 조건에 맞는 데이터를 개수 제한 없이 한 번에 배열로 뽑아낼 수 있으며, SORT는 그 결과를 별도의 정렬 작업 없이 자동으로 원하는 순서로 정리해 줍니다. 이 세 함수를 조합하면 보조열이나 매크로 없이도 조건별 실시간 동적 보고서를 수식 한 줄로 완성할 수 있습니다.

2. XLOOKUP 문법 완벽 해부

XLOOKUP의 기본 문법은 다음과 같습니다.

수식: =XLOOKUP(찾을값, 찾을범위, 반환범위, 찾지못했을때, 일치모드, 검색모드)

첫 번째 인수인 찾을값과 두 번째 인수인 찾을범위는 VLOOKUP과 동일한 개념이지만, 세 번째 인수인 반환범위를 찾을범위와 완전히 독립적으로 지정할 수 있다는 점이 결정적인 차이입니다. 반환범위가 찾을범위보다 왼쪽에 있어도, 오른쪽에 있어도 전혀 상관없이 자유롭게 지정할 수 있습니다.

네 번째 인수인 찾지못했을때는 값을 찾지 못했을 경우 표시할 문구를 직접 지정하는 자리로, 별도로 IFERROR 함수를 씌우지 않아도 XLOOKUP 하나만으로 오류 처리가 끝납니다. 다섯 번째 인수인 일치모드는 기본값이 정확히 일치이므로 VLOOKUP처럼 실수로 근사값 검색이 되어버리는 위험이 원천적으로 차단됩니다. 여섯 번째 인수인 검색모드를 활용하면 데이터를 아래에서부터 위로 검색해서 가장 최근 값을 찾는 것도 간단히 설정할 수 있습니다.

3. FILTER 함수: 다중 조건 동적 배열 추출

FILTER 함수는 지정한 조건을 만족하는 모든 행을 한 번에 배열로 반환합니다.

수식: =FILTER(배열, 포함조건, 찾는값없을때)

첫 번째 인수인 배열은 결과로 가져올 전체 데이터 범위이고, 두 번째 인수인 포함조건은 참 또는 거짓으로 판별되는 조건식입니다. 이 조건에 해당하는 행만 자동으로 걸러져서 원본 데이터 아래로 스필 형태로 흩뿌려집니다. 세 번째 인수인 찾는값없을때는 조건에 맞는 행이 하나도 없을 경우 표시할 문구를 지정하는 자리입니다.

여러 조건을 동시에 만족해야 하는 다중 조건 검색은 각 조건을 곱하기 기호로 연결하면 됩니다. 조건이 참이면 1, 거짓이면 0으로 계산되는 원리를 이용해 모든 조건이 동시에 참일 때만 결과값이 1이 되어 필터링되는 방식입니다.

수식: =FILTER(A2:D6, (B2:B6="서울점")*(D2:D6>=500000), "조건에 맞는 데이터 없음")

여러 조건 중 하나만 만족해도 되는 경우에는 곱하기 기호 대신 더하기 기호로 조건을 연결하면 됩니다. 하나라도 참이면 결과값이 1 이상이 되어 조건을 만족하는 것으로 처리됩니다.

4. SORT와 SORTBY: 결과를 자동으로 정렬하는 동적 배열

SORT 함수는 지정한 배열을 원하는 기준 열과 순서로 자동 정렬해서 반환합니다.

수식: =SORT(배열, 정렬기준열, 정렬순서, 방향)

정렬기준열 자리에는 정렬 기준으로 삼을 열이 배열의 몇 번째 열인지를 숫자로 입력하며, 정렬순서 자리에는 오름차순이면 1, 내림차순이면 마이너스 1을 입력합니다. SORT의 진짜 위력은 FILTER와 중첩될 때 드러납니다. FILTER로 먼저 조건에 맞는 데이터를 걸러낸 뒤 그 결과 전체를 SORT로 감싸주면, 조건에 맞는 데이터를 뽑아내는 동시에 원하는 기준으로 자동 정렬까지 완료된 결과를 수식 한 줄로 얻을 수 있습니다.

수식: =SORT(FILTER(A2:D6, B2:B6="서울점"), 4, -1)

SORTBY 함수는 SORT와 달리 정렬 기준을 배열 안의 열 번호가 아니라 별도의 독립적인 배열로 지정할 수 있어서, 반환할 데이터와 정렬 기준이 되는 데이터가 서로 다른 범위에 있을 때 특히 유용합니다.

5. 따라하기: 실무 더미 데이터를 활용한 단계별 적용

아래와 같은 매출 데이터가 A1부터 D6까지 있다고 가정하겠습니다.

1단계, 단일 조건 XLOOKUP 실습. 서울점의 첫 번째 매출 건 품목을 찾고 싶다면 다음과 같이 작성합니다.

수식: =XLOOKUP("서울점", B2:B6, C2:C6, "데이터 없음", 0)

이 수식은 B2부터 B6 범위에서 서울점을 정확히 일치하는 값으로 찾아 C열의 품목을 반환하며, 찾지 못하면 데이터 없음이라는 문구를 표시합니다.

2단계, 다중 조건 FILTER 실습. 서울점에서 판매되었으면서 동시에 매출액이 500000 이상인 모든 행을 한 번에 추출하고 싶다면 다음과 같이 작성합니다.

수식: =FILTER(A2:D6, (B2:B6="서울점")*(D2:D6>=500000), "조건에 맞는 데이터 없음")

이 수식을 빈 셀에 입력하고 엔터를 누르면, 서울점 조건과 매출액 조건을 동시에 만족하는 행인 2026년 7월 1일 노트북 건과 2026년 7월 15일 모니터 건 두 개의 행이 자동으로 아래로 흩뿌려지며 결과가 채워집니다.

3단계, FILTER와 SORT 결합 실습. 위 결과를 매출액이 큰 순서대로 자동 정렬해서 보고 싶다면 FILTER 전체를 SORT로 감싸줍니다.

수식: =SORT(FILTER(A2:D6, (B2:B6="서울점")*(D2:D6>=500000), "데이터 없음"), 4, -1)

이 수식에서 SORT의 두 번째 인수인 4는 FILTER 결과 배열 안에서 네 번째 열, 즉 매출액 열을 정렬 기준으로 삼으라는 뜻이고 세 번째 인수인 마이너스 1은 내림차순 정렬을 의미합니다. 결과적으로 매출액이 가장 큰 행이 맨 위로 오도록 자동 정렬된 최종 보고서가 완성됩니다.

6. 도구별 성능 비교: VLOOKUP vs INDEX MATCH vs XLOOKUP vs FILTER

결론적으로 단일 값을 찾는 조회 업무라면 XLOOKUP이, 조건에 맞는 여러 행을 한 번에 뽑아내는 추출 업무라면 FILTER가 가장 강력한 선택지입니다. 다만 구버전 호환성이 반드시 필요한 환경이라면 여전히 INDEX MATCH가 안전한 대안으로 남아 있습니다.

7. 실무자가 가장 자주 묻는 질문 4가지

질문 하나, FILTER로 뽑은 결과에 새로운 데이터가 추가되면 자동으로 갱신되나요.
네, FILTER의 첫 번째 인수인 배열 범위 안에 새로운 데이터가 추가되면 결과도 자동으로 갱신됩니다. 다만 원본 범위를 정식 표로 미리 변환해두면 범위 자체가 자동으로 늘어나므로 훨씬 안정적으로 관리할 수 있습니다.

질문 둘, XLOOKUP으로 여러 개의 열을 한 번에 가져올 수 있나요.
가능합니다. 세 번째 인수인 반환범위 자리에 한 개의 열이 아니라 여러 개의 열로 구성된 범위를 통째로 지정하면, 결과값도 여러 개의 열로 자동으로 펼쳐져서 반환됩니다.

질문 셋, FILTER 조건에서 OR 조건, 즉 여러 값 중 하나라도 해당하면 되는 조건은 어떻게 작성하나요.
각 조건을 더하기 기호로 연결하면 됩니다. 예를 들어 지점이 서울점이거나 부산점인 행을 모두 찾고 싶다면 두 조건을 각각 괄호로 감싼 뒤 더하기 기호로 이어주면, 둘 중 하나라도 참인 행이 모두 결과에 포함됩니다.

질문 넷, 구버전 엑셀 사용자와 파일을 공유해야 하는데 그래도 동적 배열의 장점을 살릴 방법이 있나요.
구버전에서는 XLOOKUP과 FILTER 자체가 지원되지 않으므로, 이 경우 배포용 파일에서는 계산이 완료된 결과값만 값으로 붙여넣기 하여 전달하거나, VLOOKUP과 INDEX MATCH로 작성된 대체 버전을 별도로 준비해두는 것이 현실적인 대안입니다.

8. 작업 효율을 높이는 실무 체크리스트

동적 배열 수식을 배포하기 전 다음 사항을 반드시 점검해야 합니다. 첫째, 결과가 펼쳐질 스필 범위 안에 다른 데이터나 서식이 남아있지 않은지 확인합니다. 둘째, FILTER의 세 번째 인수에 조건에 맞는 데이터가 없을 때 표시할 문구가 지정되어 있는지 확인합니다. 셋째, 다중 조건을 곱하기로 연결했는지 더하기로 연결했는지, 즉 AND 조건인지 OR 조건인지 의도한 대로 작성되었는지 재확인합니다. 넷째, 파일을 공유할 대상이 구버전 엑셀을 사용하지 않는지 사전에 확인합니다.