SUMPRODUCT로 VLOOKUP 대체하기: 2차원 데이터를 더 강력하게 다루는 방법

엑셀에서 데이터를 검색할 때 많은 사용자들이 VLOOKUP 함수에 의존합니다. 하지만 오늘은 SUMPRODUCT를 활용한 더 강력한 대안을 소개해 드리겠습니다. 이 방법은 2차원 데이터를 직접 다룰 수 있어 VLOOKUP보다 더 유연하고 강력합니다.

SUMPRODUCT와 INDEX의 조합: 2차원 검색의 마법

VLOOKUP이 단방향 검색만 가능한 것과 달리, SUMPRODUCT와 INDEX를 조합하면 행과 열 값을 동시에 검색하여 교차점의 값을 찾을 수 있습니다. 이는 마치 엑셀 시트에서 눈으로 행과 열을 따라가 교차점을 찾는 것과 같은 효과를 자동화합니다.

작동 원리

이 함수는 다음과 같이 작동합니다:

1. 행 값(예: "뉴진스")을 검색하여 해당 ROW() 값을 반환
2. 열 값(예: "맴버수")을 검색하여 해당 COLUMN() 값을 반환
3. INDEX 함수를 사용하여 두 값이 교차하는 셀의 값을 반환

실제 사용 예시

예를 들어, C7 셀에:

=INDEX($A$1:$Z$1000,SUMPRODUCT(ROW($E$4:$H$9)*($E$4:$H$9=B7)),SUMPRODUCT(COLUMN($E$4:$H$9)*($E$4:$H$9=C6)))

이 함수를 입력하면:

• B7에 "뉴진스"가 있고
• C6에 "소속"이 있을 때
• 함수는 "뉴진스"의 행과 "소속"의 열이 교차하는 값인 "분쟁중"을 반환합니다.

함수 개선 버전

INDEX 함수와 SUMPRODUCT의 궁합을 개선한 버전도 있습니다:

=INDEX($A$1:$Z$1000,SUMPRODUCT(MAX(1,ROW($E$4:$H$9)*($E$4:$H$9=B7))),SUMPRODUCT(MAX(1,COLUMN($E$4:$H$9)*($E$4:$H$9=C6))))

이 개선된 버전은 더 안정적인 결과를 제공합니다.

VLOOKUP과의 차이점

이 방식은 VLOOKUP과 약간 다른 특성을 가집니다:

• VLOOKUP은 첫 번째 일치 값을 반환하지만, 이 방식은 가장 마지막에 있는 값을 반환합니다.
• 2차원 검색이 가능해 행과 열을 동시에 참조할 수 있습니다.
• 데이터 영역만 지정하면 되므로 사용이 간편합니다.

사용 팁

1. 데이터 영역($E$4:$H$9)만 지정하면 됩니다. 이 부분을 자신의 데이터에 맞게 변경하세요.
2. 입력 위치는 컨트롤+X를 이용해 필요에 따라 자유롭게 변경할 수 있습니다.
3. Excel 2021 이상 버전이라면 LET 함수를 활용해 더 간결하게 작성할 수 있습니다.

결론

이 SUMPRODUCT와 INDEX를 조합한 방식은 VLOOKUP의 한계를 뛰어넘어 2차원 데이터를 더 효과적으로 다룰 수 있게 해줍니다. 특히 문자 데이터를 2차원으로 검색해야 할 때 놀라운 성능을 발휘합니다.

한 번 사용해보시면 그 강력함에 놀라실 것입니다. 이렇게 유용한 기능이 왜 더 널리 알려지지 않았는지 오히려 신기할 정도입니다.

추가 사용 예시

다른 위치에 적용하고 싶다면:

=INDEX($A$1:$Z$1000,SUMPRODUCT(ROW($E$4:$H$9)*($E$4:$H$9=B1)),SUMPRODUCT(COLUMN($E$4:$H$9)*($E$4:$H$9=C1)))

이 함수를 엑셀 작업에 적용해보세요. 복잡해 보이지만, 실제로는 데이터 영역만 지정하면 되기 때문에 사용하기 매우 쉽습니다.