Excel에서 검색 가능한 드롭 다운 목록을 만드는 방법은 무엇입니까?

값이 많은 드롭 다운 목록의 경우 적절한 값을 찾는 것은 쉬운 일이 아닙니다. 이전에는 드롭 다운 상자에 첫 글자를 입력 할 때 드롭 다운 목록을 자동 완성하는 방법을 도입했습니다. 자동 완성 기능 외에도 드롭 다운 목록에서 적절한 값을 찾을 때 작업 효율성을 높이기 위해 드롭 다운 목록을 검색 가능하게 만들 수도 있습니다. 드롭 다운 목록을 검색 가능하게 만들려면 아래 자습서가 단계별로 보여줍니다.

Excel에서 검색 가능한 드롭 다운 목록 만들기
드롭 다운 목록에 대한 추가 자습서 ...

Excel에서 검색 가능한 드롭 다운 목록 만들기

예를 들어 드롭 다운 목록에 필요한 소스 데이터는 A2 : A9 범위에 있습니다.

이 방법을 사용하려면 데이터 유효성 검사 드롭 다운 목록 대신 콤보 상자가 필요합니다. 검색 가능한 드롭 다운 목록을 생성하려면 다음과 같이하십시오.

1. 찾을 수없는 경우 개발자 리본 탭에서 다음과 같이 개발자 탭을 활성화하십시오.

1). Excel 2010 및 2013에서 입양 부모로서의 귀하의 적합성을 결정하기 위해 미국 이민국에 > 옵션. 그리고에 옵션 대화 상자에서 리본 맞춤 설정 오른쪽 창에서 개발자 상자를 클릭 한 다음 OK 단추. 스크린 샷보기 :

2). Outlook 2007에서 사무실 버튼> Excel 옵션. 에서 Excel 옵션 대화 상자에서 인기순 오른쪽 표시 줄에서 리본에 개발자 탭 표시 상자를 클릭하고 마지막으로 OK 버튼을 클릭합니다.

2. 표시 후 개발자 탭을 클릭하십시오 개발자 > 끼워 넣다 > 콤보 박스. 스크린 샷보기 :

3. 워크 시트에 콤보 상자를 그리고 마우스 오른쪽 단추로 클릭합니다. 고르다 등록 오른쪽 클릭 메뉴에서.

4. 에서 등록 대화 상자에서 다음을 수행해야합니다.

1). 고르다 거짓 에서 AutoWordSelect 들;

2). 다음에서 셀 지정 LinkedCell 들. 이 경우 A12를 입력합니다.

3). 고르다 2-fmMatchEntryNone 에서 MatchEntry 들;

4). 유형 드롭 다운 목록 로 ListFillRange 들;

5). 닫기 등록 대화 상자. 스크린 샷보기 :

5. 이제 클릭하여 디자인 모드를 닫습니다. 개발자 > 디자인 모드.

6. 빈 셀 C2를 선택한 다음 수식 복사 및 붙여 넣기 =-ISNUMBER (IFERROR (SEARCH ($ A $ 12, A2,1), "")) 수식 입력 줄에 입력하고 Enter 키를 누릅니다. 선택한 셀을 동일한 수식으로 자동 채우기 위해 C9 셀로 드래그합니다. 스크린 샷보기 :

노트:

1. $ A $ 12는 4 단계에서 LinkedCell 필드에 지정한 셀입니다.

2. 위 단계를 완료 한 후 이제 테스트 할 수 있습니다. 드롭 다운 상자에 문자 C를 입력하면 C를 포함하는 모든 셀이 숫자 1로 채워지는 것을 볼 수 있습니다.

7. D2 셀을 선택하고 수식을 입력합니다. = IF (C2 = 1, COUNTIF ($ C $ 2 : C2,1), "") 수식 입력 줄에 입력하고 Enter 키를 누릅니다. 그런 다음 D2의 채우기 핸들을 D9로 드래그하여 D3 : D9 범위를 채 웁니다.

8. E2 셀 선택, 수식 복사 및 붙여 넣기 =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"") 수식 입력 줄에 입력하고 Enter 키를 누릅니다. 그런 다음 E2의 채우기 핸들을 E9로 끌어 셀을 채 웁니다. 그러면 아래 스크린 샷과 같이 셀이 채워지는 것을 볼 수 있습니다.

