최초 작성일: 2013-11-04
최종 수정일: 2026-09-21
조회수: 38 회
작성자: 권현욱 (엑셀러)
강의 제목: 이중 조건으로 검색하기

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

이런 질문을 주신 분이 있군요.

…(중략)…
엑셀의 한 셀에는
1, 2, 3, 4, 5와 같이 반을 선택할 수 있는 드롭다운을 만들고
그 옆에는 1반을 선택하면 1-1번, 1-2번, 1-3번을 선택할 수 있도록
드롭다운을 만들려면 어떻게 만들어야 하나요?

강좌에서 이중 유효성검사와 관련된 것을 찾아봤지만
엑셀 초보라 무슨 말인지 잘 모르겠습니다.
최대한 자세하게 설명 부탁드려요~

위 표(성명 목록)와 같은 데이터가 있습니다.

반	번호	성명
1	1	김정민
1	2	이종호
1	3	권준우
1	4	고미숙
1	5	김종래
1	6	김인수
1	7	이지윤
1	8	노태준
1	9	김정주
1	10	안진환
2	1	이태원
2	2	안명언
2	3	유홍준
2	4	신승미
2	5	유영만
2	6	서대원
2	7	손동원
2	8	박영춘
2	9	황규진
3	1	전중환
3	2	최인철
3	3	권현욱
3	4	김태희
3	5	권예지
3	6	신영철
3	7	윤지산
3	8	조세현
3	9	윤광원
3	10	신민아
3	11	이경락
3	12	강승영
4	1	박병진
4	2	민경호
4	3	박세일
4	4	홍수원
4	5	남경준
4	6	홍성균
4	7	이보영
4	8	김상근
4	9	채심연
4	10	김판서
5	1	홍참판
5	2	송강
5	3	노준의
5	4	오용
5	5	공손승
5	6	관승
5	7	박원길
5	8	박정서
5	9	서문경
5	10	김무송
5	11	이병아
5	12	방춘매
6	1	양산백
6	2	축영대
6	3	백소정
6	4	허선
6	5	임충
6	6	진명
6	7	이순신
6	8	임꺽정
6	9	양산박
7	1	염상진
7	2	전승환
7	3	한창갑
7	4	조준호
7	5	류시현
7	6	이근호
7	7	박정숙
7	8	권재롱
7	9	김태훈
7	10	홍성태
7	11	최태근
7	12	신세경
7	13	안효주

이와 같은 것을 "이중 유효성 검사"라고 불러도 좋을지 모르겠으나
일단 아래의 테이블에서 "반"과 "번호"를 선택해 보시기 바랍니다.

반	번호	이름
1	3	권준우

반을 선택하면 그 반의 인원수에 해당하는 숫자만큼 "번호"란에 표시되고
번호를 선택하면 해당 반/번호의 학생 이름이 표시됩니다.

우리가 아주 잘 알고 있는 "유효성 검사"와 "이름 정의"를 이용하면
어렵지 않게 해결할 수 있습니다.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

엑셀 이중 조건으로 검색하기, 반과 번호로 이름 찾기

Q: 엑셀에서 반과 번호를 선택하면 이름이 나오게 하려면 어떻게 하나요?

INDEX와 MATCH 함수를 배열 수식으로 조합합니다. =INDEX(C20:C94,MATCH(1,(A20:A94=E25)*(B20:B94=F25),0))처럼 쓰면 반(E25)과 번호(F25)가 모두 일치하는 행의 성명을 가져옵니다. 배열 수식이므로 Ctrl + Shift + Enter 키를 함께 눌러야 합니다.

Q: 선택한 반의 인원수만큼만 번호 목록이 나오게 하려면 어떻게 하나요?

COUNTIF 함수로 반의 인원수를 구하고, OFFSET 함수로 인원수만큼의 범위를 가리키는 동적 이름 인원수를 정의한 뒤, 번호 셀의 데이터 유효성 검사 '목록'의 원본에 =인원수를 입력합니다.

Q: 두 조건이 모두 맞는 행을 찾는 MATCH 수식은 어떻게 동작하나요?

(A20:A94=E25)*(B20:B94=F25)는 반과 번호가 모두 일치하는 행에서만 1이고 나머지는 0인 배열을 만듭니다. MATCH(1, 그 배열, 0)이 1이 처음 나오는 위치를 찾고, INDEX가 같은 위치의 성명을 돌려줍니다.

핵심 요약: 반과 번호 두 조건으로 이름 검색하기

동적 이름 정의와 데이터 유효성 검사로 반에 따라 번호 목록의 길이가 바뀌게 하고, INDEX·MATCH 배열 수식으로 두 조건에 맞는 학생의 이름을 불러옵니다.

인원수 구하기: =COUNTIF(Preface!A20:A94,반)로 선택한 반의 인원수를 구합니다.
동적 이름 인원수: =OFFSET(Workplace!$B$1,0,0,Workplace!$C$1,1)로 1부터 인원수까지의 범위를 가리키게 합니다.
유효성 검사: 번호 셀에 '제한 대상 - 목록', 원본 =인원수를 지정합니다.
이름 불러오기: =INDEX(C20:C94,MATCH(1,(A20:A94=E25)*(B20:B94=F25),0))를 배열 수식으로 입력합니다.

1. 반 인원수 기본 데이터 준비

(1) 먼저, 반 인원 수를 표시하기 위한 기본 데이터를 준비합니다.
인원 수가 반마다 가변적이므로 숫자를 좀 넉넉하게 주었습니다.
(Workplace 시트의 B열 숫자 참고)

참고: 예제 파일의 Workplace 시트

