VLOOKUP의 한계, INDEX와 MATCH 함수 조합으로 극복하기

엑셀을 사용하면서 데이터를 찾고 싶을 때 가장 먼저 떠올리는 함수는 무엇인가요? 아마 많은 분들이 VLOOKUP 함수를 떠올리실 겁니다. VLOOKUP은 특정 값을 기준으로 원하는 정보를 찾아주는 매우 유용한 함수입니다. 하지만 VLOOKUP 함수는 몇 가지 명확한 한계를 가지고 있습니다.

첫째, VLOOKUP 함수는 찾는 값이 항상 검색 범위의 가장 왼쪽 열에 있어야 한다는 제약이 있습니다. 만약 찾으려는 값이 오른쪽에 있다면 VLOOKUP 함수로는 직접 데이터를 가져올 수 없습니다. 둘째, VLOOKUP 함수는 열 번호를 직접 입력해야 합니다. 데이터 범위의 열이 추가되거나 삭제되면 열 번호를 다시 수정해야 하는 번거로움이 있습니다. 셋째, VLOOKUP 함수는 정확히 일치하는 값만 찾거나, 근사값을 찾을 때만 사용할 수 있어 유연성이 떨어집니다.

이러한 VLOOKUP 함수의 한계를 명쾌하게 해결해주는 강력한 조합이 있습니다. 바로 INDEX 함수와 MATCH 함수를 조합하는 것입니다. 이 두 함수를 함께 사용하면 VLOOKUP의 모든 단점을 극복하고 훨씬 더 강력하고 유연한 데이터 검색 및 추출이 가능해집니다.

INDEX 함수란 무엇인가요?

INDEX 함수는 이름 그대로 지정한 범위에서 특정 행과 열의 교차점에 있는 값을 반환하는 함수입니다. 간단히 말해, “이 표에서 몇 번째 행, 몇 번째 열에 있는 값을 보여줘”라고 요청하는 것과 같습니다.

INDEX 함수의 기본 구문은 다음과 같습니다.

INDEX(array, row_num, [column_num])

• array: 값을 가져올 셀 범위를 지정합니다.

• row_num: 가져올 값의 행 번호를 지정합니다.

• column_num (선택 사항): 가져올 값의 열 번호를 지정합니다. 이 인수를 생략하면 array가 단일 열 또는 단일 행인 경우 해당 행 또는 열의 값을 반환합니다.

예를 들어, A1:C5 범위에서 3행 2열에 있는 값을 가져오고 싶다면 INDEX(A1:C5, 3, 2)와 같이 사용할 수 있습니다.

MATCH 함수란 무엇인가요?

MATCH 함수는 지정한 범위에서 특정 값을 찾아 해당 값이 몇 번째에 있는지 그 위치(순서)를 반환하는 함수입니다. 즉, “이 목록에서 내가 찾는 값이 몇 번째에 있어?”라고 묻는 것과 같습니다.

MATCH 함수의 기본 구문은 다음과 같습니다.

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: 찾으려는 값입니다.

• lookup_array: 값을 찾을 범위를 지정합니다. 이 범위는 단일 행 또는 단일 열이어야 합니다.

• match_type (선택 사항): 일치 유형을 지정합니다.

• 1 또는 생략: lookup_value보다 작거나 같은 가장 큰 값. lookup_array는 오름차순으로 정렬되어야 합니다.

• 0: 정확히 일치하는 첫 번째 값. lookup_array는 정렬되지 않아도 됩니다.

• -1: lookup_value보다 크거나 같은 가장 작은 값. lookup_array는 내림차순으로 정렬되어야 합니다.

가장 많이 사용되는 match_type은 0 (정확히 일치)입니다. 예를 들어, A1:A10 범위에서 “사과”라는 값이 몇 번째에 있는지 찾고 싶다면 MATCH("사과", A1:A10, 0)와 같이 사용할 수 있습니다. 만약 “사과”가 3번째에 있다면 결과로 3이 반환됩니다.

INDEX와 MATCH 함수 조합의 마법

이제 INDEX 함수와 MATCH 함수의 역할을 이해하셨다면, 이 둘을 어떻게 조합하여 VLOOKUP의 한계를 뛰어넘는지 알 수 있습니다.