9. 이제 이름 범위를 만들어야합니다. 클릭하세요 공식 > 이름 정의.

10. 에서 새 이름 대화 상자에서 다음을 입력하십시오. 드롭 다운 목록 으로 이름 상자, 수식 입력 =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1) 로 을 참고하여 상자를 클릭 한 다음 OK 버튼을 클릭합니다.

11. 이제 클릭하여 디자인 모드를 활성화합니다. 개발자 > 디자인 모드. 그런 다음 3 단계에서 만든 콤보 상자를 두 번 클릭하여 응용 프로그램 용 Microsoft Visual Basic 창.

12. 아래 VBA 코드를 복사하여 코드 편집기에 붙여 넣습니다.

VBA 코드 : 드롭 다운 목록을 검색 가능하게 만들기

Private Sub ComboBox1_GotFocus()
	ComboBox1.ListFillRange = "DropDownList"
	Me.ComboBox1.DropDown
End Sub

13. 닫기 응용 프로그램 용 Microsoft Visual Basic 창.

이제부터 목록 상자에 입력을 시작하면 모호한 검색이 시작되고 드롭 다운 목록에 관련 값만 나열됩니다.

주의 사항: 워크 시트를 닫았다가 다시 열면 12 단계에서 만든 VBA 코드가 자동으로 제거됩니다. 따라서이 통합 문서를 Excel 매크로 사용 통합 문서 형식으로 저장해야합니다.

그리고, 확인란이있는 드롭 다운 목록 ~의 유용성 Excel 용 Kutools 필요에 따라 지정된 범위, 현재 워크 시트, 현재 통합 문서 또는 열려있는 모든 통합 문서의 확인란이있는 드롭 다운 목록을 쉽게 만들 수 있습니다.
지금 다운로드하여 사용해보십시오! (30 일 무료 트레일)

최고의 사무 생산성 도구

Excel 용 Kutools는 대부분의 문제를 해결하고 생산성을 80 % 향상시킵니다.

재사용: 빠르게 삽입 복잡한 공식, 차트 그리고 이전에 사용한 모든 것; 셀 암호화 암호로; 메일 링리스트 생성 이메일 보내기 ...
슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집 할 수 있습니다.) 레이아웃 읽기 (많은 수의 셀을 쉽게 읽고 편집합니다.) 필터링 된 범위에 붙여 넣기...
셀 / 행 / 열 병합 데이터 손실없이; 셀 내용 분할; 중복 행 / 열 결합... 중복 셀 방지; 범위 비교...
중복 또는 고유 선택 행; 빈 행 선택 (모든 셀이 비어 있음); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 무작위 선택 ...
정확한 사본 수식 참조를 변경하지 않고 여러 셀; 참조 자동 생성 여러 시트에; 글 머리 기호 삽입, 확인란 등 ...
텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 부분합을 만들고 인쇄합니다. 셀 내용과 주석 간 변환...
슈퍼 필터 (다른 시트에 필터 구성표 저장 및 적용) 고급 정렬 월 / 주 / 일, 빈도 등 특수 필터 굵은 기울임 꼴로 ...
통합 문서와 워크 시트 결합; 키 열을 기반으로 테이블 병합; 데이터를 여러 시트로 분할; xls, xlsx 및 PDF 일괄 변환...
300 개 이상의 강력한 기능. Office / Excel 2007-2019 및 365를 지원합니다. 모든 언어를 지원합니다. 기업 또는 조직에 쉽게 배포 할 수 있습니다. 전체 기능 30 일 무료 평가판. 60 일 환불 보장.

자세히보기 ... 무료 다운로드... 매수...

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
생산성을 50 % 향상시키고 매일 수백 번의 마우스 클릭을 줄입니다!

자세히보기 ... 무료 다운로드... 매수...

60 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
ismaeel ahmad · 1 years ago
how to use this dropdown in vba form any konw please reply
- Reply
To post as a guest, your comment is unpublished.
Jeroen · 1 years ago
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
- Reply
To post as a guest, your comment is unpublished.
Ajesh · 1 years ago
I have around 80000 data while running excel is hang
- Reply
To post as a guest, your comment is unpublished.
sourav1986 · 1 years ago
Sir How to use this in excel userform combobox....? plz help
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Sourav Singha,
  Can't use it in a userform combobox. Sorry for the inconvenience.
  
  Reply
