엑셀 Ctrl + F 로 검색하신다면, 이렇게 한 번 해보세요! | VLOOKUP 자동화

엑셀 INDEX/MATCH 함수와 배열수식을 활용하여 CTRL + F 찾기/검색 기능을 함수로 자동화하는 방법을 알아봅니다.

홈페이지 » 엑셀 찾기 및 바꾸기, 검색 기능 자동화 - 함수 2개로 완벽 해결!

엑셀 찾기 및 바꾸기, 검색 기능 자동화 - 함수 2개로 완벽 해결!

엑셀 찾기 자동화 공식 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 엑셀 찾기 검색 자동화 - INDEX/MATCH 공식
    예제파일
  • [엑셀고급] 엑셀 찾기 검색 자동화 - INDEX/MATCH 공식
    완성파일

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


엑셀 찾기 & 검색 - VLOOKUP 자동화 공식

세로방향 검색

=IF(MIN(IF(전체범위=찾을값,MATCH(ROW(전체범위),ROW(전체범위))))=0,"",INDEX(출력범위,MIN(IF(전체범위=찾을값,MATCH(ROW(전체범위),ROW(전체범위))))))

가로방향 검색

=IF(MIN(IF(전체범위=찾을값,MATCH(COLUMN(전체범위),COLUMN(전체범위))))=0,"",INDEX(출력범위,MIN(IF(전체범위=찾을값,MATCH(COLUMN(전체범위),COLUMN(전체범위))))))
엑셀 찾기 검색 자동화 vlookup 공식
엑셀 찾기/검색 자동화 VLOO,KUP 공식 설명
인수 설명
찾을값 검색할 값 또는 값이 입력된 셀 주소입니다.
전체범위 값을 검색할 전체 범위입니다.
출력범위 일치하는 값이 있을 경우 출력할 범위입니다.

INDEX/MATCH 공식 기초

INDEX/MATCH 함수는 기존 VLOOKUP 함수만으로는 해결이 어려운 다양한 문제를 다루기 위해 실무자가 꼭 알아야 할 엑셀 필수 공식입니다. INDEX/MATCH 기본 공식은 아래 영상 강의에서 자세히 설명해드렸습니다.

INDEX/MATCH 공식을 사용하면 아래 여러가지 응용 공식을 작성할 수 있습니다. 각 공식에 대한 요약설명 및 자세한 동작원리는 아래 관련 포스트를 참고해주세요.

넓은 범위를 검색하는 VLOOKUP 공식의 동작원리

넓은 범위를 검색하는 VLOOKUP 공식 (a.k.a 찾기 및 검색 자동화 공식)은 기존의 VLOOKUP 함수와 INDEX/MATCH 함수의 부족한 부분을 보완한 공식입니다.

기존의 INDEX/MATCH 함수는 값을 검색할 범위로 가로나 세로로 긴 범위만 참조할 수 있었는데요. 그러다보니 넓은 범위를 대상으로 값을 검색해야 할 경우에는 찾기 기능을 사용하거나 매크로를 사용해야만 했습니다.

VLOOKUP INDEXMATCH 제한사항

