VLOOKUP MATCH 함수 결합, INDEX 활용

VLOOKUP은 열 번호를 고정해야 하는 단점이 있습니다. 열 번호가 고정되어 있을 경우, 데이터 구조가 변경되면 수식을 다시 수정해야 하는 불편함이 발생합니다. 이를 해결하기 위해 VLOOKUP과 MATCH 함수를 결합하면 동적인 열 인덱스를 설정할 수 있습니다. 이번 글에서는 VLOOKUP과 MATCH 함수를 결합하여 보다 유연하게 데이터를 검색하는 방법을 살펴보겠습니다.

1. VLOOKUP의 기본적인 동작 원리

우선 VLOOKUP 함수는 기본적으로 첫 번째 열에서 특정 값을 찾아, 그 값과 같은 행에 있는 다른 열의 값을 반환하는 함수입니다. 예를 들어, 아래와 같은 데이터에서 ‘사원 이름’에 해당하는 사원의 ‘부서’를 찾을 때 VLOOKUP을 사용할 수 있습니다.

사원 ID사원 이름부서

번호	이름	부서
001	홍길동	인사팀
002	김철수	영업팀
003	이영희	개발팀

이 수식은 ‘홍길동’을 찾아 해당 행의 세 번째 열(부서)을 반환합니다. 하지만 여기서 문제는 데이터가 확장되거나 열의 위치가 바뀌면, 세 번째 열 번호를 일일이 수정해야 한다는 점입니다. 이때 MATCH 함수를 결합하면 이러한 불편함을 해결할 수 있습니다.

2. MATCH 함수의 동작 원리

MATCH 함수는 특정 값이 배열 또는 범위 내에서 몇 번째에 위치하는지를 반환하는 함수입니다. VLOOKUP과 함께 사용할 때 MATCH 함수는 검색하고자 하는 열의 번호를 동적으로 계산하는 데 사용됩니다.

예를 들어, 위의 데이터에서 ‘부서’ 열이 몇 번째 열에 있는지 알아내려면 MATCH 함수를 다음과 같이 사용할 수 있습니다.

이 수식은 A1에서 C1 범위 내에서 ‘부서’가 몇 번째에 위치하는지 반환합니다. 이 경우 결과는 ‘3’입니다.

3. VLOOKUP과 MATCH 결합하여 동적 열 인덱스 구현

이제 VLOOKUP과 MATCH 함수를 결합하여, 데이터 구조가 변해도 수식을 수정하지 않고 사용할 수 있는 방법을 알아보겠습니다. 앞서 VLOOKUP에서 열 번호를 수동으로 입력했던 것을 MATCH 함수로 대체해 동적으로 처리하는 방식입니다.

예시 1: 기본 사용법

아래는 VLOOKUP과 MATCH를 결합한 수식입니다. 예를 들어, ‘홍길동’의 부서를 찾을 때 VLOOKUP 함수에서 열 번호를 동적으로 계산하도록 MATCH 함수를 사용합니다.

이 수식은 ‘홍길동’을 A2

범위에서 찾아, A1범위에서 ‘부서’가 몇 번째 열에 있는지 계산한 후 해당 열에 있는 값을 반환합니다. 이제 열의 위치가 바뀌더라도 MATCH 함수가 자동으로 새로운 위치를 계산해주기 때문에, 열 번호를 일일이 수정할 필요가 없습니다.

예시 2: 복잡한 데이터에서의 활용

MATCH 함수는 열뿐만 아니라 행도 동적으로 처리할 수 있습니다. 예를 들어, 아래와 같이 직원들의 판매 기록이 있는 데이터에서 특정 직원의 특정 월 판매 데이터를 검색한다고 가정해봅시다.

사원 이름1월2월3월

이름	1월	2월	3월
홍길동	100	120	130
김철수	110	140	150
이영희	90	100	110

이 경우, 사원의 이름과 월을 기준으로 동적으로 데이터를 검색하기 위해서는 VLOOKUP과 MATCH 함수를 함께 사용할 수 있습니다.

이 수식은 ‘홍길동’의 2월 판매 데이터를 동적으로 찾아냅니다. MATCH 함수는 ‘2월’이 몇 번째 열에 있는지를 계산하고, 그 값을 VLOOKUP 함수가 사용하여 해당하는 데이터를 반환합니다.

4. 실무에서의 활용 사례

1) 재무 분석

재무 데이터를 관리할 때, 열의 위치가 변경될 가능성이 있는 경우 VLOOKUP과 MATCH 함수를 결합하면 유연하게 데이터를 조회할 수 있습니다. 예를 들어, 특정 계정 코드의 거래 내역을 검색할 때, 계정 코드가 다른 위치로 이동해도 MATCH 함수가 새로운 위치를 동적으로 찾아내어 정확한 데이터를 반환할 수 있습니다.

2) 인사 관리

인사 데이터를 관리할 때, 직원 정보가 여러 열에 나열되어 있는 경우, 직원 ID나 이름을 기준으로 특정 열(예: 부서, 직급 등)의 정보를 자동으로 검색하는 데 활용할 수 있습니다.

3) 대규모 데이터 처리

대규모 데이터베이스에서 열이 추가되거나 삭제될 때, MATCH 함수로 열 번호를 동적으로 계산하여 VLOOKUP을 사용하는 것이 매우 유용합니다. 이를 통해 수식 관리가 용이해지고, 실수 없이 데이터를 빠르게 검색할 수 있습니다.

5. VLOOKUP과 MATCH 함수의 장단점

장점

• 유연성: 데이터 구조가 변경되더라도 MATCH 함수를 사용하여 자동으로 열 번호를 계산하기 때문에, 수식을 일일이 수정할 필요가 없습니다.
• 효율성: 대규모 데이터에서 열이 추가되거나 삭제되어도 VLOOKUP과 MATCH 결합 수식을 사용하면 쉽게 데이터를 조회할 수 있습니다.
• 단순성: INDEX-MATCH의 복잡함 없이도 쉽게 동적 데이터 검색을 구현할 수 있습니다.

단점

• 다른 수식에 비해 복잡할 수 있음: MATCH 함수를 결합하는 방법이 다소 복잡해 보일 수 있어, 엑셀 초보자에게는 익숙해지기까지 시간이 걸릴 수 있습니다.
• 행 인덱스 제한: VLOOKUP 함수는 기본적으로 데이터를 위에서 아래로 검색하는 방식이기 때문에, 행 단위의 검색에는 한계가 있습니다. 이러한 경우에는 INDEX-MATCH 결합을 고려하는 것이 좋습니다.

6. 결론

VLOOKUP과 MATCH 함수를 결합하면 고정된 열 번호로 인해 발생하는 불편함을 해결하고, 보다 유연하게 데이터를 검색할 수 있습니다. 특히, 데이터가 커지거나 열의 위치가 변경되는 경우에 유용하며, 실무에서 재무, 인사, 판매 등의 데이터를 처리할 때 매우 강력한 도구가 될 수 있습니다. 엑셀을 더 효율적으로 사용하고자 한다면, VLOOKUP과 MATCH 함수를 결합한 방법을 익혀 실무에 적용해보세요!

 

2024.10.21 - [분류 전체보기] - VLOOKUP 다른 시트 및 파일에서 데이터 검색하기

VLOOKUP 다른 시트 및 파일에서 데이터 검색하기

 

2024.10.21 - [분류 전체보기] - 엑셀 VLOOKUP 다중조건

엑셀 VLOOKUP 다중조건

 

2024.10.21 - [분류 전체보기] - 엑셀 VLOOKUP 함수 완벽 가이드

엑셀 VLOOKUP 함수 완벽 가이드