To post as a guest, your comment is unpublished.
Josh · 1 years ago
Is there a way to make it call up a hyperlink? My email is joshuarobertdaniels@gmail.com
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Josh,
  Sorry can;t help you with that yet.
  
  Reply
To post as a guest, your comment is unpublished.
Vrezh · 2 years ago
I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Vrezh,
  Sorry this kind of problem can't be solved yet. Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Steve Olah · 2 years ago
How can I use this? I have two problem
1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
I need for I can search with typing too, so simple(not active-x) combo is wrong.

2nd how set padding? - my combo text when I search is not see whole because itt has padding.

3th if my source is C column, how drop empty elements from list
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Steve Olah,
  Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
  
  Reply
To post as a guest, your comment is unpublished.
sigidap90@gmail.com · 2 years ago
I had try tutorial drodown list searchable, Some like that,. But i'am can't make searcable from list and Combo Box Search??? How to make VBA Macro Connected in Excel??
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Sorry didn't get your point. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
  
  Reply
To post as a guest, your comment is unpublished.
Mubashir · 2 years ago
I want to make this drop down to work for whole column, so that with multiple entries, I have this search suggestion option available every time. Above option, just shows suggestion for one time. Please help
- Reply
To post as a guest, your comment is unpublished.
Min · 2 years ago
Hi. I get many helps from your post. However, it doesn't make automatic dropdown if there are mixed language on the list (e.g: first cell is written in English, second cell is written in Korean etc.) Has anyone had solve this problem?
- Reply
To post as a guest, your comment is unpublished.
dan · 3 years ago
The automatic dropdown list is not working. Everything else is working. Do you know where my snag might lie?
- Reply
- To post as a guest, your comment is unpublished.
  dan · 3 years ago
  
  I figured it my be with the last step. I put the VBA code in my personal.xlsb worksheet but looks like the code needs to be on the sheet of the respective workbook. hazah
  
  Reply
To post as a guest, your comment is unpublished.
michaeljcianci@gmail.com · 3 years ago
I got this to work but for some reason excel crashes if i attempt to use the arrows to select things from the drop down. has anyone had this issue? is it even supposed to be possible?
thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Michael,
  The problem you mentioned does not appear in my case. Which Office version do you use?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  yogi · 2 years ago
  
  Dear Crystal,
  
  I got the same problem as michael does, excel crashes every time i use down arrow in the drop box, i got excel 2010 on my laptop, which version do you use?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 2 years ago
  
  Hi yogi,
  The method has been successfully tested in Office 2010, 2013 as well as 2016. No idea for this problem. Sorry about that.
  
  Reply
To post as a guest, your comment is unpublished.
alluxxx · 3 years ago
Is there a way to prioritize the location of a letter in a word? I used this method, but when I type in "A," for example, I get terms with "A" anywhere in the word. I would prefer if it started showing all the terms that begin with "A". Is this possible?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hi,
  Sorry for reply so late. If you want to search values in drop-down list that begin with a certain character, please change the formula in column C to
  =--ISNUMBER(IFERROR(SEARCH($A$12,MID(A3,1,1),1),"")).
  Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
KELS · 3 years ago
I may have misunderstood how this function is supposed to work, but i can only get the combo box search to populate one cell, A12 (using the example from the tutorial). If i click into A13 to populate the next cell with a different value from the drop down, it just replaces what i have in A12 and does not populate A13. I need this search to apply to any cell in column A from A12 down. Have i done something incorrect or does this combo box search only allow a single cell in the workbook to be populated with the result? Will be grateful for any help with this.
- Reply
To post as a guest, your comment is unpublished.
Kathleen · 3 years ago
I may have misunderstood how this function is supposed to work, but i can only get the combo box search to populate one cell, A12 (using the example from the tutorial). If i click into A13 to populate the next cell with a different value from the drop down, it just replaces what i have in A12 and does not populate A13. I need this search to apply to any cell in column A from A12 down. Have i done something incorrect or does this combo box search only allow a single cell in the workbook to be populated with the result? Will be grateful for any help with this.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Good Day,
  This combo box search only allow a single cell in the workbook to be populated with the result.
  I'll try to find another method to solve your problem.
  
  Reply
