회사에서 월별·부서별로 시트가 나뉜 데이터를 VLOOKUP으로 조회할 때, 시트마다 수식을 다시 작성하느라 번거로웠던 적이 있으실 텐데요. 이 문제는 수식의 '고정된 범위' 때문에 발생하는데, 엑셀 기본 함수만으로 간단히 해결할 수 있습니다.👇
여러 시트 VLOOKUP 검색을 간단히 해결해보세요!👍
이럴 때 VLOOKUP과 INDIRECT 함수를 결합한 공식을 사용하면, 10초 만에 모든 시트의 값을 한 번에 검색할 수 있습니다.
=VLOOKUP($A2,INDIRECT("'"&B$1&"'!$A:$E"),2,0)
- 먼저 일반적인 방법으로 다른 시트의 값을 검색하는 VLOOKUP 수식을 작성한 후, 나머지 시트를 참조하도록 자동채우기 합니다.그러면 첫 번째 시트는 잘 검색되지만, 다른 시트에서는 오류가 발생하는 것을 확인할 수 있습니다.
다른 시트를 참조하는 VLOOKUP 함수를 자동채우기하면 오류가 발생합니다.
- 이러한 "고정된 범위"로 인해 발생하는 문제는 INDIRECT 함수로 해결할 수 있습니다. INDIRECT는 셀 주소를 문자로 입력해서 값을 참조하는 함수인데요, 예를 들어 =INDIRECT("B5")를 입력하면 B5셀을 참조하고, =INDIRECT("2월!C5")를 입력하면 2월 시트의 C5셀 값을 가져옵니다.
=INDIRECT("2월!C5")
INDIRECT 함수는 문자로 입력된 주소의 값을 동적으로 참조합니다.
- 따라서, 앞서 작성한 VLOOKUP 수식의 참조 범위 부분을 INDIRECT 함수와 큰따옴표로 감싸주겠습니다. 다음과 같이 수식을 작성합니다.
=VLOOKUP(B5,INDIRECT("'1월'!B:C"),2,0)
VLOOKUP 함수의 참조 범위를 INDIRECT 함수로 작성합니다.
- 지금부터가 핵심입니다. INDIRECT 안의 시트 이름을 직접 입력하지 말고 시트명이 적힌 셀을 참조하도록 바꿔야 합니다. 따라서 시트 이름 부분을 지운 후, 다음과 같이 큰따옴표 2개 + & 기호 2개 + 시트명이 입력된 셀을 넣어주세요.
=VLOOKUP(B5,INDIRECT("'"&C4&"'!B:C"),2,0)
INDIRECT 함수의 시트명을 셀 참조로 변경합니다.
- 이제 자동 채우기를 위해 참조 셀의 행과 열을 절대 참조($)로 고정합니다. 시트명이 입력된 머리글 행은 숫자 앞에 $를, 검색값이 입력된 열은 알파벳 앞에 $를 추가해주세요.
셀 주소의 참조 방식을 변경합니다.
$기호(참조 방식)의 동작원리는 아래 1분 영상 강의에서 알기 쉽게 정리했으니 참고하세요!
- 이제 완성된 수식을 아래쪽과 오른쪽으로 자동 채우기 하면, 여러 시트의 데이터가 한 번에 VLOOKUP으로 검색되어 정리됩니다.
여러 시트의 값이 한 번에 VLOOKUP 됩니다.
