엑셀 VLOOKUP과 INDEX&MATCH로 원하는 값 쉽게 찾기

엑셀에서 데이터를 효율적으로 관리하고 찾아야 할 때, 다양한 함수들이 도움을 줄 수 있습니다. 특히, VLOOKUP과 INDEX, MATCH 함수는 데이터 검색에 매우 유용한 도구입니다.

이번 글에서는 이 두 가지 방법을 통해 원하는 값을 쉽게 찾는 방법을 살펴보겠습니다.

VLOOKUP 함수의 이해

VLOOKUP 함수는 'Vertical Lookup'의 약자로, 세로 방향으로 데이터를 조회하는 기능을 제공합니다. 이 함수는 원하는 값을 찾기 위해 특정 범위에서 첫 번째 열을 검색하고, 이와 관련된 다른 열에서 값을 반환합니다.

VLOOKUP 함수의 기본적인 문법은 다음과 같습니다.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: 찾고자 하는 값 또는 셀 참조입니다.
• table_array: 검색할 데이터 범위입니다.
• col_index_num: 반환할 값이 있는 열의 번호입니다.
• range_lookup: TRUE 또는 FALSE 값으로, TRUE는 근사값을, FALSE는 정확한 값을 찾도록 설정합니다.

예를 들어, 특정 상품 코드에 해당하는 상품명을 찾고자 할 때, VLOOKUP 함수를 아래와 같이 사용할 수 있습니다.

=VLOOKUP("A005", A1:C10, 2, FALSE)

위 예제에서 "A005"라는 상품 코드를 A1:C10 범위에서 검색하고, 해당 상품 코드와 연관된 두 번째 열의 값을 반환합니다. 그러나 VLOOKUP 함수는 몇 가지 제한 사항이 있습니다.

첫 번째 열에서만 검색이 가능하고, 검색할 값이 포함된 열은 항상 반환할 값이 있는 열의 왼쪽에 위치해야 합니다.

상품 코드	상품명	단가
A001	키보드	30,000
A002	마우스	15,000
A003	모니터	200,000
A004	프린터	120,000
A005	마우스패드	6,500

위의 표에서 VLOOKUP을 사용하여 상품 코드 'A005'에 해당하는 상품명을 찾는 것이 가능합니다. 그러나 만약 상품 코드가 다른 열에 위치한다면, VLOOKUP은 사용할 수 없습니다.

다른 내용도 보러가기 #1

INDEX와 MATCH 함수의 조합

INDEX와 MATCH 함수는 함께 사용하여 VLOOKUP의 제한 사항을 극복할 수 있는 강력한 조합입니다. 이 두 함수는 서로 보완적인 역할을 하며, 보다 유연한 데이터 검색을 가능하게 합니다.

INDEX 함수의 원리

INDEX 함수는 특정 범위에서 지정한 행과 열의 교차점에 있는 값을 반환합니다. INDEX 함수의 기본 문법은 다음과 같습니다.

=INDEX(array, row_num, [column_num])

• array: 값이 포함된 범위입니다.
• row_num: 반환할 값이 위치한 행 번호입니다.
• column_num: 반환할 값이 위치한 열 번호입니다.

예를 들어, 상품명 '마우스패드'를 찾기 위해 INDEX 함수를 사용할 수 있습니다.

=INDEX(B2:B6, 5)

이 수식은 B2:B6 범위의 5번째 행에 있는 값을 반환하여 '마우스패드'를 찾습니다.

MATCH 함수의 원리

MATCH 함수는 특정 값이 범위에서 몇 번째 위치에 있는지를 반환합니다. MATCH 함수의 기본 문법은 다음과 같습니다.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: 찾고자 하는 값입니다.
• lookup_array: 검색할 범위입니다.
• match_type: 일치 유형으로, 0은 정확한 일치를 찾고, 1은 가장 큰 값보다 작거나 같은 값을 찾습니다.

예를 들어, 상품 코드 'A005'의 위치를 찾기 위해 다음과 같이 사용할 수 있습니다.

=MATCH("A005", D2:D6, 0)

이 수식은 D2:D6 범위에서 'A005'의 위치를 찾아 반환합니다.

INDEX와 MATCH 함수의 결합

이제 INDEX와 MATCH 함수를 결합하여 원하는 값을 찾아보겠습니다. 상품 코드 'A005'에 해당하는 상품명을 찾기 위해 다음과 같이 수식을 작성할 수 있습니다.

=INDEX(B2:B6, MATCH("A005", D2:D6, 0))

이 수식은 MATCH 함수가 'A005'의 위치를 찾고, 그 위치를 INDEX 함수에 전달하여 해당 위치의 상품명을 반환합니다. 이렇게 하면 VLOOKUP의 한계를 극복하고, 보다 유연하게 데이터를 검색할 수 있습니다.

상품 코드	상품명	단가
A001	키보드	30,000
A002	마우스	15,000
A003	모니터	200,000
A004	프린터	120,000
A005	마우스패드	6,500

위의 표를 참고하여 상품 코드에 해당하는 상품명을 찾는 과정을 정리해보면, MATCH 함수가 'A005'의 위치를 5로 찾고, 이후 INDEX 함수가 B열에서 5번째 행의 값을 반환하게 됩니다. 결과적으로 '마우스패드'라는 상품명이 반환됩니다.

실무에서의 활용

실무에서 INDEX와 MATCH 함수를 활용하는 방법은 다양합니다. 예를 들어, 판매 실적 자료를 작성할 때 상품 코드에 따라 상품명과 단가를 자동으로 불러올 수 있습니다.

이 경우, 상품 코드는 이미 입력되어 있고, 상품명과 단가는 다른 테이블에서 가져와야 할 때 INDEX와 MATCH의 조합이 매우 유용합니다. 판매 실적 자료에서 상품명을 가져오는 수식은 다음과 같습니다.

=INDEX($B$2:$B$6, MATCH(C2, $D$2:$D$6, 0))

여기서 C2는 상품 코드가 입력된 셀입니다. 이 수식을 통해 C2에 입력된 상품 코드에 해당하는 상품명과 단가를 각각 가져올 수 있습니다.

단가를 가져오는 수식은 다음과 같이 작성할 수 있습니다.

=INDEX($C$2:$C$6, MATCH(C2, $D$2:$D$6, 0))

위의 수식을 통해 판매 실적 자료를 효율적으로 작성할 수 있으며, 필요할 때마다 수식을 복사하여 활용할 수 있습니다.

상품 코드	상품명	단가
A001	키보드	30,000
A002	마우스	15,000
A003	모니터	200,000
A004	프린터	120,000
A005	마우스패드	6,500

이와 같이 INDEX와 MATCH 함수를 활용하면 다양한 상황에서 원하는 값을 손쉽게 찾을 수 있습니다. 이러한 방법을 익혀두면 데이터 관리와 분석에 많은 도움이 될 것입니다.

다른 내용도 보러가기 #2

결론

엑셀에서 데이터를 효율적으로 찾고 관리하는 데 있어 VLOOKUP, INDEX, MATCH 함수는 매우 유용한 도구입니다. VLOOKUP의 한계를 극복하고 보다 유연하게 데이터를 처리하기 위해 INDEX와 MATCH의 조합을 활용하는 방법을 알아보았습니다.

이 두 가지 방법을 잘 활용하면 데이터 분석 및 관리에서 큰 효율성을 얻을 수 있을 것입니다. 엑셀에서 원하는 값을 쉽게 찾는 능력을 키우고, 실무에 적용해 보시기 바랍니다.

관련 영상