VLOOKUP 보다 유용한 INDEX와 MATCH 함수

INDEX와 MATCH 함수는 함께 사용되면 VLOOKUP 함수가 가진 몇 가지 한계를 극복할 수 있는 매우 강력한 대안이 됩니다. 특히, VLOOKUP은 오른쪽에서 왼쪽으로 검색할 수 없고, 검색 범위가 고정된 열이어야 하는 등의 제한이 있는데, INDEX와 MATCH는 이러한 문제들을 해결할 수 있습니다. 아래에서는 각각의 함수 설명과 함께 VLOOKUP의 한계를 극복할 수 있는 예시와 사례를 소개하고자 합니다.

1. INDEX와 MATCH 함수란?

• INDEX(범위, 행 번호, [열 번호]): 주어진 범위에서 특정 행과 열의 교차점에 있는 값을 반환하는 함수입니다.
  • 예: INDEX(A1:C10, 3, 2)는 A1범위의 3행 2열에 해당하는 값을 반환합니다.
• MATCH(검색값, 검색범위, [일치 유형]): 특정 값이 검색 범위에서 몇 번째 위치에 있는지 반환하는 함수입니다.
  • 예: MATCH(50, A1:A10, 0)는 A1범위에서 50이라는 값이 몇 번째에 있는지 반환합니다. 여기서 0은 정확히 일치하는 값을 찾는다는 의미입니다.

2. VLOOKUP의 한계를 극복하는 방법

한계 1: VLOOKUP은 왼쪽에서 오른쪽으로만 검색할 수 있다.

VLOOKUP 함수는 기본적으로 검색하는 값이 왼쪽에 있고, 찾고자 하는 값이 오른쪽에 있어야 합니다. 예를 들어, **"ID"**를 기준으로 **"이름"**을 찾는 건 가능하지만, **"이름"**을 기준으로 **"ID"**를 찾는 것은 불가능합니다. 이때, INDEX와 MATCH는 자유롭게 왼쪽이나 오른쪽 어느 방향으로도 검색이 가능합니다.

예시: 이름을 기준으로 ID를 찾기

• 데이터 예시:
  • A열: 이름 (예: "김철수", "이영희")
  • B열: ID (예: 101, 102)

VLOOKUP을 사용할 경우:

=VLOOKUP("김철수", A2:B10, 2, FALSE)

여기서는 김철수가 **이름(A열)**에 있을 때, ID를 찾아낼 수 있지만, 만약 ID가 왼쪽에 있고 이름이 오른쪽에 있다면 VLOOKUP으로 해결할 수 없습니다.

 

INDEX와 MATCH를 사용할 경우:

=INDEX(B2:B10, MATCH("김철수", A2:A10, 0))

이 수식은 김철수를 A열에서 찾아, 해당하는 위치의 B열 값을 반환합니다. 이 방식은 왼쪽에서 오른쪽, 오른쪽에서 왼쪽 모두 검색할 수 있다는 장점이 있습니다.

한계 2: VLOOKUP은 열 번호가 고정된다.

VLOOKUP은 참조할 열을 고정된 숫자로 지정합니다(예: 2, 3등). 하지만 데이터 구조가 변하거나 새로운 열이 추가되면 열 번호를 수동으로 변경해야 하는 번거로움이 있습니다.

예시: 동적으로 열을 참조하기

데이터 예시:

• A열: 이름 (예: "김철수", "이영희")
• B열: 부서 (예: "영업", "개발")
• C열: 나이 (예: 30, 28)

VLOOKUP을 사용할 경우:

=VLOOKUP("김철수", A2:C10, 3, FALSE)

 

여기서 VLOOKUP은 **3번째 열(C열)**에서 값을 반환합니다. 그러나 새로운 열이 추가되어 데이터 구조가 변경되면 수식을 수정해야 합니다.

INDEX와 MATCH를 사용할 경우:

=INDEX(A2:C10, MATCH("김철수", A2:A10, 0), MATCH("나이", A1:C1, 0))

이 수식은 첫 번째 MATCH 함수로 이름이 A열에서 몇 번째에 있는지 찾고, 두 번째 MATCH 함수로 **"나이"**가 몇 번째 열에 있는지 찾습니다. 이를 통해 데이터 구조가 변경되더라도 열 번호를 동적으로 계산할 수 있어 더 유연합니다.

한계 3: VLOOKUP은 여러 기준으로 검색할 수 없다.

VLOOKUP은 하나의 열을 기준으로 값을 찾습니다. 하지만 여러 조건에 따라 값을 검색해야 할 때, VLOOKUP은 이를 처리할 수 없습니다.

예시: 이름과 부서를 기준으로 나이를 찾기

데이터 예시:

• A열: 이름 (예: "김철수", "이영희")
• B열: 부서 (예: "영업", "개발")
• C열: 나이 (예: 30, 28)

INDEX와 MATCH를 사용할 경우:

=INDEX(C2:C10, MATCH(1, (A2:A10="김철수")*(B2:B10="영업"), 0))

이 수식은 이름이 김철수이고 부서가 영업인 조건을 만족하는 행을 찾아서, 그 행의 나이를 반환합니다. 이처럼 여러 조건을 적용할 수 있어 더 정교한 검색이 가능합니다.

3. INDEX와 MATCH 함수 활용 사례

사례 1: 고객 데이터베이스에서 고객의 이메일을 기준으로 고객 ID 찾기

• 문제: 고객의 이메일을 기준으로 고객 ID를 찾고자 할 때, 이메일은 A열에 있고 고객 ID는 B열에 있습니다.
• 해결 방법: INDEX와 MATCH를 사용하여 이메일을 기준으로 고객 ID를 쉽게 찾을 수 있습니다.

=INDEX(B2:B100, MATCH("example@email.com", A2:A100, 0))

사례 2: 학생 성적표에서 과목 이름을 동적으로 참조하여 성적 가져오기

• 문제: 학생 성적표에서 과목 이름이 매번 바뀔 때, 성적을 찾기 위해 동적으로 과목 이름을 참조하고자 할 때.
• 해결 방법: INDEX와 MATCH를 사용하여 과목 이름이 바뀌더라도 성적을 가져올 수 있습니다.

=INDEX(A2:D10, MATCH("학생이름", A2:A10, 0), MATCH("과목이름", A1:D1, 0))

사례 3: 다중 조건을 기반으로 데이터 찾기

• 문제: 직원 이름과 부서를 기준으로 급여 데이터를 찾고 싶을 때.
• 해결 방법: INDEX와 MATCH를 사용하여 여러 조건을 기반으로 값을 찾습니다.

=INDEX(D2:D100, MATCH(1, (A2:A100="김철수")*(B2:B100="영업부"), 0))

4. 결론

INDEX와 MATCH 함수를 함께 사용하면 VLOOKUP 함수가 가지는 한계를 쉽게 극복할 수 있습니다. 데이터가 왼쪽에 있더라도 검색할 수 있고, 열 구조가 바뀌어도 유연하게 대응할 수 있으며, 여러 조건을 동시에 검색할 수 있다는 점에서 더 강력한 도구입니다. 실무에서 데이터 처리의 복잡성이 증가할수록 이 두 함수를 적절히 활용하면 데이터 관리와 분석이 훨씬 수월해질 것입니다.