문서·업무 도구가이드
엑셀 반복업무를 줄이는 함수 7개: XLOOKUP·FILTER·UNIQUE 예제로 배우기
주문 표 하나로 XLOOKUP, FILTER, UNIQUE, SORT, SUMIFS, TEXTSPLIT, IFERROR를 차례로 써 보는 예제를 수식과 정확한 결과값까지 정리했어요. 함수마다 쓸 수 있는 엑셀 버전은 Microsoft 공식 문서 기준으로 표시했어요.
이 글에서 얻는 것
- XLOOKUP 하나로 '코드로 이름·단가 찾기'가 끝나고, 못 찾았을 때 보여 줄 말도 수식 안에 넣을 수 있어요.
- FILTER·UNIQUE·SORT는 결과가 여러 칸으로 '흘러넘쳐(spill)' 채워지니, 결과가 들어갈 칸을 비워 두어야 해요.
- SUMIFS는 합할 범위를 맨 앞에 쓰고, 조건은 '범위, 조건' 쌍으로 계속 붙여요.
- TEXTSPLIT은 공식 문서의 적용 대상이 Microsoft 365와 Excel 2024뿐이에요. Excel 2021 사용자와 함께 쓰는 파일에는 넣지 마세요.
- IFERROR는 오류를 모두 가려 주기 때문에, 진짜 실수까지 숨기지 않도록 범위를 좁혀 써야 해요.
매주 같은 표에서 같은 사람 걸 골라내고, 같은 합계를 다시 계산하고 있다면 함수로 바꿀 차례예요. 아래 일곱 개는 '찾기 → 골라내기 → 정리하기 → 합치기 → 쪼개기 → 오류 정리'라는 반복업무의 흐름을 그대로 따라가요. 예제는 모두 같은 작은 주문 표를 쓰니, 표를 그대로 옮겨 적고 수식을 붙여 넣으면 결과를 바로 비교해 볼 수 있어요.
함수별로 쓸 수 있는 버전(Microsoft 지원 문서 기준)
| 함수 | 하는 일 | Windows용 Excel 버전 |
|---|---|---|
| XLOOKUP | 값을 찾아 옆 칸 값 가져오기 | Microsoft 365, Excel 2024, Excel 2021 |
| FILTER | 조건에 맞는 행만 골라내기 | Microsoft 365, Excel 2024, Excel 2021 |
| UNIQUE | 중복 없이 목록 만들기 | Microsoft 365, Excel 2024, Excel 2021 |
| SORT | 범위를 정렬한 결과 만들기 | Microsoft 365, Excel 2024, Excel 2021 |
| SUMIFS | 여러 조건으로 합계 | Microsoft 365, Excel 2024, 2021, 2019, 2016 |
| TEXTSPLIT | 구분 기호로 글자 나누기 | Microsoft 365, Excel 2024 |
| IFERROR | 오류 대신 다른 값 보여 주기 | Microsoft 365, Excel 2024, 2021, 2019, 2016 |
XLOOKUP 문서의 '적용 대상' 목록에는 2016과 2019도 보이지만, 같은 문서 메모에 'Excel 2016 및 Excel 2019에서 사용할 수 없습니다'라고 적혀 있어요. 그래서 위 표는 메모를 따랐어요. 회사에서 2019 이하 버전을 쓰는 동료와 파일을 주고받는다면 1~4번 함수는 피하는 편이 안전해요.
예제에 쓰는 주문 표
A1:C7 — 1행은 머리글, 데이터는 2~7행
| 행 | A 담당 | B 지역 | C 금액 |
|---|---|---|---|
| 2 | 김민 | 서울 | 120000 |
| 3 | 이서 | 부산 | 80000 |
| 4 | 김민 | 부산 | 45000 |
| 5 | 박준 | 서울 | 200000 |
| 6 | 이서 | 서울 | 60000 |
| 7 | 박준 | 부산 | 150000 |
1. XLOOKUP — 코드로 상품 정보 찾기
같은 시트 E1:G4에 상품표가 있다고 해 볼게요. E열은 코드(P-101, P-102, P-103), F열은 상품명(무선 마우스, USB 허브, 노트북 거치대), G열은 단가(19000, 25000, 32000)예요. 형식은 XLOOKUP(찾을 값, 찾을 범위, 가져올 범위, 못 찾았을 때 값)이에요.
| 수식 | 결과 |
|---|---|
| =XLOOKUP("P-102", E2:E4, G2:G4, "없음") | 25000 |
| =XLOOKUP("P-103", E2:E4, F2:F4, "없음") | 노트북 거치대 |
| =XLOOKUP("P-109", E2:E4, G2:G4, "없음") | 없음 |
네 번째 값을 빼면 못 찾았을 때 #N/A가 나와요. 기본은 정확히 일치하는 값만 찾고, 다섯 번째 인수 match_mode에 2를 넣으면 *, ? 와일드카드 검색이 돼요.
2. FILTER — 조건에 맞는 행만 골라내기
| 수식 | 결과(흘러넘쳐 채워짐) |
|---|---|
| =FILTER(A2:C7, (B2:B7="서울")*(C2:C7>=100000), "없음") | 김민 | 서울 | 120000 박준 | 서울 | 200000 |
| =FILTER(A2:C7, (B2:B7="부산")+(C2:C7>=150000), "없음") | 이서 | 부산 | 80000 김민 | 부산 | 45000 박준 | 서울 | 200000 박준 | 부산 | 150000 |
| =FILTER(A2:C7, B2:B7="대구", "없음") | 없음 |
조건을 * 로 곱하면 '둘 다 맞는 행(AND)', + 로 더하면 '하나라도 맞는 행(OR)'이에요. 두 번째 수식에서 박준·부산·150000 행은 두 조건을 모두 만족하지만 한 번만 나와요. 맞는 행이 하나도 없을 때 세 번째 인수가 없으면 #CALC! 오류가 나니, "없음"처럼 보여 줄 말을 넣어 두세요.
3. UNIQUE — 중복 없는 담당자 목록
| 수식 | 결과 |
|---|---|
| =UNIQUE(A2:A7) | 김민 이서 박준 |
| =UNIQUE(B2:B7) | 서울 부산 |
결과는 처음 나온 순서대로 나와요. 세 번째 인수 exactly_once에 TRUE를 넣으면 '딱 한 번만 나온 값'만 남는데, 이 표처럼 모두 두 번 이상 나오면 남는 값이 없어요.
4. SORT — 금액 큰 순서로 정렬한 사본
| 수식 | 결과 |
|---|---|
| =SORT(A2:C7, 3, -1) | 박준 | 서울 | 200000 박준 | 부산 | 150000 김민 | 서울 | 120000 이서 | 부산 | 80000 이서 | 서울 | 60000 김민 | 부산 | 45000 |
| =SORT(UNIQUE(A2:A7)) | 김민 박준 이서 |
두 번째 인수는 기준 열 번호(3 = 금액), 세 번째는 1이 오름차순(기본값), -1이 내림차순이에요. 원본 표는 그대로 두고 정렬된 결과만 따로 만들어 주니, 보고용 표를 매번 수동 정렬하던 일을 없앨 수 있어요.
5. SUMIFS — 조건 여러 개로 합계
| 수식 | 결과 |
|---|---|
| =SUMIFS(C2:C7, A2:A7, "김민") | 165000 |
| =SUMIFS(C2:C7, A2:A7, "박준") | 350000 |
| =SUMIFS(C2:C7, B2:B7, "서울", C2:C7, ">=100000") | 320000 |
합할 범위(C2:C7)가 맨 앞이에요. SUMIF는 합할 범위가 세 번째라서 둘을 섞어 쓰다 자주 틀려요. 조건 범위는 합할 범위와 행·열 수가 같아야 하고, 글자 조건과 ">=100000" 같은 비교 조건은 큰따옴표로 감싸요. "김*"처럼 와일드카드도 돼요.
6. TEXTSPLIT — 파일명·주소를 칸별로 나누기
| A10 칸 값 | 수식 | 결과(옆으로 채워짐) |
|---|---|---|
| 2026-10-23_가나상사_견적서_v2 | =TEXTSPLIT(A10, "_") | 2026-10-23 | 가나상사 | 견적서 | v2 |
| 서울시,마포구,합정동 | =TEXTSPLIT(A10, ",") | 서울시 | 마포구 | 합정동 |
두 번째 인수는 열로 나눌 기호, 세 번째는 행으로 나눌 기호예요. 기호가 여러 개면 {",","_"}처럼 배열로 넣으면 되고, 네 번째 인수 ignore_empty를 TRUE로 두면 '쉼표 두 개 연속'처럼 생긴 빈칸을 건너뛰어요. 버전 표에서 봤듯 공식 문서의 적용 대상에 Excel 2021은 없어요.
7. IFERROR — 오류 대신 깔끔한 표시
| B 실적 | C 목표 | 수식 | 결과 |
|---|---|---|---|
| 90 | 120 | =IFERROR(B2/C2, "-") | 0.75(백분율 서식이면 75%) |
| 150 | 120 | =IFERROR(B3/C3, "-") | 1.25(백분율 서식이면 125%) |
| 30 | 0 | =IFERROR(B4/C4, "-") | - |
IFERROR는 #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!을 모두 같은 값으로 바꿔요. 함수 이름 오타(#NAME?)나 지워진 참조(#REF!)까지 '-'로 덮이니, 0으로 나누기처럼 예상되는 오류가 있는 칸에만 쓰세요. 찾기 실패만 처리하고 싶다면 IFERROR로 감싸기보다 XLOOKUP의 네 번째 인수를 쓰는 편이 정확해요.
#SPILL! 오류가 났다면
2~4번과 6번 함수는 결과가 여러 칸에 걸쳐 채워져요. Microsoft 문서가 꼽는 대표 원인은 이래요. 결과가 들어갈 칸에 이미 뭔가 있거나, 병합된 셀과 겹치거나, 수식을 엑셀 '표' 안에 넣은 경우예요. 표 안에서는 흘러넘치는 수식을 지원하지 않으니 수식을 표 바깥으로 옮기거나 표를 일반 범위로 바꿔야 해요. A:A처럼 열 전체를 넣어 결과가 시트 끝을 넘는 경우도 있으니, A2:A7처럼 필요한 범위만 지정하세요.
오류 칸을 선택하면 나오는 오류 확인 메뉴에서 '장애 셀 선택'(영문판 Select Obstructing Cells)을 누르면, 결과를 막고 있는 칸을 바로 찾을 수 있어요.
반복 작업을 함수로 바꾸기 전 확인
- 함께 쓰는 사람의 엑셀 버전이 Microsoft 365 또는 2021 이상인가요? TEXTSPLIT은 2024 이상인가요?
- 머리글은 1행 한 줄로, 데이터 중간에 빈 행이나 병합 셀이 없나요?
- 같은 담당자·거래처 이름이 띄어쓰기까지 똑같이 적혀 있나요? '김민'과 '김 민'은 다른 값이에요.
- 흘러넘치는 함수의 결과 자리 아래·오른쪽이 비어 있나요?
- IFERROR로 감싼 칸이 진짜 오류까지 숨기고 있지 않은지 한 번은 풀어서 확인했나요?
담당자별 합계 =SUMIFS(C2:C7, A2:A7, "김민")
서울 10만 원 이상 =FILTER(A2:C7, (B2:B7="서울")*(C2:C7>=100000), "없음")
담당자 목록(가나다) =SORT(UNIQUE(A2:A7))
금액 큰 순 사본 =SORT(A2:C7, 3, -1)
코드로 단가 찾기 =XLOOKUP("P-102", E2:E4, G2:G4, "없음")
파일명 나누기 =TEXTSPLIT(A10, "_")
달성률(0 나누기 방지) =IFERROR(B2/C2, "-")한계와 주의할 점
- 버전 정보는 2026-10-23에 Microsoft 지원 문서의 '적용 대상'과 본문 메모를 기준으로 정리했어요. 한국어판 문서와 영문판 문서의 메뉴 이름이 다르게 번역된 곳도 있어요.
- 예제 결과는 위 주문 표 값으로 계산해 맞춘 값이에요. 실제 파일에서는 숫자가 글자 형식으로 저장돼 있거나 앞뒤 공백이 있으면 SUMIFS·FILTER 결과가 달라질 수 있어요.
- SORT의 가나다 정렬 결과는 한국어 정렬 기준을 전제로 했어요. 영문·숫자가 섞인 이름은 순서가 다르게 보일 수 있어요.
확인한 자료
- Microsoft 지원 — XLOOKUP 함수 · 2026. 10. 23. 확인
- Microsoft Support — XLOOKUP function · 2026. 10. 23. 확인
- Microsoft Support — FILTER function · 2026. 10. 23. 확인
- Microsoft Support — UNIQUE function · 2026. 10. 23. 확인
- Microsoft Support — SORT function · 2026. 10. 23. 확인
- Microsoft Support — SUMIFS function · 2026. 10. 23. 확인
- Microsoft Support — TEXTSPLIT function · 2026. 10. 23. 확인
- Microsoft Support — IFERROR function · 2026. 10. 23. 확인
- Microsoft 지원 — #SPILL! 오류를 수정하는 방법 · 2026. 10. 23. 확인
- Microsoft Support — How to correct a #SPILL! error · 2026. 10. 23. 확인