핵심은 MATCH 함수를 사용하여 찾고자 하는 값의 행 또는 열 위치를 동적으로 알아낸 다음, 그 위치 정보를 INDEX 함수에 전달하는 것입니다.

VLOOKUP 함수는 “찾는 값이 왼쪽에 있어야 하고, 열 번호를 직접 지정해야 한다”는 제약이 있었습니다. 하지만 INDEX와 MATCH 조합은 이러한 제약을 완전히 해소합니다.

1. 찾는 값이 어느 열에 있든 상관없습니다.

MATCH 함수를 사용하여 찾으려는 값의 열 위치를 알아낼 수 있으므로, 데이터 범위의 어느 위치에 있든 상관없이 해당 열의 데이터를 가져올 수 있습니다.

2. 열 번호를 직접 지정할 필요가 없습니다.

MATCH 함수가 동적으로 열 위치를 찾아주므로, 데이터가 변경되어도 열 번호를 일일이 수정할 필요가 없습니다.

3. 훨씬 더 유연한 검색이 가능합니다.

INDEX 함수와 MATCH 함수는 각각 행과 열을 독립적으로 지정할 수 있으므로, 데이터를 양방향으로 검색하는 데에도 매우 효과적입니다.

INDEX와 MATCH 함수를 조합한 기본 구문은 다음과 같습니다.

INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

• return_array: 최종적으로 값을 가져오고 싶은 열 또는 행 범위를 지정합니다.

• MATCH(lookup_value, lookup_array, 0): 찾고자 하는 값(lookup_value)이 lookup_array에서 몇 번째 위치에 있는지 정확히 찾아냅니다.

만약 행과 열 모두 동적으로 찾고 싶다면 다음과 같이 사용할 수 있습니다.

INDEX(array, MATCH(row_lookup_value, row_lookup_array, 0), MATCH(column_lookup_value, column_lookup_array, 0))

• array: 전체 데이터 범위를 지정합니다.

• 첫 번째 MATCH: 찾으려는 행의 위치를 동적으로 찾습니다.

• 두 번째 MATCH: 찾으려는 열의 위치를 동적으로 찾습니다.

INDEX와 MATCH 함수 조합, 실제 활용 사례

이제 INDEX와 MATCH 함수 조합이 왜 강력한지 이론적으로 이해하셨을 겁니다. 그렇다면 실제 업무에서 어떻게 활용할 수 있을까요? 다양한 시나리오를 통해 알아보겠습니다.

사례 1: VLOOKUP처럼 사용하기 (찾는 값이 오른쪽에 있을 때)

가장 기본적인 활용 사례입니다. VLOOKUP 함수는 찾는 값이 항상 검색 범위의 가장 왼쪽에 있어야 했지만, INDEX와 MATCH 조합은 이러한 제약이 없습니다.

상황: 직원 ID를 기준으로 직원 이름, 부서, 급여 정보를 가져오고 싶습니다. 직원 ID는 A열에 있고, 직원 이름은 B열, 부서는 C열, 급여는 D열에 있습니다. 그런데 만약 직원 이름(B열)을 기준으로 직원 ID(A열)를 찾고 싶다면 VLOOKUP으로는 불가능합니다.

데이터:

| 직원 ID (A) | 직원 이름 (B) | 부서 (C) | 급여 (D) |

| :———- | :———— | :——- | :——- |

| 101 | 홍길동 | 영업 | 3000000 |

| 102 | 김철수 | 마케팅 | 3500000 |

| 103 | 이영희 | 개발 | 4000000 |

목표: 직원 이름(예: “김철수”)을 입력하면 해당 직원의 급여(D열)를 가져오고 싶습니다.

해결 방법:

1. 직원 이름의 행 번호 찾기: MATCH("김철수", B2:B4, 0) -> 결과: 2 (B2:B4 범위에서 “김철수”는 2번째에 있습니다.)

2. 해당 행의 급여 가져오기: INDEX(D2:D4, 2) -> 결과: 3500000 (D2:D4 범위의 2번째 값)

최종 함수:

=INDEX(D2:D4, MATCH("김철수", B2:B4, 0))

