엑셀 검색 기능 만들기

엑셀에 데이터가 많아지면 원하는 내용을 하나씩 찾아보는 것이 상당히 번거롭습니다.

물론 Ctrl + F를 눌러 찾기 기능을 사용할 수도 있지만, 특정 셀에 검색어를 입력하면 조건에 맞는 데이터가 자동으로 표시되는 검색 기능을 직접 만들어 두면 훨씬 편리합니다.

특히 직원 명단, 고객 목록, 상품 리스트, 재고 관리표처럼 데이터가 계속 쌓이는 엑셀 파일에서는 검색창을 만들어 활용하면 업무 효율을 높일 수 있습니다.

이번 글에서는 엑셀 검색 기능 만들기 방법을 기본 찾기 기능부터 FILTER 함수를 활용한 검색창까지 알아보겠습니다.

엑셀에서 가장 간단한 검색 방법 Ctrl + F

별도의 검색창을 만들 필요 없이 현재 엑셀 문서에서 특정 문자나 숫자를 찾는 것이 목적이라면 기본적으로 제공되는 찾기 기능을 사용하면 됩니다.

키보드에서

Ctrl + F

를 누르면 찾기 창이 나타납니다.

여기에 검색할 내용을 입력하고 다음 찾기 또는 모두 찾기를 선택하면 해당 내용이 들어 있는 셀을 확인할 수 있습니다.

예를 들어 직원 명단에서 김철수라는 이름을 찾고 싶다면 찾기 창에 김철수를 입력하면 됩니다.

단순히 특정 셀의 위치를 확인하는 용도라면 이 방법이 가장 간단합니다.

하지만 검색 결과를 별도의 영역에 자동으로 표시하고 싶다면 검색 기능을 직접 만들어야 합니다.

FILTER 함수로 엑셀 검색 기능 만들기

Microsoft 365나 FILTER 함수를 지원하는 최신 버전의 엑셀을 사용한다면 비교적 간단하게 검색 기능을 만들 수 있습니다.

예를 들어 다음과 같은 직원 데이터가 있다고 가정해 보겠습니다.

이름	부서	직급	연락처
김철수	영업팀	대리	010-1234-5678
이영희	총무팀	과장	010-2345-6789
박민수	영업팀	사원	010-3456-7890
김민지	개발팀	대리	010-4567-8901

A2:D5 영역에 위와 같은 데이터가 입력되어 있고 F2 셀을 검색어 입력칸으로 사용한다고 가정하겠습니다.

검색 결과를 표시할 위치에 다음 수식을 입력합니다.

=FILTER(A2:D5,ISNUMBER(SEARCH(F2,A2:A5)),”검색 결과 없음”)

이제 F2 셀에 이름을 입력하면 해당 검색어가 포함된 행이 자동으로 표시됩니다.

FILTER 검색 수식 원리 알아보기

사용한 수식은 다음과 같습니다.

=FILTER(A2:D5,ISNUMBER(SEARCH(F2,A2:A5)),”검색 결과 없음”)

각 함수의 역할을 살펴보면 이해하기 쉽습니다.

FILTER 함수

FILTER 함수는 지정한 조건에 맞는 데이터만 추출하는 함수입니다.

기본 구조는 다음과 같습니다.

=FILTER(데이터범위,조건,”결과없음”)

위 예제에서는 A2:D5가 검색 결과로 가져올 전체 데이터 영역입니다.

SEARCH 함수

SEARCH 함수는 특정 문자열이 셀 안에 포함되어 있는지 검색합니다.

예를 들어 검색창에 김이라고 입력하면

김철수
김민지

처럼 이름에 김이 포함된 데이터를 찾을 수 있습니다.

즉, 이름을 정확하게 모두 입력하지 않아도 일부 문자만 입력해 검색할 수 있다는 장점이 있습니다.

ISNUMBER 함수

SEARCH 함수가 검색어를 찾으면 해당 문자의 위치를 숫자로 반환합니다.

ISNUMBER 함수는 이 결과가 숫자인지를 확인해 TRUE 또는 FALSE로 변환합니다.

FILTER 함수는 이 결과를 이용해 조건에 맞는 행만 가져오게 됩니다.

부서명으로 검색 기능 만들기

검색 대상을 이름이 아니라 부서로 변경하는 것도 가능합니다.

데이터 구조가

A열 : 이름
B열 : 부서
C열 : 직급
D열 : 연락처

