엑셀 INDIRECT 함수 활용: 이중 유효성 검사 드롭다운 완벽 가이드

“팀장님이 대분류를 선택하면 그 옆에 소분류가 자동으로 바뀌게 만들어오라는데, 이거 어떻게 하죠?”

엑셀을 다루다 보면 누구나 한 번쯤 마주치는 난관이 있어요. 바로 종속된 드롭다운 목록을 만드는 일이죠.

예를 들어, ‘과일’을 선택하면 옆 칸에는 ‘사과, 배, 포도’만 나오고, ‘채소’를 선택하면 ‘당근, 양파’만 나오게 하는 기능 말이에요.

이거 하나만 할 줄 알면 “오, 엑셀 좀 하는데?”라는 소리 듣는 건 시간문제예요.

하지만 막상 하려고 보면 데이터 유효성 검사까지는 알겠는데, 두 목록을 연결하는 방법에서 막히곤 하죠.

인터넷을 뒤져봐도 설명이 너무 어렵거나, 옛날 버전 기준이라 헷갈리셨나요?

걱정 마세요. 오늘 제가 알려드리는 INDIRECT 함수 활용법만 따라 하시면, 5분 만에 자동화된 엑셀 서식을 완성할 수 있어요.

1. INDIRECT 함수, 도대체 뭔가요?

본격적인 작업에 앞서 원리부터 아주 간단히 짚고 넘어갈게요.

무작정 따라 하는 것보다 원리를 알면 응용력이 생기니까요.

INDIRECT 함수는 쉽게 말해 ‘주소 청부업자’예요.

우리가 셀에 “A1″이라고 텍스트로 적어두면, 엑셀은 그걸 그냥 글자 “A1″으로만 인식해요.

하지만 INDIRECT 함수 안에 “A1″을 넣으면, 엑셀은 그제야 “아! 텍스트가 아니라 실제 주소 A1 셀을 찾아가라는 거구나!”라고 이해하고 그 셀의 값을 가져오죠.

💡 핵심 개념
=INDIRECT(“텍스트”) → 텍스트가 가리키는 실제 셀의 값이나 범위를 반환함.

이 원리를 이용해 대분류에서 선택한 텍스트(예: 과일)를 이름으로 가진 범위를 불러오게 만드는 것이 오늘 작업의 핵심이에요.

자, 이제 이론은 끝났으니 실전으로 들어가 볼까요?

2. 기초 공사: 데이터 목록 만들기

가장 먼저 해야 할 일은 데이터를 예쁘게 정리하는 거예요.

이 단계가 꼬이면 뒤에 아무리 함수를 잘 써도 작동하지 않아요.

아래 표처럼 가로형으로 데이터를 정리하는 게 가장 직관적이고 편해요.

주의할 점!
맨 윗줄(헤더)에 있는 단어들이 나중에 첫 번째 드롭다운 목록이 될 거예요.

그리고 그 아래 있는 항목들이 두 번째 드롭다운 목록이 되죠.

이 구조를 머릿속에 꼭 기억해 두세요.

3. 마법의 열쇠: 이름 관리자 설정

이제 엑셀에게 “이 묶음의 이름은 ‘전자제품’이야”라고 알려줘야 해요.

일일이 범위를 지정할 수도 있지만, 우리는 스마트하게 단축키를 써볼게요.

이렇게 하면 맨 윗줄의 이름(전자제품, 가구, 식품)으로 그 아래 데이터 범위가 자동으로 이름 정의가 돼요.

이 작업이 제대로 됐는지 확인하고 싶다면 [Ctrl] + [F3]을 눌러 이름 관리자를 열어보세요.

깔끔하게 정리되어 있다면 성공입니다!

4. 1단계 유효성 검사 (대분류 만들기)

이제 본격적으로 선택 상자를 만들어볼까요?

먼저 대분류(상위 카테고리)를 선택할 셀을 지정해요.

1. 드롭다운을 넣을 셀을 클릭합니다.
2. 상단 메뉴 [데이터] 탭 → [데이터 유효성 검사] 클릭.
3. ‘제한 대상’을 [목록]으로 변경합니다.
4. ‘원본’ 칸에 대분류 제목들을 직접 드래그하거나 입력합니다.
   (예: =$A$1:$C$1 또는 전자제품,가구,식품)

여기까지는 평소에 하던 것과 똑같죠?

진짜 마법은 다음 단계에서 일어납니다.

5. 2단계 유효성 검사 (INDIRECT 연결)

대망의 하이라이트입니다.

대분류 옆 칸, 즉 소분류가 나올 셀을 선택해주세요.

그리고 다시 [데이터 유효성 검사] 창을 엽니다.

1. ‘제한 대상’은 똑같이 [목록]으로 설정하세요.

2. ‘원본’ 칸에 아래 수식을 입력합니다.

=INDIRECT(대분류가_있는_셀_주소)

예를 들어 대분류 셀이 E5라면, =INDIRECT(E5)라고 입력하면 끝이에요!

이제 확인 버튼을 누르면 오류 메시지가 뜰 수도 있어요.

“원본이 현재 계산 결과로 오류를 반환합니다”라는 메시지인데, 무시하고 [예]를 누르시면 됩니다.

(아직 대분류 셀이 비어있어서 생기는 자연스러운 현상이에요.)

6. 자주 겪는 오류 해결법 (FAQ)

따라 했는데 잘 안 되시나요?

90%는 이 문제 때문일 거예요.

7. 실무 활용 팁: 어디에 써먹을까요?

이 기능, 생각보다 쓸 곳이 정말 많아요.

제가 실제로 업무에서 활용했던 사례들을 공유해 드릴게요.

• ✅ 지역 선택: ‘서울특별시’ 선택 시 → ‘강남구, 서초구…’ 노출
• ✅ 부서/팀원 관리: ‘인사팀’ 선택 시 → ‘김철수, 이영희…’ 노출
• ✅ 제품 견적서: ‘모델명’ 선택 시 → 해당 모델의 ‘옵션 리스트’ 노출

특히 견적서나 발주서를 만들 때 오타를 방지하는 데 이만한 기능이 없답니다.

동료들이 입력 실수를 줄여줘서 퇴근 시간이 빨라지는 건 덤이고요.

마치며: 엑셀 자동화의 첫걸음

오늘은 INDIRECT 함수를 활용한 이중 유효성 검사 드롭다운 만드는 법을 알아봤어요.

처음엔 이름 정의하고 수식 넣는 게 복잡해 보일 수 있어요.

하지만 딱 한 번만 직접 만들어보면 “아, 이래서 쓰는구나!” 하고 무릎을 치실 거예요.

이 기능 하나로 여러분의 엑셀 파일은 훨씬 더 전문적이고 사용하기 편리한 도구가 될 것입니다.

지금 바로 빈 시트를 열어서 작은 목록부터 연습해 보세요.

혹시 중간에 막히는 부분이 있다면 언제든 다시 이 글을 확인해 주시고요!

여러분의 칼퇴를 응원합니다!
다음에는 더 유용한 엑셀 꿀팁으로 찾아올게요.