만약 직원 이름을 E2 셀에 입력하고, 급여를 F2 셀에 표시하고 싶다면 다음과 같이 사용할 수 있습니다.

=INDEX(D2:D4, MATCH(E2, B2:B4, 0))

이처럼 INDEX와 MATCH 조합은 VLOOKUP의 “왼쪽에 있어야 한다”는 제약을 완벽하게 해결합니다.

사례 2: 양방향 검색 (행과 열 모두 찾기)

INDEX와 MATCH 조합의 진정한 강력함은 행과 열 모두 동적으로 검색할 수 있다는 점입니다. 이는 복잡한 데이터 표에서 특정 조건에 맞는 값을 정확하게 찾아낼 때 매우 유용합니다.

상황: 월별 판매 실적 데이터를 가지고 있습니다. 행에는 제품별로, 열에는 월별로 판매량이 기록되어 있습니다. 특정 제품의 특정 월 판매량을 조회하고 싶습니다.

데이터:

| 제품/월 | 1월 | 2월 | 3월 |

| :—— | :—– | :—– | :—– |

| 제품 A | 100 | 120 | 150 |

| 제품 B | 80 | 90 | 110 |

| 제품 C | 150 | 160 | 180 |

목표: 제품명(예: “제품 B”)과 월(예: “2월”)을 지정하면 해당 판매량(90)을 가져오고 싶습니다.

해결 방법:

1. 제품 이름의 행 번호 찾기: MATCH("제품 B", A2:A4, 0) -> 결과: 2 (A2:A4 범위에서 “제품 B”는 2번째)

2. 월 이름의 열 번호 찾기: MATCH("2월", B1:D1, 0) -> 결과: 2 (B1:D1 범위에서 “2월”은 2번째)

3. 찾은 행과 열의 교차점 값 가져오기: INDEX(B2:D4, 2, 2) -> 결과: 90 (B2:D4 범위에서 2행 2열의 값)

최종 함수:

=INDEX(B2:D4, MATCH("제품 B", A2:A4, 0), MATCH("2월", B1:D1, 0))

만약 제품명은 F2 셀에, 월은 G2 셀에 입력하고, 판매량은 H2 셀에 표시하고 싶다면 다음과 같이 사용할 수 있습니다.

=INDEX(B2:D4, MATCH(F2, A2:A4, 0), MATCH(G2, B1:D1, 0))

이처럼 INDEX와 MATCH 조합은 VLOOKUP으로는 불가능했던 양방향 검색을 아주 쉽게 구현할 수 있게 해줍니다.

사례 3: 여러 조건에 맞는 값 찾기 (SUMPRODUCT와 함께)

INDEX와 MATCH 조합 자체만으로는 여러 조건을 동시에 만족하는 값을 찾기 어렵습니다. 하지만 SUMPRODUCT 함수와 함께 사용하면 여러 조건을 만족하는 유일한 값을 효과적으로 찾을 수 있습니다. (만약 여러 행에 걸쳐 조건이 일치하는 경우, SUMPRODUCT는 그 합계를 반환합니다. 단일 결과가 보장된다면 유일한 값을 반환합니다.)

상황: 쇼핑몰의 주문 내역 데이터가 있습니다. 고객 ID, 상품명, 주문 수량, 주문 금액이 기록되어 있습니다. 특정 고객이 특정 상품을 주문한 총 금액을 알고 싶습니다.

데이터:

| 주문 ID | 고객 ID | 상품명 | 주문 수량 | 주문 금액 |

| :—— | :—— | :——- | :——– | :——– |

| 1 | C001 | 노트북 | 1 | 1200000 |

| 2 | C002 | 키보드 | 2 | 100000 |

| 3 | C001 | 마우스 | 1 | 30000 |

| 4 | C003 | 모니터 | 1 | 300000 |

| 5 | C001 | 키보드 | 1 | 50000 |

목표: 고객 ID “C001″이 “키보드”를 주문한 총 금액을 알고 싶습니다. (결과는 50000)

해결 방법:

SUMPRODUCT 함수는 배열 내의 곱셈 결과를 더해주는 함수입니다. 이 특징을 이용하여 여러 조건을 배열로 만들고, 해당 조건이 모두 참(TRUE, 1)인 행의 특정 열 값만 곱하여 합산할 수 있습니다.