로 되어 있다면 다음 수식을 사용할 수 있습니다.

=FILTER(A2:D100,ISNUMBER(SEARCH(F2,B2:B100)),”검색 결과 없음”)

F2 셀에 영업이라고 입력하면 부서명에 영업이 포함된 직원들이 검색됩니다.

따라서 영업팀을 전부 입력하지 않고 일부 문자만 입력해도 검색할 수 있습니다.

여러 열을 동시에 검색하려면?

조금 더 편리하게 만들려면 이름뿐만 아니라 이름, 부서, 직급 등 여러 항목에서 검색어를 찾도록 만들 수 있습니다.

예를 들어 A열부터 C열까지 검색하려면 다음과 같은 방식으로 작성할 수 있습니다.

=FILTER(A2:D100,(ISNUMBER(SEARCH(F2,A2:A100)))+(ISNUMBER(SEARCH(F2,B2:B100)))+(ISNUMBER(SEARCH(F2,C2:C100))),”검색 결과 없음”)

이렇게 설정하면 F2에 입력한 검색어가

이름
부서
직급

중 하나에 포함되어 있어도 해당 행이 검색 결과에 나타납니다.

예를 들어 대리를 입력하면 직급이 대리인 직원이 나타나고, 개발을 입력하면 개발팀 직원이 나타나는 방식입니다.

하나의 검색창으로 여러 항목을 검색해야 할 때 유용합니다.

검색어를 입력하지 않았을 때 결과 숨기기

앞에서 만든 수식은 검색칸이 비어 있을 때 전체 데이터가 나타날 수 있습니다.

검색어를 입력하기 전에는 아무것도 표시하지 않게 만들고 싶다면 IF 함수를 함께 사용하면 됩니다.

=IF(F2=””,””,FILTER(A2:D100,ISNUMBER(SEARCH(F2,A2:A100)),”검색 결과 없음”))

이렇게 설정하면 F2가 비어 있을 때는 검색 결과도 표시되지 않습니다.

검색어를 입력하는 순간 조건에 맞는 데이터가 나타납니다.

실제 검색창처럼 사용하려면 이 방법이 조금 더 깔끔합니다.

여러 항목 검색 + 빈 검색창 처리하기

앞의 기능을 모두 합치면 다음과 같이 만들 수 있습니다.

=IF(F2=””,””,FILTER(A2:D100,(ISNUMBER(SEARCH(F2,A2:A100)))+(ISNUMBER(SEARCH(F2,B2:B100)))+(ISNUMBER(SEARCH(F2,C2:C100))),”검색 결과 없음”))

이 수식은

검색어가 없으면 → 결과 숨김

검색어가 있으면 → 이름·부서·직급에서 검색

일치하는 데이터가 없으면 → 검색 결과 없음 표시

방식으로 작동합니다.

직원 명단이나 고객관리 파일처럼 여러 항목을 한 번에 검색해야 하는 경우 활용하기 좋은 방식입니다.

검색창처럼 꾸미는 방법

수식만 만들어도 검색 기능은 작동하지만 검색어를 입력하는 셀을 눈에 잘 띄게 꾸며주면 사용하기가 훨씬 편리합니다.

예를 들어 F1 셀에는

검색어 입력

이라고 적고 F2 셀을 실제 검색창으로 사용할 수 있습니다.

F2 셀에 테두리와 배경색을 적용하고 열 너비를 넓혀주면 일반 프로그램의 검색창과 비슷하게 사용할 수 있습니다.

또한 검색 결과가 나타나는 영역 위쪽에는 이름, 부서, 직급, 연락처와 같은 제목을 만들어 두면 데이터 확인도 편리합니다.

FILTER 함수가 안 되는 경우

FILTER 함수는 동적 배열을 지원하는 비교적 최신 엑셀에서 사용할 수 있습니다.

사용 중인 엑셀 버전에서

#NAME?

오류가 나타나거나 FILTER 함수 자체가 인식되지 않는다면 해당 버전에서 FILTER 함수를 지원하지 않는 경우를 확인해야 합니다.

이런 환경에서는 고급 필터나 INDEX, MATCH 등의 함수를 조합하거나 VBA를 이용해 별도의 검색 기능을 만드는 방법을 고려할 수 있습니다.

따라서 검색 기능을 만들기 전에 현재 사용하는 엑셀에서 FILTER 함수가 지원되는지 확인하는 것이 좋습니다.

엑셀 근무일수 계산 방법 (NETWORKDAYS)