To post as a guest, your comment is unpublished.
Ben Johnston · 3 years ago
I feel dumb, but immediately after posting, I realized I probably hadn't added the 1 to DropDownList1 in the VBA, and sure enough that was the problem! Thanks anyway!
- Reply
To post as a guest, your comment is unpublished.
Ben Johnston · 3 years ago
Hello, thanks for the tutorial! I'm having an issue where every time I type in the combo box, "DropDownList1" disappears from the "ListFillRange" property. So long as I don't type in the box, if I retype "DropDownList1" in the property, the box does show suggestions. I have looked everything over and could not find any errors. Is this a common problem, and is there a way to fix it? Thank you for your time!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Ben,
  I am also comfusing about the disappearing of the "DripDownList" from the "ListFillRange" property
  But it does not influence the finally rsult of making the drop-down list seachable.
  
  Reply
To post as a guest, your comment is unpublished.
dave · 3 years ago
is there a way to have the search box put the top result if left blank? in the case of this example it would automatically put china if it was left blank
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear dave,
  Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
  
  Reply
To post as a guest, your comment is unpublished.
Al B · 3 years ago
I've had an ongoing issue with all documents I've used this method on. A shadow of the drop-down box reappears underneath it each time I click into another cell within the spreadsheet and begin typing. It's beyond just a nuisance because when the shadow drops down, it prevents use of any additional searchable drop-down boxes. Please help!!! This is affecting multiple documents we use throughout our organization.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Good day,
  Sorry for replying so late. The problem you methoded does not appear in my case.Would be nice if you could provide your Office verson. Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Gunawan Budianto · 3 years ago
4. In the Properties dialog box, you need to:
1). Select False in the AutoWordSelect field;
2). Specify a cell in the LinkedCell field. In this case, we enter A12;

Why A12? thank's
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hi,
  This cell is optionally selected which can help to finish the whole operation. You can choose any one as you need.
  
  Reply
To post as a guest, your comment is unpublished.
Jelbin · 3 years ago
Hi As in forum,
I need to have this searchable dropdown for columns 2 to 500. Please let me know how i can as the second combo replicates the same in first which i dont want
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Jelbin,
  Can't handle this. Sorry about that.
  
  Reply
To post as a guest, your comment is unpublished.
Havocknox · 4 years ago
Thank you for this breakdown to make the combo box searchable. I have even gotten three of them working on the same page. My problem I have run into is when I start typing in the search information and the info narrows down, if I hit the down arrow key to select the item in the list Excel crashes on me. Has anyone had this happen, and if so have you found a way to solve this issue.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hi,
  The problemm you mentioned does not appear in my case. Would you please provide your Office version?
  
  Reply
To post as a guest, your comment is unpublished.
Heric · 4 years ago
Hi,

your guide is most helpful, but i still encounter one last problem.
I am trying to do a simple invoice, and do the drop down for my customer name cell, must my customer listing be in the same worksheet as my invoice worksheet? Is is possible i have two worksheet, "invoice" & "customer name", and do the drop down list for customer name at "invoice" worksheet?

Thank you
- Reply
To post as a guest, your comment is unpublished.
Jaydie · 4 years ago
Thank you, I used above and it works perfectly....

Until you have two combo boxes in one sheet.. When you want to type in the second combo box it highlights the text in the first combo box and does not want to search
If I leave the first box blank, the second box works fine

