엑셀 VLOOKUP으로 여러 시트 데이터 조회하는 3가지 방법

필요한 모든 정보가 하나의 워크시트에만 있는 경우는 드뭅니다. 완전한 데이터베이스를 만들려면 엑셀에서 다른 시트의 데이터를 불러와야 하는 상황이 자주 발생하죠. 이런 번거로움을 해결하기 위해 마이크로소프트는 VLOOKUP이라는 강력한 함수를 제공합니다. 이 함수를 활용하면 여러 시트에 걸쳐 데이터를 손쉽게 조회할 수 있습니다. 이 글에서는 VLOOKUP 함수를 사용해 엑셀에서 여러 시트를 조회하는 3가지 방법을 자세히 알아보겠습니다.

연습용 워크북 다운로드

아래 첨부된 엑셀 파일을 내려받아 직접 따라 해 보시는 것을 추천합니다.

엑셀에서 여러 시트를 조회하는 3가지 방법

예를 들어, 한 서점이 온라인과 오프라인 매장에서 동시에 책을 판매한다고 가정해 보겠습니다. 이 서점에는 온라인 판매용 도서 목록과 매장 판매용 도서 목록, 두 개의 목록이 있습니다.

이 튜토리얼에서는 이 두 개의 도서 목록을 하나의 완성된 통합 목록으로 합치는 3가지 방법을 소개합니다.

1. IFERROR 함수로 여러 시트 조회하기

온라인과 매장에서 판매 가능한 책이 모두 포함된 완성된 도서 목록을 만들려면 "Store" 시트와 "Online" 시트의 정보를 결합해야 합니다.

아래 단계를 따라 조회 방법을 익혀 보세요.

🔗 단계:

❶ 먼저 수식 결과를 저장할 셀 C5를 선택합니다.

❷ 그다음 아래 수식을 입력합니다.

❸ ENTER 키를 누릅니다.

❹ 채우기 핸들(Fill Handle)을 끌어 도서명(Book Name) 열 끝까지 적용합니다.

이것으로 완료입니다.

💡 저자(Author) 열까지 완성하려면 셀 D5에 아래 수식을 입력하고 위의 1~4단계를 반복하세요.

␥ 수식 분석

📌 구문: IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "Not found"))

• B5 ▶ 검색 키로 사용할 ID를 가져옵니다.
• Store!$B$5:$D$9 ▶ Store 시트의 B5부터 D9 범위 내에서 검색을 수행합니다.
• Online!$B$5:$D$9 ▶ Online 시트의 B5부터 D9 범위 내에서 검색을 수행합니다.
• 2 ▶ 도서명이 있는 열 번호로, 해당 열의 값을 반환합니다.
• FALSE ▶ 검색 시 정확히 일치하는 값만 찾도록 지정합니다.
• =IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found")) ▶ ID가 96인 도서명을 반환합니다.

더 읽어보기: 엑셀에서 여러 값을 조회해 하나의 셀에 연결하여 반환하는 방법

2. INDIRECT 함수로 여러 시트 VLOOKUP하기

IFERROR 함수 대신 INDIRECT 함수를 사용해서도 여러 시트를 조회할 수 있습니다. 다만 INDIRECT 함수는 여러 시트에서 데이터를 가져올 때 훨씬 유연하지만, 구문이 더 복잡하다는 점을 유의해야 합니다. 따라서 INDIRECT 함수를 VLOOKUP 함수와 함께 사용할 때는 주의가 필요합니다. 바로 단계로 들어가 보겠습니다.

🔗 단계:

❶ 먼저 수식 결과를 저장할 셀 C5를 선택합니다.

❷ 그다음 아래 수식을 입력합니다.

❸ ENTER 키를 누릅니다.

❹ 채우기 핸들을 끌어 도서명 열 끝까지 적용합니다.

이것으로 완료입니다.

💡 저자 열까지 완성하려면 셀 D5에 아래 수식을 입력하고 1~4단계를 반복하세요.

␥ 수식 분석