=SUMPRODUCT((B2:B6="C001") * (C2:C6="키보드") * (E2:E6))

• (B2:B6="C001"): 고객 ID가 “C001″인 행은 TRUE(1), 아니면 FALSE(0) 배열을 만듭니다.

• (C2:C6="키보드"): 상품명이 “키보드”인 행은 TRUE(1), 아니면 FALSE(0) 배열을 만듭니다.

• *: 두 배열을 곱합니다. 두 조건이 모두 TRUE(1*1=1)인 경우만 1이 되고, 하나라도 FALSE이면 0이 됩니다.

• (E2:E6): 실제 주문 금액입니다.

• SUMPRODUCT: (조건 결과 * 주문 금액)의 합계를 계산합니다. 결과적으로 “C001″이면서 “키보드”인 행의 주문 금액만 합산됩니다.

이 방법은 INDEX/MATCH 조합과는 조금 다르지만, 여러 조건에 맞는 데이터를 추출하는 데 매우 효과적인 방법으로 함께 알아두면 좋습니다.

사례 4: 동적으로 열 제목 변경 (REPORTING)

데이터를 요약하여 보고서를 만들 때, 특정 항목의 열 제목을 동적으로 변경하고 싶을 때가 있습니다. 예를 들어, 매월 다른 달의 판매 실적을 보여주는 보고서를 만들 때, 보고서 상단의 월 이름을 동적으로 가져오는 경우입니다.

상황: 월별 판매 실적 데이터가 있고, 보고서 상단에 현재 조회하려는 월을 표시하고 싶습니다.

데이터:

| 제품/월 | 1월 | 2월 | 3월 |

| :—— | :—– | :—– | :—– |

| 제품 A | 100 | 120 | 150 |

| 제품 B | 80 | 90 | 110 |

목표: 조회할 월을 A1 셀에 입력하면, 해당 월의 제목을 B1 셀에 표시하고 싶습니다. (예: A1에 “2월” 입력 시 B1에 “2월 판매 실적” 표시)

해결 방법:

=A1 & " 판매 실적"

이것은 매우 간단하지만, 만약 INDEX/MATCH 조합을 사용하여 보고서의 특정 셀에 월 이름을 동적으로 가져오는 경우라면 다음과 같이 활용할 수 있습니다.

만약 A1 셀에 “2월”이라고 입력하고, B1 셀에 해당 월의 판매량 합계를 표시하고 싶다면:

1. “2월”의 열 번호 찾기: MATCH(A1, B1:D1, 0) -> 결과: 2

2. 해당 열의 판매량 합계 가져오기: SUM(INDEX(B2:D4, 0, MATCH(A1, B1:D1, 0)))

3. INDEX(B2:D4, 0, ...) : row_num을 0으로 지정하면 해당 열 전체를 의미합니다.

4. SUM(...): 해당 열 전체의 합계를 구합니다.

최종 함수 (B1 셀에 입력):

=SUM(INDEX(B2:D4, 0, MATCH(A1, B1:D1, 0)))

이처럼 INDEX와 MATCH 함수는 보고서의 동적인 표현을 구현하는 데에도 유용하게 사용될 수 있습니다.

INDEX와 MATCH 조합, 왜 VLOOKUP보다 강력한가?

지금까지 INDEX와 MATCH 함수의 원리와 다양한 활용 사례를 살펴보았습니다. 그렇다면 이 조합이 VLOOKUP보다 근본적으로 강력하다고 말할 수 있는 이유는 무엇일까요?

1. 유연성 (Flexibility):

2. 방향 제약 없음: VLOOKUP은 찾는 값이 항상 범위의 가장 왼쪽에 있어야 하지만, INDEX/MATCH는 데이터의 어느 위치에 있든 상관없이 검색이 가능합니다. 이는 데이터 구조가 자주 변경되거나, 특정 열의 데이터를 기준으로 다른 열의 데이터를 찾아야 할 때 매우 중요합니다.

3. 양방향 검색: 행과 열 모두 독립적으로 지정할 수 있어, 행렬 데이터를 다룰 때 훨씬 강력합니다. VLOOKUP은 기본적으로 행 방향 검색만 가능하며, 열 방향 검색을 위해서는 다른 함수와의 조합이 필요합니다.

