INDEX/MATCH 함수를 활용한, 엑셀 찾기/검색 공식! - CTRL + F 완벽 자동화

실습 가이드

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

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

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

세로방향 검색

가로방향 검색

INDEX/MATCH 함수는 VLOOKUP 함수만으로는 해결하기 어려운 다양한 검색 상황을 처리할 수 있어, 실무자가 반드시 익혀두어야 할 엑셀 필수 공식입니다. INDEX/MATCH 함수의 기본 사용법은 아래 영상 강의에서 자세히 설명해 드렸습니다.

INDEX/MATCH 공식을 응용하면 아래와 같이 다양한 검색 공식을 작성할 수 있습니다. 각 공식의 요약 설명과 자세한 동작 원리는 아래 관련 포스트를 참고해 주세요.

넓은 범위를 검색하는 VLOOKUP 공식(일명 찾기·검색 자동화 공식)은 기존 VLOOKUP 함수와 INDEX/MATCH 함수의 한계를 함께 보완한 공식입니다.

기존 INDEX/MATCH 함수는 가로 또는 세로로 긴 1차원 범위만 검색 대상으로 참조할 수 있었습니다. 그러다 보니 표 전체처럼 넓은 범위에서 값을 찾아야 할 때는 찾기 기능이나 매크로에 의존해야만 했습니다.

이번 강의에서 소개해 드린 공식을 사용하면 표 전체와 같은 넓은 범위를 대상으로 값을 검색할 수 있어, 실무에서 마주치는 대부분의 찾기·검색 업무를 손쉽게 자동화할 수 있습니다.

1. ROW 함수로 행번호 배열 만들기 : ROW 함수에 범위를 입력하면 해당 범위의 행 번호가 배열로 반환됩니다. 예제파일의 L4:L15 범위를 선택한 뒤 아래 수식을 입력하고 Ctrl + Shift + Enter로 배열수식을 입력하면, 범위의 행 번호가 배열로 반환됩니다.
   =ROW(B2:B12)

   

   오빠두Tip : 엑셀 365 버전 사용자는 L4셀만 선택한 뒤 =ROW(B2:B12)를 일반 함수처럼 Enter로 입력하면 파란색 테두리와 함께 배열이 자동으로 출력됩니다.
2. MATCH/ROW 함수로 1부터 증가하는 순번 만들기 : MATCH 함수와 ROW 함수를 결합하면 1부터 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))

   

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

   

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))))

   

   오빠두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)))))