예제 파일의 Workplace 시트 B열(B1:B20)에는 1부터 20까지의 숫자가, C1 셀에는 선택한 반의 인원수를 구하는 수식이 들어 있습니다. A열(A1:A7)에는 1~7이 입력되어 있어 반 목록에 쓸 수 있습니다.

2. 반의 인원수 구하기

(2) 반의 인원이 몇 명인지를 알아내기 위한 수식을 작성합니다.
COUNTIF 함수를 이용하면 되겠지요?(Workplace 시트의 C1 셀 참고).

=COUNTIF(Preface!A20:A94,반)

예를 들어, "3반"을 선택하였다면
Workplace 시트의 C1 셀에 입력된 위 수식의 결과 값인 "12"가 표시됩니다.

여기서 "반"이라는 것은 "반"이 표시되는 E25 셀에 사전에 이름을 정의해 둔 것입니다.
그렇게 한 이유는?
작업 범위가 달라지면 수식도 바꿔주어야 하는데 이렇게 이름을 정의해 두고
수식에서 사용하면 그런 수고를 막을 수 있기 때문이지요.

정정: 함수 이름

원문에서는 인원수를 세는 함수를 Counta라고 했지만, 실제 수식은 조건에 맞는 셀을 세는 COUNTIF 함수(=COUNTIF(Preface!A20:A94,반))를 사용합니다.

3. 이름 "인원수" 정의하기

(3) "수식" 탭의 "이름 관리자" 아이콘을 클릭하고
"새로 만들기" 버튼을 누른 다음 "인원수"라는 이름을 정의합니다

=OFFSET(Workplace!$B$1,0,0,Workplace!$C$1,1)

많이 눈에 익은 수식이지요?
Offset 함수를 이용하여 동적 이름 정의(Dynamic Naming)를 한 것입니다.

이름 관리자 대화상자. 인원수 이름이 OFFSET(Workplace!...,0,0,Workplace!$C$1,1)로 정의된 화면

아이엑셀러

참고: 화면의 참조 대상

위 화면의 참조 대상은 Workplace!$A$1로 보이지만, 이 예제 파일에서는 1~20이 들어 있는 B열을 기준으로 하는 =OFFSET(Workplace!$B$1,0,0,Workplace!$C$1,1)로 정의되어 있습니다. 위 코드 박스가 예제 파일의 실제 수식입니다.

4. 번호 셀에 유효성 검사 적용하기

(4) 앞의 (3) 단계에서 정의한 이름을 활용할 차례입니다.
"데이터 - 데이터 유효성 검사"를 선택합니다.
"제한 대상 - 목록"을 선택하고 "원본" 항목에는 "=인원수"라고 입력하고 "확인"을 누릅니다.

데이터 유효성 대화상자. 제한 대상은 '목록', 원본은 =인원수이며 원본 항목 부분이 빨간 점선 타원으로 표시됨

아이엑셀러

참고: 유효성 검사를 지정한 셀

예제 파일에서 위 유효성 검사(목록, 원본 =인원수)는 번호 셀(F25)에 지정되어 있습니다. 반 셀(E25)에도 같은 방법으로 목록(예: Workplace 시트 A1:A7)을 지정하면 반을 목록에서 고를 수 있습니다.

5. 반/번호에 해당하는 사람 불러오기

(5) 지정한 반/번호에 해당하는 사람을 불러오기 위한 수식을 작성합니다.

{=INDEX(C20:C94,MATCH(1,(A20:A94=E25)*(B20:B94=F25),0))}

배열 수식이므로 수식 앞과 뒤의 중괄호는 손으로 입력하는 것이 아니라
Ctrl + Shift + Enter 키를 함께 누르면 자동으로 생긴다는 것을 잊지 마시고…

정정: 수식의 범위

원문 수식은 범위를 C19:C93, A19:A93, B19:B93로 적어 데이터의 마지막 행(94행, 7반 13번 안효주)이 빠지고 머리글(19행)이 포함되어 있었습니다. 예제 파일의 실제 수식(G25)과 앞의 COUNTIF 범위(A20:A94)에 맞춰 20:94 행으로 바로잡았습니다.

참고: 최신 Excel에서는

Excel 365와 Excel 2021 이후 버전에서는 Ctrl + Shift + Enter 없이 Enter만 눌러도 이 수식이 계산됩니다. 같은 결과를 =XLOOKUP(1,(A20:A94=E25)*(B20:B94=F25),C20:C94)로도 구할 수 있습니다.

정리 — 이중 조건 검색 순서

순서	하는 일
(1)	Workplace 시트 B열에 1~20의 숫자를 넉넉하게 준비
(2)	`=COUNTIF(Preface!A20:A94,반)`로 반의 인원수를 구함(C1 셀)
(3)	이름 `인원수`를 `=OFFSET(Workplace!$B$1,0,0,Workplace!$C$1,1)`로 정의
(4)	번호 셀에 데이터 유효성 검사(목록, 원본 `=인원수`) 적용
(5)	INDEX·MATCH 배열 수식으로 반/번호에 해당하는 이름을 불러옴

자주 묻는 질문 (FAQ)

Q1. 엑셀에서 반과 번호를 선택하면 이름이 나오게 하려면 어떻게 하나요?

INDEX와 MATCH 함수를 배열 수식으로 조합합니다. =INDEX(C20:C94,MATCH(1,(A20:A94=E25)*(B20:B94=F25),0))처럼 쓰면 반(E25)과 번호(F25)가 모두 일치하는 행의 성명을 가져옵니다. 배열 수식이므로 Ctrl + Shift + Enter 키를 함께 눌러야 합니다.