이 글에서는 엑셀에서 드롭다운 목록을 선택해 다른 시트의 데이터를 불러오는 방법을 알아보겠습니다. 하나의 시트에 대량의 데이터가 저장되어 있어도, 실제로 필요한 것은 그중 일부 특정 데이터일 때가 많습니다. 이럴 때 다른 시트로 필요한 데이터만 가져와서 활용하면 작업 효율이 크게 향상됩니다.
여기서는 먼저 드롭다운 목록을 만든 뒤, 다양한 함수를 활용해 다른 시트에서 데이터를 가져오는 4가지 쉬운 방법을 단계별로 소개합니다.
연습 파일 다운로드
아래 연습 파일을 내려받아 직접 따라 해볼 수 있습니다.
엑셀에서 드롭다운 선택 후 다른 시트 데이터 가져오는 4가지 방법
방법 1. VLOOKUP 함수로 다른 시트 데이터 불러오기
첫 번째 방법은 VLOOKUP 함수를 활용하는 것입니다. VLOOKUP 함수는 표의 가장 왼쪽 열에서 특정 값을 찾은 후, 같은 행에서 지정한 열의 값을 반환하는 함수입니다.
데이터셋 소개
이 예제에서는 판매자 3명의 3개월치 매출 데이터가 각각 다른 시트에 나뉘어 저장되어 있습니다.
- 1월 매출 데이터는 Jan 시트에 저장되어 있습니다.
- 2월 매출 데이터는 Feb 시트에 저장되어 있습니다.
- 3월 매출 데이터는 Mar 시트에 저장되어 있습니다.
1단계: 드롭다운 메뉴 만들기
아래 순서대로 드롭다운 메뉴를 생성합니다.
- 먼저 새 시트를 선택하고 데이터셋 구조를 만듭니다. 여기서는 VLOOKUP Function이라는 이름의 시트에 구조를 작성했으며, E열에 월(Month Name)과 시트 이름(Sheet Names)을 입력했습니다.
- 드롭다운을 만들려면 E3 셀을 선택합니다.
- 데이터(Data) 탭으로 이동해 데이터 유효성 검사(Data Validation) 옵션을 클릭하면 데이터 유효성 검사 창이 열립니다.
- 제한 대상(Allow) 필드에서 목록(List)을 선택한 후 원본(Source) 필드를 클릭합니다.
- 드롭다운 메뉴에 추가할 항목을 선택합니다. 여기서는 E8~E10 셀을 선택했고, 확인(OK)을 눌러 진행합니다.
- 확인을 누르면 E3 셀에 드롭다운 메뉴가 생성됩니다. 이 메뉴에서 원하는 시트를 선택할 수 있습니다.
2단계: 다른 시트에서 데이터 가져오기
이제 아래 단계를 따라 다른 시트의 데이터를 불러옵니다.
- 먼저 C5 셀을 선택하고 다음 수식을 입력합니다.
=VLOOKUP($B5,INDIRECT("'"&$E$3&"'!$B$5:$C$11"),2,FALSE)
- Enter 키를 누릅니다.
여기서는 VLOOKUP 함수 안에 INDIRECT 함수를 함께 사용했습니다.
🔎 수식의 작동 원리
① INDIRECT("'"&$E$3&"'!$B$5:$C$11")
INDIRECT 함수는 텍스트 문자열로 지정된 참조를 반환합니다. E3 셀에는 시트 이름이 저장되어 있으므로, 예를 들어 E3에 Jan이 입력되어 있다면 Jan 시트의 B5:C11 범위를 참조하게 됩니다.
② VLOOKUP($B5,INDIRECT(...),2,FALSE)
이 수식은 B5 셀에 저장된 값을 Jan 시트의 표 배열에서 찾습니다. 열 번호는 2이며 정확히 일치하는 값을 찾아야 하므로 마지막 인수에 FALSE를 사용했습니다.
- 마지막으로 채우기 핸들(Fill Handle)을 이용해 나머지 셀에도 결과를 표시합니다.
- 이제 드롭다운 메뉴에서 월 이름을 변경하면 데이터가 자동으로 업데이트됩니다.
- 3월(March)로 변경했을 때도 결과가 자동으로 갱신되는 것을 확인할 수 있습니다.
방법 2. INDIRECT 함수로 다른 시트 데이터 불러오기
두 번째 방법은 INDIRECT 함수를 활용하는 것입니다. 앞서 사용한 동일한 데이터셋을 그대로 사용하겠습니다.
1단계: 드롭다운 메뉴 만들기
- 새 시트를 선택하고 기존 데이터셋과 같은 구조를 만든 뒤, E열에 월 이름과 시트 이름을 입력합니다.
- 데이터(Data) 탭에서 데이터 유효성 검사(Data Validation) 옵션을 선택합니다.
- 제한 대상(Allow) 필드에서 목록(List)을 선택하고 원본(Source) 필드를 클릭합니다.
- 드롭다운에 추가할 항목, 즉 시트 이름이 담긴 E8~E10 셀을 선택하고 확인(OK)을 누릅니다.
- 완료되면 E3 셀에 드롭다운 메뉴가 생성되며, 여기서 원하는 시트를 선택할 수 있습니다.
2단계: 다른 시트에서 데이터 가져오기
드롭다운 메뉴가 준비되었으니, 이제 데이터를 불러오는 방법을 살펴보겠습니다.
- 먼저 C5 셀을 선택하고 다음 수식을 입력합니다.
=INDIRECT("'"&$E$3&"'!C6")
INDIRECT 함수는 텍스트 문자열로 지정된 참조를 반환합니다. E3 셀에 Jan이 저장되어 있다면, Jan 시트의 C6 셀 값을 가져오게 됩니다.
- 수식 입력 후 Enter를 누르고 채우기 핸들을 드래그하면 동일한 수식이 아래 셀들에 복사됩니다.
- C7 셀을 선택해 수식의 C6을 C7로 수정한 후 Enter를 누릅니다.
- 마찬가지로 C8 셀에서는 C6을 C8로 바꾸고 Enter를 누릅니다.
- 나머지 셀도 같은 방식으로 처리하면 아래와 같은 결과를 얻을 수 있습니다.
- 드롭다운 메뉴에서 월 이름을 변경하면 데이터가 자동으로 업데이트됩니다.
- 3월(March)로 변경했을 때의 업데이트된 결과입니다.
방법 3. 데이터 유효성 검사 옵션으로 다른 시트 데이터 추출하기
세 번째 방법은 데이터(Data) 탭의 데이터 유효성 검사 옵션을 활용합니다. 이번에는 새로운 데이터셋을 사용합니다. 제품의 ID, 이름(Name), 가격(Price)이 담긴 Product List 데이터셋에서 다른 시트로 데이터를 추출해 완전한 데이터셋을 만드는 과정입니다.
1단계: 드롭다운 메뉴 삽입하기
- 먼저 데이터셋 구조를 만든 후 B5 셀을 선택합니다.
- 데이터(Data) 탭에서 데이터 유효성 검사 옵션을 선택해 창을 엽니다.
- 제한 대상(Allow) 필드에서 목록(List)을 선택한 후 원본(Source) 필드를 클릭합니다.
- Product List 시트로 이동해 B5 셀부터 제품 ID 범위를 선택하고 확인(OK)을 누릅니다.
- 그러면 B5 셀의 드롭다운 메뉴에서 제품 ID(Product ID)를 선택할 수 있게 됩니다.
- 같은 절차로 C5 셀에도 드롭다운 메뉴를 만듭니다. 이번에는 Product List 시트에서 제품 이름(Product Name) 범위를 선택합니다.
- D5 셀에는 Product List 시트에서 가격(Price) 범위를 선택해 드롭다운 메뉴를 만듭니다.
- 완료하면 5행의 원하는 셀마다 드롭다운 메뉴가 생성된 것을 볼 수 있습니다.
2단계: 다른 시트에서 데이터 추출하기
- 4행과 5행의 원하는 셀들을 선택합니다.
- 삽입(Insert) 탭으로 이동해 테이블(Table)을 선택합니다.
- 테이블 만들기(Create Table) 창이 나타나면, 헤더가 있는 테이블인 경우 테이블에 머리글 포함(My table has headers)에 체크합니다. 없다면 체크를 해제하세요. 확인(OK)을 눌러 진행합니다.
- 확인을 누르면 아래와 같은 결과가 나타납니다.
- 다음으로 D5 셀을 선택합니다.
- D5 셀 선택 상태에서 Tab 키를 누르면 6행의 해당 셀들에 드롭다운 메뉴가 자동으로 추가됩니다. 이 메뉴들을 이용해 각 행의 셀을 채울 수 있습니다.
- 같은 방법으로 모든 행에 드롭다운 메뉴를 삽입하고, 이를 통해 데이터를 추출합니다.
- 마지막으로 D열의 셀 서식(Number Format)을 통화(Currency)로 변경하면 아래처럼 보기 좋게 표현할 수 있습니다.
방법 4. FILTER 함수로 다른 시트 데이터 추출하기
마지막 방법은 FILTER 함수를 활용하는 것입니다. FILTER 함수는 일반적으로 범위나 배열을 필터링하는 데 사용됩니다. 여기서는 제품의 ID, 이름, 수량(Quantity), 가격이 담긴 데이터셋을 사용합니다.
1단계: 데이터셋을 테이블로 변환하기
- 먼저 데이터셋 내 아무 셀이나 선택합니다. 여기서는 B4 셀을 선택했습니다.
- 삽입(Insert) 탭에서 테이블(Table)을 선택합니다.
- 테이블 만들기 대화 상자에서 테이블에 머리글 포함에 체크하고 확인(OK)을 누릅니다.
- 확인을 누르면 데이터셋이 아래와 같이 테이블로 변환됩니다.
- 테이블 디자인(Table Design) 탭에서 테이블 이름(Table Name)을 변경합니다. 여기서는 Product라고 지정했습니다.
2단계: 고유 값 목록 만들기
테이블을 만든 후에는 제품 이름(Product Name) 열에서 고유한 값을 추출해야 합니다.
- 새 시트로 이동해 아무 셀이나 선택합니다. 여기서는 I4 셀을 선택했습니다.
- 다음 수식을 입력합니다.
=UNIQUE(Product[Product Name])
- Enter를 누르면 테이블의 제품 이름 열에서 고유한 값들이 표시됩니다.
여기서 사용한 UNIQUE 함수는 범위나 배열에서 고유한 값만 반환하는 함수로, 이 수식은 Product 테이블의 제품 이름 열에서 중복 없는 값들을 추출합니다.
3단계: 드롭다운 메뉴 추가하기
- 고유 값 목록이 있는 시트로 이동합니다.
- 4행에 Product 테이블의 머리글을 작성합니다.
- 다음으로 G5 셀을 선택합니다.
- 데이터(Data) 탭에서 데이터 유효성 검사 옵션을 선택합니다.
- 제한 대상(Allow) 필드에서 목록(List)을 선택하고 원본(Source) 필드를 클릭합니다.
- 드롭다운에 추가할 항목, 즉 제품 이름이 담긴 I4~I6 셀을 선택하고 확인(OK)을 누릅니다.
- 그러면 G5 셀에 드롭다운 메뉴가 생성됩니다.
4단계: 다른 시트에서 데이터 삽입하기
- B5 셀을 선택하고 다음 수식을 입력합니다.
=FILTER(Product,Product[Product Name]=G5)
첫 번째 인수는 필터링할 Product 테이블 전체를 의미하고, 두 번째 인수는 제품 이름이 G5 셀의 값과 일치해야 한다는 조건을 나타냅니다.
- Enter를 누르면 아래와 같은 결과가 나타납니다.
- 마지막으로 드롭다운 메뉴에서 제품 이름을 변경하면 데이터가 자동으로 업데이트됩니다.
주의 사항
드롭다운 메뉴에서 선택해 다른 시트의 데이터를 가져올 때 다음 사항들을 꼭 기억하세요.
- 방법 1에서는 수식을 입력할 때 큰따옴표(")를 정확하게 입력해야 합니다. 잘못 입력하면 결과가 올바르게 나오지 않습니다. 또한 VLOOKUP 함수의 열 번호(column index number)도 특히 신경 써야 합니다.
- 방법 2에서는 채우기 핸들을 사용해도 수식이 자동으로 갱신되지 않습니다. 이를 피하려면 각 행의 수식을 직접 수정해야 하며, 번거롭다면 방법 1을 사용하는 것이 좋습니다.
- 방법 3은 주로 다른 시트에 새로운 데이터셋을 구축할 때 유용합니다.
- 방법 4는 Excel 365에서만 사용할 수 있습니다. 구버전을 사용한다면 위의 다른 방법들을 활용하세요.
마무리
지금까지 엑셀에서 드롭다운 목록을 선택해 다른 시트의 데이터를 가져오는 4가지 쉬운 방법을 살펴보았습니다. VLOOKUP, INDIRECT, 데이터 유횅성 검사, FILTER까지 상황에 맞는 방법을 골라 활용하면 반복적인 데이터 조회 작업을 크게 줄일 수 있습니다. 글 초반의 연습 파일을 내려받아 직접 실습해 보시길 권합니다. 궁금한 점이나 추가로 알고 싶은 내용이 있다면 댓글로 남겨주세요.
함께 읽으면 좋은 글
- 엑셀 드롭다운 목록에 항목 추가하는 5가지 방법
- VBA로 드롭다운 목록에서 값 선택하기 (2가지 방법)
- 공백이 있는 종속 드롭다운 목록 만드는 방법
- VBA로 드롭다운 목록에 고유 값 넣기 (완벽 가이드)
- 엑셀 드롭다운 목록에서 중복 제거하는 4가지 방법