VLOOKUP – 여러 시트에서 한번에 찾기 – Excel 및 Google 스프레드시트
이 튜토리얼에서는 Excel 및 Google 스프레드시트의 여러 시트에 대해서 VLOOKUP을 수행하는 방법을 보여드립니다. 사용 중인 Excel 버전이 XLOOKUP을 지원하는 경우, 대신 XLOOKUP을 사용하는 것을 추천드립니다.
VLOOKUP 함수는 단일 데이터 집합에 대해서만 조회를 수행할 수 있습니다. 서로 다른 시트에 저장된 여러 데이터 집합에서 조회를 수행하려면 모든 데이터 집합을 하나의 집합으로 결합하거나 IFERROR 함수를 VLOOKUP과 함께 사용하여 단일 수식에서 여러 시트에 대한 조회를 수행할 수 있습니다.
후자의 방법은 Excel에서 간편하게 사용할 수 있고 전자의 방법은 Google 스프레드시트에서 더 쉽게 사용할 수 있습니다.
IFERROR를 사용한 VLOOKUP
VLOOKUP 함수(또는 기타 LOOKUP 수식)는 일치하는 항목을 찾을 수 없는 경우 오류를 반환합니다. 일반적으로 IFERROR 함수를 사용하면 오류를 사용자가 정의한 값으로 대체할 수 있습니다.
첫 번째 VLOOKUP이 첫 번째 시트에서 일치하는 항목을 찾을 수 없는 경우 IFERROR를 사용하여 다른 시트에서 또 다른 VLOOKUP을 수행하도록 수식을 구현합니다.
VLOOKUP – 한 번에 두 시트 조회하기
=IFERROR(VLOOKUP(B3,'Department A'!$B$3:$C$7,2,FALSE),VLOOKUP(B3,'Department B'!$B$3:$C$7,2,FALSE))
수식을 자세히 살펴보겠습니다:
VLOOKUP 함수
다음은 첫 번째 시트에 대한 VLOOKUP입니다:
=VLOOKUP(B3,'Dept. A'!$B$3:$C$7,2,FALSE)
참고: VLOOKUP 함수는 테이블의 첫 번째 열에서 조회 값을 검색하고 정의된 컬럼 인덱스(함수의 세 번째 인수)에서 해당 값을 반환합니다. 마지막 인수는 일치 유형을 정의합니다(예: True – 근사치 일치, False – 정확히 일치).
다음은 두 번째 시트에 대한 VLOOKUP입니다:
=VLOOKUP(B3,'Dept. B'!$B$3:$C$7,2,FALSE)
IFERROR 함수
IFERROR 함수를 사용하여 두 VLOOKUP의 결과를 결합했습니다.
첫 번째 VLOOKUP(예: 부서 A)의 값을 확인합니다. 첫 번째 VLOOKUP 결과 값이 오류일 경우 두 번째 VLOOKUP의 결과 값을 반환합니다.
=IFERROR(E3,F3)
참고: IFERROR 함수는 값(즉, 첫 번째 인수)이 오류(예: #N/A, #REF!)인지 확인합니다. 오류인 경우 오류 두 번째 인수로 정의된 값을 반환합니다. 오류가 아니라면 첫 번째 인수의 값을 반환합니다..
이 모든 개념을 결합하면 원래의 수식이 됩니다:
=IFERROR(VLOOKUP(B3,'Department A'!$B$3:$C$7,2,FALSE),VLOOKUP(B3,'Department B'!$B$3:$C$7,2,FALSE))
VLOOKUP – 한 번에 두개 이상 시트 조회하기
시트가 두 개 이상인 경우 위의 공식에 추가 시트당 하나의 VLOOKUP과 하나의 IFERROR를 추가해야 합니다. 다음은 시트 3장에 대한 공식입니다:
=IFERROR(IFERROR(VLOOKUP(B3,'Dept. A'!$B$3:$C$7,2,FALSE),VLOOKUP(B3,'Dept. B'!$B$3:$C$7,2,FALSE)),VLOOKUP(B3,'Dept. C'!$B$3:$C$7,2,FALSE))
Google 스프레드시트에서 VLOOKUP을 사용하여 한 번에 여러 시트에서 값 조회하기
IFERROR와 VLOOKUP의 조합은 Google 스프레드시트에서 동일한 방식으로 작동하지만, 데이터 집합을 하나로 결합하는 것이 더 간단합니다.
중괄호 {}를 사용한 VLOOKUP(Google 스프레드시트)
중괄호를 사용하여 데이터 집합을 하나로 결합할 수 있습니다.
=VLOOKUP(B3,{'Department A'!$B$3:$C$7;'Department B'!$B$3:$C$7;'Department C'!$B$3:$C$7},2,FALSE)
수식을 분석하고 시각화해 보겠습니다:
중괄호 안의 세미콜론을 사용하여 셀 범위를 구분하여 모든 데이터 집합을 수직으로 쌓습니다:
={'Department A'!$B$3:$C$7;'Department B'!$B$3:$C$7;'Department C'!$B$3:$C$7}
참고: Google 스프레드시트의 중괄호는 셀 범위에서 배열을 만드는 데에도 사용할 수 있습니다. 세미콜론은 값을 세로로 쌓는 데 사용되며 쉼표는 값을 가로로 쌓는 데 사용됩니다. 셀 범위를 세로로 쌓을 경우 각 셀 범위의 열 개수가 동일한지 확인해야 합니다. 그렇지 않으면 작동하지 않습니다. 가로의 경우 행의 개수가 동일해야 합니다.
결과 배열은 VLOOKUP의 두 번째 인수로 사용됩니다:
=VLOOKUP(E3,$B$3:$C$17,2,FALSE)
이 모든 결과를 합치면 원래 수식이 됩니다:
=VLOOKUP(B3,{'Department A'!$B$3:$C$7;'Department B'!$B$3:$C$7;'Department C'!$B$3:$C$7},2,FALSE)
