문서·업무 도구가이드

엑셀 반복업무를 줄이는 함수 7개: XLOOKUP·FILTER·UNIQUE 예제로 배우기

주문 표 하나로 XLOOKUP, FILTER, UNIQUE, SORT, SUMIFS, TEXTSPLIT, IFERROR를 차례로 써 보는 예제를 수식과 정확한 결과값까지 정리했어요. 함수마다 쓸 수 있는 엑셀 버전은 Microsoft 공식 문서 기준으로 표시했어요.

8분 읽기2026. 10. 23.

이 글에서 얻는 것

  • 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 목표수식결과
90120=IFERROR(B2/C2, "-")0.75(백분율 서식이면 75%)
150120=IFERROR(B3/C3, "-")1.25(백분율 서식이면 125%)
300=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처럼 필요한 범위만 지정하세요.

Tip

오류 칸을 선택하면 나오는 오류 확인 메뉴에서 '장애 셀 선택'(영문판 Select Obstructing Cells)을 누르면, 결과를 막고 있는 칸을 바로 찾을 수 있어요.

반복 작업을 함수로 바꾸기 전 확인

  • 함께 쓰는 사람의 엑셀 버전이 Microsoft 365 또는 2021 이상인가요? TEXTSPLIT은 2024 이상인가요?
  • 머리글은 1행 한 줄로, 데이터 중간에 빈 행이나 병합 셀이 없나요?
  • 같은 담당자·거래처 이름이 띄어쓰기까지 똑같이 적혀 있나요? '김민'과 '김 민'은 다른 값이에요.
  • 흘러넘치는 함수의 결과 자리 아래·오른쪽이 비어 있나요?
  • IFERROR로 감싼 칸이 진짜 오류까지 숨기고 있지 않은지 한 번은 풀어서 확인했나요?
복사해 쓰는 수식 모음(주문 표 A1:C7 기준)
담당자별 합계      =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의 가나다 정렬 결과는 한국어 정렬 기준을 전제로 했어요. 영문·숫자가 섞인 이름은 순서가 다르게 보일 수 있어요.

확인한 자료