엑셀 조건에 맞는 행만 뽑아 목록 만들기

고객 명단이 수십 줄이 넘으면 "이번 주에는 실손 고객에게만 안내 문자를 보내자"처럼 일부만 따로 모아야 할 때가 생깁니다. 자동 필터로 걸러서 복사해 붙여도 되지만, 명단이 바뀔 때마다 다시 해야 합니다. 수식으로 뽑아 두면 원본에 한 줄이 늘어도, 조건 칸을 "건강"으로 바꿔도 목록이 알아서 다시 채워집니다.

엑셀 시험을 준비하는 분이라면 여러 함수를 겹쳐 쓰는 연습 문제로도 좋고, 수식 없이 같은 일을 하는 고급 필터도 함께 알아 두면 도움이 됩니다. 이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편입니다.

예제 표: 고객 명단과 조건 칸

A~C열에 명단이 있고, F1 셀에 찾을 상품을 적습니다. 뽑은 목록은 E3 머리글 아래 E4부터 채웁니다. 이름과 번호는 모두 가상입니다. 맨 윗줄은 열 문자, 맨 왼쪽은 행 번호입니다.

	A	B	C	D	E	F	G
1	이름	상품	연락처		찾을 상품	실손
2	김민준	실손	010-0000-2101
3	이서연	건강	010-0000-2102		이름	상품	연락처
4	박지훈	실손	010-0000-2103
5	최수아	암	010-0000-2104
6	정도윤	실손	010-0000-2105
7	강하은	치아	010-0000-2106
8	조예준	건강	010-0000-2107
9	윤지우	실손	010-0000-2108

완성 수식과 결과

쓰는 엑셀 버전에 따라 둘 중 하나를 고릅니다. 버전을 모르겠다면 방법 A를 먼저 넣어 보고, #NAME?이 나오면 방법 B를 쓰면 됩니다.

방법 A — 엑셀 2021 · 2024 · Microsoft 365 (FILTER)

1. E4 셀 하나만 클릭하고 아래 수식을 입력한 뒤 Enter를 누릅니다.
2. 끝입니다. 결과가 E4:G7로 저절로 넘쳐 채워집니다. 이렇게 옆 칸까지 퍼지는 것을 "분산(spill)"이라고 합니다.

=FILTER(A2:C9, B2:B9=F1, "없음")

방법 B — 모든 버전 (INDEX + SMALL + IF)

1. E4 셀을 클릭하고 아래 수식을 입력합니다.
2. 엑셀 2021 이후·365라면 Enter, 엑셀 2019 이하라면 Ctrl + Shift + Enter를 누릅니다. 이전 버전은 수식 앞뒤에 중괄호 { }가 자동으로 붙어야 제대로 들어간 것입니다.
3. E4의 채우기 핸들(셀 오른쪽 아래 작은 네모)을 G4까지 오른쪽으로 끈 뒤, E4:G4를 선택한 채 9행까지 끌어내립니다.

=IFERROR(INDEX($A$2:$C$9, SMALL(IF($B$2:$B$9=$F$1, ROW($B$2:$B$9)-ROW($B$2)+1), ROWS($E$3:E3)), COLUMNS($E$3:E3)), "")

행	E 이름	F 상품	G 연락처
4	김민준	실손	010-0000-2101
5	박지훈	실손	010-0000-2103
6	정도윤	실손	010-0000-2105
7	윤지우	실손	010-0000-2108
8~9	(빈칸)	(빈칸)	(빈칸)

두 방법 모두 결과는 같습니다. F1을 "건강"으로 바꾸면 이서연·조예준 두 줄로 바로 바뀝니다. 방법 B는 미리 채워 둔 칸 수(여기서는 6줄)까지만 뽑으므로, 명단이 길면 넉넉하게 끌어내려 두세요.

수식 풀어 보기: 안쪽부터 한 겹씩

방법 B의 E5 칸(두 번째 줄, 첫 번째 열)을 따라가 보겠습니다.

1. ROW($B$2:$B$9)-ROW($B$2)+1 — 시트의 행 번호 2~9를 명단 안의 순서 1~8로 바꿉니다. 중간 결과는 {1, 2, 3, 4, 5, 6, 7, 8}입니다.
2. IF($B$2:$B$9=$F$1, 위의 순서) — 상품이 "실손"인 줄만 순서를 남기고 나머지는 FALSE로 만듭니다. 중간 결과는 {1, FALSE, 3, FALSE, 5, FALSE, FALSE, 8}입니다.
3. ROWS($E$3:E4) — E5 칸에서는 이 부분이 E3:E4, 즉 2줄이므로 2입니다. 아래로 끌어내릴 때마다 1, 2, 3, 4…로 늘어나는 번호표 역할을 합니다.
4. SMALL(IF 결과, 2) — SMALL은 FALSE를 건너뛰고 숫자 중 2번째로 작은 값을 고릅니다. 결과는 3(명단의 3번째 줄)입니다.
5. INDEX($A$2:$C$9, 3, COLUMNS(…)) — COLUMNS 부분은 오른쪽으로 끌 때 늘어나는 번호표로, E열에서 1, F열에서 2, G열에서 3이 됩니다. E5에서는 명단의 3번째 줄 1번째 열인 박지훈을 꺼냅니다.
6. 바깥 IFERROR(…, "") — 실손 고객은 4명뿐이라 5번째 칸(E8)에서는 SMALL이 #NUM!을 냅니다. Microsoft 문서대로 k가 데이터 개수보다 크면 나는 오류입니다. IFERROR가 이것을 빈칸으로 바꿉니다.