Please help
- Reply
To post as a guest, your comment is unpublished.
NAJMA · 4 years ago
plz help me
i cannt enter formula in formula bar
when i paste this formula & paste this =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
give me error.type :(
- Reply
To post as a guest, your comment is unpublished.
Ashok · 4 years ago
HI, How to do the same searchable program for contnious rwo , i tried and it is working one row only , i want to do the same for below row also for different name
- Reply
To post as a guest, your comment is unpublished.
Ahmed Shahin · 4 years ago
Hi Herb,

What if i created a drop down list from another work sheet? the formula " =--ISNUMBER(IFERROR(SEARCH($A$2,H2,1),""))" has wrong reference and when i edit it it doesn't allow to put the right cell. what do you suggest? thank you
- Reply
To post as a guest, your comment is unpublished.
Yesenia · 4 years ago
I, like Cristina above, would also like to know how to make multiple combo boxes for one sheet. I tried but when I begin typing in the second combobox two things happen: 1. no drop down list appears, and 2. the simple act of typing in combobox2 activates the selection from my original combobox1 and highlights it in the drop down from combobox1. I checked to make sure all of my coding says combobox2 for combobox2 etc. for the other boxes but there is a disconnect that I can't figure out.
- Reply
- To post as a guest, your comment is unpublished.
  Jaydie · 4 years ago
  
  I have the exact same problem, have you managed a solution yet??
  
  Reply
To post as a guest, your comment is unpublished.
FAUZI · 4 years ago
Thank You.. Very helpfull.. God Bless You
- Reply
To post as a guest, your comment is unpublished.
Maarten · 5 years ago
Hi,

I can't fill in 'DropDownList' in the 'ListFillRange'.... What's the catch? I don't understand the solution of imad.
Thanks.
- Reply
- To post as a guest, your comment is unpublished.
  Herb123987 · 4 years ago
  
  [quote name="Maarten"]Hi,
  
  I can't fill in 'DropDownList' in the 'ListFillRange'.... What's the catch? I don't understand the solution of imad.
  Thanks.[/quote]
  
  I posted this answer above for IMAD and saw this posting down here for MAARTEN so I figured I'd post this for him too.
  
  I have seen this "how to make an autofill / auto suggest DDL / combo box" on a few different sites and they ALL want you to put "something" in the ListFillRange Properties field [b]BEFORE[/b] they have you [b]create a named range[/b] by clicking Formula > Define Name ....... and the [b]ListFillRange will always go blank in the Properties window[/b] UNTIL you define the name (Formula > Define Name)
  
  THAT is why i think IMAD, above and MAARTEN below (here) was having the problem - not 100% sure though.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Maarten · 4 years ago
  
  Hi there,
  
  Thanks a lot for your solution. I gave up already, but I'll try again.
  
  Reply
- To post as a guest, your comment is unpublished.
  Andone · 5 years ago
  
  try to put this=--ISNUMBER(IFERROR(SEARCH($A$12,$A$2,1),"")) instead =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),"")) in step 6
  
  Reply
To post as a guest, your comment is unpublished.
imad · 5 years ago
So I Finally got it to work! I attached the linkedcell to a vlookup and got all the information pulling into a row. I was wondering if there could be any extension on the vba to actually filter the table as we type?
- Reply
To post as a guest, your comment is unpublished.
imad · 5 years ago
Mine isn't working. My dropdownlist label was not working in the "properties" for the combobox. Everytime I entered it, it disappeared. So I used "test" instead. I adjusted the macro with the word test instead of dropdowmlist. Let me know if there is something else I can do? Search not working.
- Reply
- To post as a guest, your comment is unpublished.
  Herb123987 · 4 years ago
  
  [quote name="imad"]Mine isn't working. My dropdownlist label was not working in the "properties" for the combobox. Everytime I entered it, it disappeared. So I used "test" instead. I adjusted the macro with the word test instead of dropdowmlist. Let me know if there is something else I can do? Search not working.[/quote]
  
  I have seen this "how to make an autofill / auto suggest DDL / combo box" on a few different sites and they ALL want you to put "something" in the ListFillRange field BEFORE they have you create a name range by clicking Formula > Define Name and the ListFillRange will always go blank in the Properties window UNTIL you define the name (Formula > Define Name)
  
  THAT is why i think IMAD, above and MAARTEN below was having the problem - not 100% sure though.
  
  Reply
To post as a guest, your comment is unpublished.
MarkC · 5 years ago
For some reason when I click a selection from the drop down list after typing a few characters the drop down main value becomes blank... any idea why this would happen and how to get it to stop?

I have a command button that I want to click to then put the selection into the next available cell in a given range, but again the value blanks out when I click on it.
- Reply
- To post as a guest, your comment is unpublished.
  imad · 5 years ago
  
  I have the exact same problem. I did everything right but the dropdownlist label just goes blank everytime I press enter. If you figured it out, please do share!
  
  Reply
To post as a guest, your comment is unpublished.
Cristina · 5 years ago
Excellent post. Could you please explain how do you copy the same drop down list to multiple cells. I want to create an expense report and I want to be able to select a different expense on each row from the same drop down list. Thank you.
- Reply
- To post as a guest, your comment is unpublished.
  Pierpaolo · 3 years ago
  
  i have the same need
  
  Reply
To post as a guest, your comment is unpublished.
Prastuti · 5 years ago
very nicely explained. Loved it. Thank you !!
- Reply

귀하의 계정에 로그인

비밀번호 재설정