📌 구문: VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(TRUE, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!lookup_range"), lookup_value)>0), 0)) & "'!table_array"), col_index_num, FALSE)

• Lookup_value ▶ $B5 ▶ 검색의 기준이 되는 검색 키워드입니다.
• Lookup_sheets ▶ $F$5:$F$6 ▶ 데이터를 조회할 시트 이름 목록의 셀 주소입니다.
• Lookup_range ▶ $B5:$B9 ▶ 조회 값이 위치한 범위입니다.
• Table_array ▶ $B$5:$D$9 ▶ 전체 데이터 테이블의 범위입니다.
• Column_index_number ▶ 2 ▶ 원하는 데이터를 가져올 열 번호입니다.

더 읽어보기: 엑셀 VBA INDEX MATCH 다중 조건 활용법 (3가지 방법)

3. 중첩 IF 함수로 여러 시트 조회하기

여러 시트를 조회하는 또 다른 방법은 ISNA 및 VLOOKUP 함수와 함께 중첩 IF(Nested IF) 함수를 사용하는 것입니다.

데이터를 가져올 시트가 몇 개 되지 않는다면 이 방법을 쉽게 사용할 수 있지만, 그렇지 않다면 권장하지 않습니다. 시트 수가 많아질수록 수식이 매우 복잡해지기 때문입니다.

그럼에도 불구하고 수식이 어떻게 작동하는지 아래 단계를 통해 확인해 보세요.

🔗 단계:

❶ 먼저 수식 결과를 저장할 셀 C5를 선택합니다.

❷ 그다음 아래 수식을 입력합니다.

❸ ENTER 키를 누릅니다.

❹ 채우기 핸들을 끌어 도서명 열 끝까지 적용합니다.

이것으로 완료입니다.

💡 저자 열까지 완성하려면 셀 D5에 아래 수식을 입력하고 1~4단계를 반복하세요.

␥ 수식 분석

📌 구문: IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_number,0)),value_if_true,value_if_false)

• Lookup_value ▶ $B5 ▶ 검색의 기준이 되는 검색 키워드입니다.
• Table_array ▶ $B$5:$D$9 ▶ 전체 데이터 테이블의 범위입니다.
• Column_index_number ▶ 2 ▶ 원하는 데이터를 가져올 열 번호입니다.
• ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ $B5가 참조하는 ID가 범위 $B$5:$D$9 내에 존재하는지 교차 검색합니다.
• IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) ▶ ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0))가 참이면 VLOOKUP($B5,Online!$B$5:$D$9,3,0)을 통해 해당 도서명을 가져옵니다.
• IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) 부분이 거짓이면 IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0))로 넘어갑니다.
• IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0) ▶ ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0))가 참이면 VLOOKUP($B5,Store!$B$5:$D$9,3,0)을 통해 도서명을 가져옵니다.

더 읽어보기: 엑셀에서 날짜 범위를 포함한 다중 조건 VLOOKUP (2가지 방법)

기억해야 할 사항

📌 조회 값(lookup value)은 항상 테이블 배열(table array)의 첫 번째 열에 있어야 합니다.

📌 배열 수식을 완성하려면 Ctrl + Shift + Enter를 함께 누르세요.

📌 함수의 구문에 항상 주의하세요.

📌 수식에 데이터 범위를 정확하게 입력해야 합니다.

결론

지금까지 엑셀에서 여러 시트에 걸쳐 데이터를 조회하는 3가지 방법을 살펴보았습니다. 글과 함께 첨부된 연습용 워크북을 내려받아 모든 방법을 직접 실습해 보시길 권장합니다. 궁금한 점이 있다면 아래 댓글로 언제든 질문해 주세요. 관련 문의에 최대한 빠르게 답변드리겠습니다.

함께 읽으면 좋은 글

• 엑셀에서 보조 열 없이 다중 조건 VLOOKUP 사용하기 (5가지 방법)
• 날짜 범위 조건에 INDEX MATCH 다중 조건 활용하는 방법
• 엑셀에서 부분 텍스트 다중 조건 INDEX-MATCH 사용하기 (2가지 방법)
• 엑셀 XLOOKUP 다중 조건 활용법 (4가지 쉬운 방법)