엑셀 찾기 및 바꾸기, 검색 기능 자동화 - 함수 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(전체범위))))))
엑셀 찾기/검색 자동화 VLOO,KUP 공식 설명 인수 설명 찾을값 검색할 값 또는 값이 입력된 셀 주소입니다. 전체범위 값을 검색할 전체 범위입니다. 출력범위 일치하는 값이 있을 경우 출력할 범위입니다. INDEX/MATCH 함수는 기존 VLOOKUP 함수만으로는 해결이 어려운 다양한 문제를 다루기 위해 실무자가 꼭 알아야 할 엑셀 필수 공식입니다. INDEX/MATCH 기본 공식은 아래 영상 강의에서 자세히 설명해드렸습니다.
INDEX/MATCH 공식을 사용하면 아래 여러가지 응용 공식을 작성할 수 있습니다. 각 공식에 대한 요약설명 및 자세한 동작원리는 아래 관련 포스트를 참고해주세요.
넓은 범위를 검색하는 VLOOKUP 공식 (a.k.a 찾기 및 검색 자동화 공식)은 기존의 VLOOKUP 함수와 INDEX/MATCH 함수의 부족한 부분을 보완한 공식입니다.
기존의 INDEX/MATCH 함수는 값을 검색할 범위로 가로나 세로로 긴 범위만 참조할 수 있었는데요. 그러다보니 넓은 범위를 대상으로 값을 검색해야 할 경우에는 찾기 기능을 사용하거나 매크로를 사용해야만 했습니다.
하지만 이번 강의에서 소개해드린 공식을 사용하시면, 넓은 범위를 대상으로 값을 검색할 수 있어 실무에서 필요한 대부분의 찾기/검색 업무를 손쉽게 자동화할 수 있습니다.
- ROW 함수로 행번호 배열 만들기 : ROW 함수로 범위를 입력하면 범위의 행번호가 배열로 반환됩니다. 예제파일의 L4:L15 범위를 선택 후, 아래 수식을 입력한 다음 Ctrl + Shift + Enter 로 배열수식을 입력하면 범위의 행번호가 배열로 반환됩니다.
=ROW(B2:B12)
ROW 함수에 범위를 입력하면 행번호가 배열로 반환됩니다. 오빠두Tip : 엑셀 365버전 사용자는 L4셀만 선택 후, =ROW(B2:B12) 를 일반 함수와 동일하게 Enter로 입력하면 파란색 테두리와 함께 배열이 자동으로 출력됩니다. - 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,5MATCH 함수는 첫번째 인수인 찾을 값을 하나씩 돌아가며 범위에서 값이 몇번째 위치하는지 순서를 검색합니다. 첫번째 찾을값(1)은 첫번째, 두번째 찾을값(2)는 두번째,... 있으므로 1부터 연속하는 순번이 반환됩니다.
- 예제파일의 O5:O15 범위를 선택 후, 아래 수식을 입력한 뒤 Ctrl + Shift + Enter로 수식을 입력하면 1부터 증가하는 순번이 반환됩니다.
=MATCH(ROW(B2:B12),ROW(B2:B12))
MATCH 함수와 ROW 함수를 활용하면 1부터 증가하는 순번을 반환합니다. - 넓은 범위 대상으로 값 검색하기 : 이제 넓은 범위를 대상으로 찾을 값이 어디에 위치하는 검색해보겠습니다. 예제파일의 R5:V15 범위를 선택 후, 아래 수식을 붙여넣기 합니다. 이후 수식을 Ctrl + Shift + Enter 로 수식을 입력합니다. 수식을 입력하면 범위에서 찾을 값만 TRUE로 표시됩니다.
=B2:F12=I10
범위에서 일치하는 값만 TRUE로 반환됩니다. - 이제 지금까지 알아본 공식에 IF 함수를 접목하면 넓은 범위를 대상으로 찾을 값의 위치를 받아올 수 있습니다. 예제파일의 X5:AB15 범위를 선택한 다음, 아래 수식을 붙여넣기 후 Ctrl + Shift + Enter로 입력합니다.
=IF(B2:F12=I10,MATCH(ROW(B2:F12),ROW(B2:F12)))
- 우리가 처음에 구했던 MATCH/ROW 공식은 1부터 증가하는 순번을 반환합니다. 그리고 IF함수의 조건은 찾을 값인 경우에만 TRUE를 반환하죠. 따라서, 수식을 입력하면 "찾을값이 몇 번째에 있는지", 순번을 출력하게됩니다.
범위에서 찾을 값과 일치하는 값의 순번만 숫자로 표시됩니다. - 찾기 검색 자동화 공식 완성 : 이제 마지막 단계입니다. MIN 함수로 범위 내에서 가장 작은 숫자를 계산하면, 찾을 값의 순번이 계산됩니다. 아래 수식을 예제파일 AE5셀에 붙여넣기 후, Ctrl + Shift + Enter 로 입력합니다.
=MIN(IF(B2:F12=I10,MATCH(ROW(B2:F12),ROW(B2:F12))))
MIN 함수를 사용해서 범위 내에서 가장 작은 숫자(=순번)를 계산합니다. 오빠두Tip : MIN 함수는 범위 내 '숫자'만 대상으로 최소값을 계산합니다. 따라서 논리값인 TRUE/FALSE는 제외하고 최소값을 구합니다. 반면 MINA 함수를 사용하면 논리값을 포함하여 최소값을 구할 수 있습니다. - 이제 INDEX 함수로, 범위에서 받아온 순번에 위치하는 값을 출력하면 넓은 범위 VLOOKUP 공식이 완성됩니다. 아래 수식을 예제파일 AH5 셀에 붙여넣기 후, Ctrl + Shift + Enter로 입력합니다.
=INDEX(B2:B12,MIN(IF(B2:F12=I10,MATCH(ROW(B2:F12),ROW(B2:F12)))))
INDEX 함수로 출력범위에서 받아온 순번에 위치한 값을 표시하면 넓은 범위 VLOOKUP 공식이 완성됩니다.
늘 좋은 컨텐츠에 감사드립니다
MIN 함수는 숫자값 중에서만 최소값을 구합니다. 논리값(FALSE,TRUE)는 제외됩니다.
반면, MINA 함수를 사용하시면 논리값을 포함해서 최소값을 구할 수 있습니다.
동영상에서 원하는 부분을 바로 찾아볼 수 있도록 정리하는 센스까지요.
지금부터라도 열심히 공부하겠지만, 좀 더 일찍 이 자료들을 만날 수 있었더라면 얼마나 좋았을까 하는 아쉬움이 큽니다.
감사합니다.
저도 min 함수가 궁금합니다. 수식에서 if 조건을 만족하는 값은 한개인데 왜 꼭 min 함수로 묶어야만 값을 반환하는지 궁금합니다. (max로 묶어도 값이 나오던데, 왜 min 또는 max가 필요할까요?)
Min 은 중복값이 있을 경우 작은값(위쪽/왼쪽)을, Max 함수는 큰값(아래/오른쪽)을 검색합니다.