하지만 이번 강의에서 소개해드린 공식을 사용하시면, 넓은 범위를 대상으로 값을 검색할 수 있어 실무에서 필요한 대부분의 찾기/검색 업무를 손쉽게 자동화할 수 있습니다.

  1. ROW 함수로 행번호 배열 만들기 : ROW 함수로 범위를 입력하면 범위의 행번호가 배열로 반환됩니다. 예제파일의 L4:L15 범위를 선택 후, 아래 수식을 입력한 다음 Ctrl + Shift + Enter 로 배열수식을 입력하면 범위의 행번호가 배열로 반환됩니다.
    =ROW(B2:B12)
    엑셀 찾기 검색 자동화 공식 행번호 배열
    ROW 함수에 범위를 입력하면 행번호가 배열로 반환됩니다.
    오빠두Tip : 엑셀 365버전 사용자는 L4셀만 선택 후, =ROW(B2:B12) 를 일반 함수와 동일하게 Enter로 입력하면 파란색 테두리와 함께 배열이 자동으로 출력됩니다.
  2. MATCH/ROW 함수로 1부터 증가하는 순번만들기 : MATCH 함수와 ROW 함수를 활용하면 1부터 증가하는 연속된 순번을 만들 수 있습니다. 예를들어, 1부터 5까지 1씩 증가하는 순번을 만들어야 할 경우 아래 공식을 사용합니다.
    =MATCH(찾을값,범위,[일치옵션])
    =MATCH(ROW(A1:A5),ROW(A1:A5))
    =MATCH({1,2,3,4,5},{1,2,3,4,5})
    =1,2,3,4,5

    MATCH 함수는 첫번째 인수인 찾을 값을 하나씩 돌아가며 범위에서 값이 몇번째 위치하는지 순서를 검색합니다. 첫번째 찾을값(1)은 첫번째, 두번째 찾을값(2)는 두번째,... 있으므로 1부터 연속하는 순번이 반환됩니다.

  3. 예제파일의 O5:O15 범위를 선택 후, 아래 수식을 입력한 뒤 Ctrl + Shift + Enter로 수식을 입력하면 1부터 증가하는 순번이 반환됩니다.
    =MATCH(ROW(B2:B12),ROW(B2:B12))

    엑셀 연속하는 순번 반환 공식
    MATCH 함수와 ROW 함수를 활용하면 1부터 증가하는 순번을 반환합니다.

  4. 넓은 범위 대상으로 값 검색하기 : 이제 넓은 범위를 대상으로 찾을 값이 어디에 위치하는 검색해보겠습니다. 예제파일의 R5:V15 범위를 선택 후, 아래 수식을 붙여넣기 합니다. 이후 수식을 Ctrl + Shift + Enter 로 수식을 입력합니다. 수식을 입력하면 범위에서 찾을 값만 TRUE로 표시됩니다.
    =B2:F12=I10

    엑셀 전체 범위 찾을 값 true
    범위에서 일치하는 값만 TRUE로 반환됩니다.

  5. 이제 지금까지 알아본 공식에 IF 함수를 접목하면 넓은 범위를 대상으로 찾을 값의 위치를 받아올 수 있습니다. 예제파일의 X5:AB15 범위를 선택한 다음, 아래 수식을 붙여넣기 후 Ctrl + Shift + Enter로 입력합니다.
    =IF(B2:F12=I10,MATCH(ROW(B2:F12),ROW(B2:F12)))
  6. 우리가 처음에 구했던 MATCH/ROW 공식은 1부터 증가하는 순번을 반환합니다. 그리고 IF함수의 조건은 찾을 값인 경우에만 TRUE를 반환하죠. 따라서, 수식을 입력하면 "찾을값이 몇 번째에 있는지", 순번을 출력하게됩니다.

    엑셀 넓은 범위 검색 공식 순번
    범위에서 찾을 값과 일치하는 값의 순번만 숫자로 표시됩니다.

  7. 찾기 검색 자동화 공식 완성 : 이제 마지막 단계입니다. MIN 함수로 범위 내에서 가장 작은 숫자를 계산하면, 찾을 값의 순번이 계산됩니다. 아래 수식을 예제파일 AE5셀에 붙여넣기 후, Ctrl + Shift + Enter 로 입력합니다.
    =MIN(IF(B2:F12=I10,MATCH(ROW(B2:F12),ROW(B2:F12))))
    엑셀 MIN 함수 최소값 구하기
    MIN 함수를 사용해서 범위 내에서 가장 작은 숫자(=순번)를 계산합니다.
    오빠두Tip : MIN 함수는 범위 내 '숫자'만 대상으로 최소값을 계산합니다. 따라서 논리값인 TRUE/FALSE는 제외하고 최소값을 구합니다. 반면 MINA 함수를 사용하면 논리값을 포함하여 최소값을 구할 수 있습니다.
  8. 이제 INDEX 함수로, 범위에서 받아온 순번에 위치하는 값을 출력하면 넓은 범위 VLOOKUP 공식이 완성됩니다. 아래 수식을 예제파일 AH5 셀에 붙여넣기 후, Ctrl + Shift + Enter로 입력합니다.
    =INDEX(B2:B12,MIN(IF(B2:F12=I10,MATCH(ROW(B2:F12),ROW(B2:F12)))))

    엑셀 넓은 범위 VLOOKUP 공식 완성
    INDEX 함수로 출력범위에서 받아온 순번에 위치한 값을 표시하면 넓은 범위 VLOOKUP 공식이 완성됩니다.

4.8 6 투표
게시글평점
guest
9 댓글
Inline Feedbacks
모든 댓글 보기
엑셀고수되고파
엑셀고수되고파
2021년 8월 19일 10:52 오후
게시글평점 :
     

유튜브 영상 14:00분 지점에서 왜 FALSE값들 사이에서 10이라는 숫자가 MIN값이 되는지가 이해가지를 않아서 댓글로 질문 남깁니다.....
늘 좋은 컨텐츠에 감사드립니다

햇님달님별님나님
햇님달님별님나님
2021년 8월 23일 4:19 오후
게시글평점 :
     

많은 도움이 될 것 같습니다. 감사합니다 🙂

남트라
남트라
2021년 8월 25일 2:28 오후
게시글평점 :
     

네 많은 엑셀 강의들이 들어도 무슨 얘길 하는건지 알아듣기 어려운게 다들 본인의 시각에서 강의를 하는 느낌인데 오빠두액셀 강의는 다른 것 같아요.

레이안
레이안
2021년 8월 31일 3:39 오후
게시글평점 :
     

좋은 내용에 강의를 해주셔서 감사합니다 ^^

코끼리
코끼리
2021년 9월 14일 8:21 오전
답글 남기기  레이안

너무 유용합니다. 바로 사용해요~

코끼리
코끼리
2021년 9월 14일 8:23 오전
게시글평점 :
     

너무 유용합니다. 바로 사용해요~

다린
다린
2021년 10월 10일 1:26 오후
게시글평점 :
     

이용자가 궁금할만한 내용을 강의자료에 정말 꼼꼼하게 잘 정리해두셨네요~ 동영상에서 원하는 부분을 바로 찾아볼 수 있도록 정리하는 센스까지요. 지금부터라도 열심히 공부하겠지만, 좀 더 일찍 이 자료들을 만날 수 있었더라면 얼마나 좋았을까… 더보기 »

Fixframe
Fixframe
2021년 11월 3일 8:48 오전
게시글평점 :
     

좋은 정보 감사합니다.

9
0
여러분의 생각을 댓글로 남겨주세요.x