방법 A의 FILTER는 이 과정을 함수 하나가 합니다. 두 번째 인수 B2:B9=F1이 줄마다 TRUE·FALSE를 만들고, TRUE인 줄만 A2:C9에서 골라 냅니다. 세 번째 인수 "없음"은 맞는 줄이 하나도 없을 때 보여 줄 값입니다.

자주 막히는 곳

• 맞는 줄이 없는데 #CALC!가 뜰 때 — FILTER의 세 번째 인수를 뺀 경우입니다. Microsoft 문서 기준으로 결과가 비면 #CALC!가 나옵니다. "없음"이나 ""를 세 번째 인수로 넣으세요.
• 방법 B의 결과가 비거나 엉뚱할 때 — 엑셀 2019 이하라면 Enter만 누르고 확정했을 가능성이 큽니다. E4를 다시 클릭하고 수식 입력줄을 누른 뒤 Ctrl + Shift + Enter로 확정하고, 다시 복사해 채우세요. Microsoft 문서는 중괄호를 손으로 치면 수식이 글자로 바뀌어 동작하지 않는다고 설명합니다.
• 달러 기호를 빼먹었을 때 — $A$2:$C$9, $F$1처럼 고정해야 할 곳이 끌어내리면서 밀리면 목록이 어긋납니다. 반대로 ROWS($E$3:E3)의 뒤쪽 E3은 일부러 고정하지 않아야 번호가 늘어납니다.

다른 방법: 보조 열, 여러 조건, 고급 필터

보조 열로 번호 매기기 — 배열 수식이 부담스러울 때. D열에 "몇 번째 실손 고객인지" 번호를 먼저 붙이면, 나머지는 평범한 INDEX·MATCH라 Ctrl + Shift + Enter가 필요 없습니다.

1. D2에 =IF(B2=$F$1, COUNTIF($B$2:B2, $F$1), "")를 넣고 D9까지 끌어내립니다. D열은 1, 빈칸, 2, 빈칸, 3, 빈칸, 빈칸, 4가 됩니다.
2. E4에 아래 수식을 넣고 방법 B와 같이 G4까지, 다시 9행까지 채웁니다.

=IFERROR(INDEX($A$2:$C$9, MATCH(ROWS($E$3:E3), $D$2:$D$9, 0), COLUMNS($E$3:E3)), "")

MATCH가 D열에서 1, 2, 3…을 차례로 찾아 그 줄을 INDEX로 꺼내는 구조입니다. 결과는 방법 A·B와 같습니다. COUNTIF의 범위 $B$2:B2는 앞만 고정해서, 아래로 내려갈수록 "지금까지 나온 실손 개수"를 세게 만든 것입니다.

조건이 두 개일 때(FILTER) — Microsoft 문서에 따르면 "그리고"는 곱하기(*), "또는"은 더하기(+)로 묶습니다. 실손 또는 암 고객의 이름만 뽑으려면 아래처럼 씁니다. 예제에서는 김민준·박지훈·최수아·정도윤·윤지우 5명이 나옵니다.

=FILTER(A2:A9, (B2:B9="실손")+(B2:B9="암"), "없음")

수식 없이 — 고급 필터. 데이터 탭의 정렬 및 필터 그룹에 있는 고급을 누르고 "다른 위치로 복사"를 고르면, 조건에 맞는 행을 다른 자리에 복사해 줍니다. 조건 범위에는 명단과 같은 열 머리글(예: "상품")을 적고 그 아래에 조건을 적어야 합니다. 한 번만 뽑으면 되는 일이라면 이쪽이 간단합니다. 다만 결과가 수식이 아니라 복사본이라, 원본이 바뀌면 다시 실행해야 합니다.

자주 묻는 질문

함께 보면 좋은 글

• 엑셀 INDEX MATCH — INDEX로 줄·열 번호를 받아 값을 꺼내는 기본
• 엑셀 MAX · MIN · LARGE — k번째로 큰 값·작은 값 고르기
• 엑셀 자동 필터 — 수식 없이 화면에서 거르기
• 엑셀 글 전체 목차 — 이 시리즈의 다른 조합 글도 여기서 찾을 수 있습니다

참고

• Microsoft 지원 — FILTER 함수
• Microsoft 지원 — 배열 수식 지침 및 예제
• Microsoft 지원 — #SPILL! 오류를 수정하는 방법