엑셀 시트 간 데이터 연동: VLOOKUP, INDEX & MATCH 기능 활용 가이드
엑셀은 다양한 데이터를 효율적으로 관리하는 데 유용한 도구입니다. 하지만 여러 시트에 분산된 데이터를 하나의 시트에서 통합하여 사용하려면 번거로운 작업이 필요합니다. 이때 VLOOKUP, INDEX & MATCH 기능을 사용하면 쉽고 효과적으로 다른 시트의 값을 가져와 활용할 수 있습니다. 이 글에서는 엑셀 시트 간 데이터 연동을 위한 핵심 기능들을 자세히 살펴보고, 실제 활용 예시를 통해 이해를 돕겠습니다.
1, VLOOKUP: 간편한 데이터 찾기 및 가져오기
VLOOKUP은 가장 널리 사용되는 엑셀 함수 중 하나로, 특정 값을 기준으로 다른 열의 값을 가져오는 데 사용됩니다. 예를 들어, 고객 목록 시트에서 고객 이름을 기준으로 주문 내역 시트에서 해당 고객의 주문 정보를 가져오는 데 사용할 수 있습니다.
1.1 VLOOKUP 함수 사용 방법
VLOOKUP 함수는 다음과 같은 구문을 사용합니다.
VLOOKUP(lookupvalue, tablearray, colindexnum, [range_lookup])
- lookup_value: 찾을 값 (예: 고객 이름).
- table_array: 데이터가 포함된 테이블 범위 (예: 주문 내역 시트).
- colindexnum: 찾을 값이 있는 열의 인덱스 번호 (예: 주문 내역 시트에서 고객 이름이 있는 열의 인덱스).
- range_lookup: (선택 사항) 찾을 값을 찾는 방식 (TRUE: 근사값 찾기, FALSE: 정확한 일치 값 찾기).
1.2 VLOOKUP 함수 활용 예시
예시 1: 고객 목록 시트에서 고객 이름을 기준으로 주문 내역 시트에서 주문 금액을 가져오기
- 고객 목록 시트: A열 (고객 이름), B열 (주문 금액)
- 주문 내역 시트: A열 (주문 번호), B열 (고객 이름), C열 (주문 금액)
excel
=VLOOKUP(A2, ‘주문 내역’!A:C, 3, FALSE)
위 공식에서:
- A2: 고객 목록 시트에서 고객 이름이 있는 셀 (예: A2 셀에 “김철수”라는 이름이 있다면 A2를 입력합니다).
- ‘주문 내역’!A:C: 주문 내역 시트의 A열부터 C열까지 범위를 지정 (데이터가 포함된 테이블 범위).
- 3: 주문 금액이 있는 열의 인덱스 번호 (C열은 3번째 열).
- FALSE: 정확한 일치 값을 찾도록 지정 (주문 내역 시트의 고객 이름과 정확히 일치하는 값을 찾습니다).
예시 2: 제품 목록 시트에서 제품 이름을 기준으로 재고 현황 시트에서 재고 수량을 가져오기
- 제품 목록 시트: A열 (제품 이름), B열 (재고 수량)
- 재고 현황 시트: A열 (제품 코드), B열 (제품 이름), C열 (재고 수량)
excel
=VLOOKUP(A2, ‘재고 현황’!A:C, 3, FALSE)
위 공식에서:
- A2: 제품 목록 시트에서 제품 이름이 있는 셀 (예: A2 셀에 “아이폰 14″라는 제품 이름이 있다면 A2를 입력합니다).
- ‘재고 현황’!A:C: 재고 현황 시트의 A열부터 C열까지 범위를 지정 (데이터가 포함된 테이블 범위).
- 3: 재고 수량이 있는 열의 인덱스 번호 (C열은 3번째 열).
- FALSE: 정확한 일치 값을 찾도록 지정 (재고 현황 시트의 제품 이름과 정확히 일치하는 값을 찾습니다).
2, INDEX & MATCH: 유연한 데이터 연동
INDEX & MATCH 함수는 VLOOKUP보다 더 유연하고 다양한 활용이 가능한 데이터 연동 방법입니다. VLOOKUP은 찾을 값이 항상 첫 번째 열에 있어야 합니다. 즉, VLOOKUP에서는 찾을 값이 있는 열이 고정되어 있습니다. 반면 INDEX & MATCH는 찾을 값이 있는 열이 어디에 있든 상관없이 다른 시트의 값을 가져올 수 있습니다.
2.1 INDEX & MATCH 함수 사용 방법
INDEX & MATCH 함수는 다음과 같은 구문을 사용합니다.
INDEX(array, rownum, [columnnum])
- array: 데이터가 포함된 범위 (예: 주문 내역 시트).
- row_num: 찾을 값이 있는 행의 인덱스 번호 (MATCH 함수로 구합니다).
- column_num: 찾을 값이 있는 열의 인덱스 번호.
MATCH(lookupvalue, lookuparray, [match_type])
- lookup_value: 찾을 값 (예: 고객 이름).
- lookup_array: 찾을 값이 있는 범위 (예: 주문 내역 시트의 고객 이름이 있는 열).
- match_type: (선택 사항) 찾을 값을 찾는 방식 (1: 가장 큰 값보다 작거나 같은 값 찾기, 0: 정확한 일치 값 찾기, -1: 가장 작은 값보다 크거나 같은 값 찾기).
2.2 INDEX & MATCH 함수 활용 예시
예시 1: 고객 목록 시트에서 고객 이름을 기준으로 주문 내역 시트에서 주문 금액을 가져오기
- 고객 목록 시트: A열 (고객 이름), B열 (주문 금액)
- 주문 내역 시트: A열 (주문 번호), B열 (고객 이름), C열 (주문 금액)
excel
=INDEX(‘주문 내역’!C:C, MATCH(A2, ‘주문 내역’!B:B, 0))
위 공식에서:
- ‘주문 내역’!C:C: 주문 내역 시트의 C열 (주문 금액이 있는 열).
- MATCH(A2, ‘주문 내역’!B:B, 0): 고객 목록 시트의 A2 셀 (고객 이름)이 주문 내역 시트의 B열 (고객 이름)에서 어느 행에 있는지 찾아 행 인덱스 번호를 반환합니다.
- 0: 정확한 일치 값을 찾도록 지정 (주문 내역 시트의 고객 이름과 정확히 일치하는 값을 찾습니다).
예시 2: 제품 목록 시트에서 제품 이름을 기준으로 재고 현황 시트에서 재고 수량을 가져오기
- 제품 목록 시트: A열 (제품 이름), B열 (재고 수량)
- 재고 현황 시트: A열 (제품 코드), B열 (제품 이름), C열 (재고 수량)
excel
=INDEX(‘재고 현황’!C:C, MATCH(A2, ‘재고 현황’!B:B, 0))
위 공식에서:
- ‘재고 현황’!C:C: 재고 현황 시트의 C열 (재고 수량이 있는 열).
- MATCH(A2, ‘재고 현황’!B:B, 0): 제품 목록 시트의 A2 셀 (제품 이름)이 재고 현황 시트의 B열 (제품 이름)에서 어느 행에 있는지 찾아 행 인덱스 번호를 반환합니다.
- 0: 정확한 일치 값을 찾도록 지정 (재고 현황 시트의 제품 이름과 정확히 일치하는 값을 찾습니다).
3, VLOOKUP vs. INDEX & MATCH: 어떤 기능을 사용해야 할까요?
VLOOKUP과 INDEX & MATCH는 모두 다른 시트의 값을 가져오는 데 사용할 수있는 유용한 도구입니다. 그러나 **