4. 성능 (Performance):

5. 대규모 데이터셋: INDEX/MATCH 조합은 VLOOKUP보다 대규모 데이터셋에서 더 나은 성능을 보이는 경우가 많습니다. 특히 VLOOKUP의 col_index_num 인수가 큰 경우, INDEX/MATCH는 실제 필요한 열만 참조하므로 계산 속도가 빠를 수 있습니다. (물론 실제 성능은 데이터 구조, Excel 버전, 함수 사용 방식 등에 따라 달라질 수 있습니다.)

6. 가독성 및 유지보수 (Readability & Maintainability):

7. 동적 열 지정: VLOOKUP은 열 번호를 직접 숫자로 입력해야 하므로, 데이터가 변경되면 열 번호를 일일이 수정해야 합니다. 하지만 INDEX/MATCH 조합은 MATCH 함수를 통해 동적으로 열 위치를 찾으므로, 데이터 구조가 변경되어도 함수 수정이 최소화됩니다. 이는 장기적인 관점에서 스프레드시트 유지보수를 훨씬 용이하게 만듭니다.

8. 의도 명확성: INDEX(데이터 범위, 행 위치, 열 위치)와 MATCH(찾을 값, 찾을 범위, 0)의 조합은 “이 범위에서, 특정 행과 열이 만나는 값을 가져오는데, 그 행과 열의 위치는 동적으로 찾겠다”는 의도를 명확하게 보여줍니다.

9. 확장성 (Scalability):

10. 다중 조건 처리: SUMPRODUCT와 같은 다른 함수와의 조합을 통해 VLOOKUP으로는 구현하기 어려운 다중 조건 검색을 효과적으로 수행할 수 있습니다.

흔한 실수와 주의사항

INDEX와 MATCH 함수 조합은 매우 강력하지만, 몇 가지 주의해야 할 점이 있습니다.

• MATCH 함수의 match_type: 가장 흔한 실수는 match_type을 제대로 지정하지 않는 것입니다.

• 정확히 일치하는 값을 찾으려면 반드시 0으로 설정해야 합니다. 1이나 -1을 사용하면 데이터가 정렬되지 않았을 경우 예상치 못한 결과를 얻을 수 있습니다.

• match_type을 생략하면 기본값 1이 사용되는데, 이는 lookup_value보다 작거나 같은 가장 큰 값을 찾습니다. 따라서 lookup_array가 반드시 오름차순으로 정렬되어 있어야 합니다.

• 범위 지정 오류: INDEX 함수의 array와 MATCH 함수의 lookup_array 범위가 일치하지 않거나, 서로 다른 기준으로 설정되면 올바른 결과를 얻을 수 없습니다. 특히 MATCH 함수는 단일 행 또는 단일 열 범위만 지정해야 합니다.

• 행/열 번호 혼동: INDEX 함수에서 row_num과 column_num의 역할을 명확히 구분해야 합니다. MATCH 함수는 lookup_array 내에서의 상대적인 위치를 반환한다는 점을 기억해야 합니다.

• 대규모 데이터에서의 성능: 앞서 언급했듯이 대규모 데이터에서는 성능 차이가 발생할 수 있습니다. 항상 최적의 함수 조합을 고민해야 합니다.

결론: 엑셀 실력 향상을 위한 필수 스킬, INDEX와 MATCH

VLOOKUP 함수는 여전히 유용하지만, 엑셀을 좀 더 깊이 있게 활용하고 싶다면 INDEX와 MATCH 함수 조합은 반드시 익혀야 할 필수 스킬입니다. 이 두 함수를 조합하면 VLOOKUP의 여러 한계를 극복하고, 훨씬 더 유연하고 강력한 데이터 검색 및 추출이 가능해집니다.

오늘부터 당장 시도해보세요!

1. VLOOKUP으로 해결하기 어려웠던 데이터 검색 문제를 떠올려보세요.

2. INDEX 함수와 MATCH 함수를 각각 어떻게 사용할지 계획해보세요.

3. 두 함수를 조합하여 실제 엑셀 시트에 적용해보세요.

처음에는