Excel 함수 정리 - Index & Match

Excel

 

 

엑셀 함수 시리즈 #2: INDEX & MATCH 함수로 VLOOKUP 한계 극복하기

엑셀에서 데이터를 조회하고 분석하는 데 있어 VLOOKUP 함수는 가장 많이 사용되는 함수 중 하나입니다. 그러나 VLOOKUP는 몇 가지 중요한 한계를 가지고 있습니다. 이 글에서는 VLOOKUP의 한계를 설명하고, INDEX와 MATCH 함수를 사용하여 이러한 한계를 극복하는 방법을 예제와 함께 설명하겠습니다.

VLOOKUP 함수의 한계

VLOOKUP 함수는 엑셀에서 데이터를 조회하는 데 유용하지만, 몇 가지 중요한 한계가 있습니다.

1. 오른쪽으로만 조회 가능: VLOOKUP는 lookup 값이 있는 열의 오른쪽에 있는 데이터만 조회할 수 있습니다. lookup 값이 있는 열의 왼쪽에 있는 데이터를 조회할 수 없습니다.
2. 정확한 일치가 필수: VLOOKUP는 기본적으로 근사 일치를 사용하지만, 정확한 일치를 원할 경우 FALSE 또는 0을 네 번째 인수로 지정해야 합니다. 이를 생략하면 근사 일치로 동작하여 잘못된 결과를 초래할 수 있습니다.
3. 대/소문자 구분 안 됨: VLOOKUP는 대/소문자를 구분하지 않습니다. 즉, "Apple"과 "apple"는 같은 값으로 간주됩니다.
4. 열 삽입/삭제 시 오류 발생: VLOOKUP는 열의 인덱스를 기반으로 동작하므로, 열을 삽입하거나 삭제하면 열의 인덱스가 변경되어 오류가 발생할 수 있습니다.

반응형

INDEX & MATCH 함수로 VLOOKUP 한계 극복하기

INDEX와 MATCH 함수를 결합하여 VLOOKUP의 한계를 극복할 수 있습니다. INDEX와 MATCH 함수는 더 유연하고 강력한 데이터 조회 기능을 제공합니다.

INDEX 함수

INDEX 함수는 지정된 범위 내에서 특정 위치의 값을 반환합니다. 문법은 다음과 같습니다:

=INDEX(범위, 행번호, [열번호])

• 범위: 값을 반환할 범위입니다.
• 행번호: 반환할 값을 포함하는 행 번호입니다.
• 열번호: 반환할 값을 포함하는 열 번호입니다(선택 사항).

MATCH 함수

MATCH 함수는 지정된 범위 내에서 특정 값을 검색하고 그 상대적인 위치를 반환합니다. 문법은 다음과 같습니다:

=MATCH(조회값, 조회범위, [일치유형])

• 조회값: 찾고자 하는 값입니다.
• 조회범위: 값을 검색할 범위입니다.
• 일치유형: 0은 정확한 일치를 의미하며, 1은 근사 일치를 의미합니다(기본값).

728x90

INDEX & MATCH 결합

INDEX와 MATCH 함수를 결합하여 VLOOKUP의 한계를 극복할 수 있습니다. 예를 들어, 다음과 같은 데이터가 있다고 가정해 보겠습니다:

이름	나이	직업
John	25	개발자
Jane	30	디자이너
Mike	35	마케터

 

이 데이터에서 "John"의 나이를 조회하려면 다음과 같은 INDEX & MATCH 함수를 사용할 수 있습니다:

=INDEX(B2:B4, MATCH("John", A2:A4, 0))

• B2:B4: 나이가 포함된 범위입니다.
• MATCH("John", A2:A4, 0): "John"의 위치를 찾습니다.

이 함수는 "John"의 나이인 25를 반환합니다.

INDEX & MATCH로 왼쪽 조회

VLOOKUP는 왼쪽으로 조회할 수 없지만, INDEX & MATCH는 가능합니다. 예를 들어, 다음과 같은 데이터가 있다고 가정해 보겠습니다:

이름	나이	직업
John	25	개발자
Jane	30	디자이너
Mike	35	마케터

 

이 데이터에서 나이가 30인 사람의 이름을 조회하려면 다음과 같은 함수를 사용할 수 있습니다:

=INDEX(A2:A4, MATCH(30, B2:B4, 0))

• A2:A4: 이름이 포함된 범위입니다.
• MATCH(30, B2:B4, 0): 나이 30의 위치를 찾습니다.

이 함수는 나이가 30인 사람의 이름인 "Jane"을 반환합니다.

SMALL

INDEX & MATCH로 열 삽입/삭제 시 오류 방지

VLOOKUP는 열을 삽입하거나 삭제하면 오류가 발생할 수 있지만, INDEX & MATCH는 이러한 문제를 피할 수 있습니다. 이는 열의 인덱스가 아닌 실제 위치를 기반으로 동작하기 때문입니다.

예를 들어, 다음과 같은 데이터가 있다고 가정해 보겠습니다:

이름나이직업

John	25	개발자
Jane	30	디자이너
Mike	35	마케터

 

이 데이터에 새로운 열을 삽입하더라도 INDEX & MATCH 함수는 여전히 올바르게 동작합니다:

=INDEX(B2:B4, MATCH("John", A2:A4, 0))

이 함수는 여전히 "John"의 나이인 25를 반환합니다.

INDEX & MATCH로 다중 조건 조회

INDEX & MATCH는 다중 조건 조회도 가능합니다. 예를 들어, 다음과 같은 데이터가 있다고 가정해 보겠습니다:

이름	나이	직업	지역
John	25	개발자	서울
Jane	30	디자이너	부산
Mike	35	마케터	서울

 

이 데이터에서 나이가 30이고 지역이 "부산"인 사람의 이름을 조회하려면 다음과 같은 함수를 사용할 수 있습니다:

=INDEX(A2:A4, MATCH(1, (B2:B4=30) * (D2:D4="부산"), 0))

• MATCH(1, (B2:B4=30) * (D2:D4="부산"), 0): 나이가 30이고 지역이 "부산"인 위치를 찾습니다.

이 함수는 나이가 30이고 지역이 "부산"인 사람의 이름인 "Jane"을 반환합니다.

결론

VLOOKUP 함수는 엑셀에서 데이터를 조회하는 데 유용하지만, 몇 가지 중요한 한계가 있습니다. INDEX와 MATCH 함수를 결합하여 이러한 한계를 극복할 수 있습니다. INDEX & MATCH 함수는 더 유연하고 강력한 데이터 조회 기능을 제공하며, 왼쪽 조회, 열 삽입/삭제 시 오류 방지, 다중 조건 조회 등 다양한 상황에서 유용하게 사용될 수 있습니다. 따라서 엑셀 사용자라면 INDEX & MATCH 함수를 잘 활용하는 것이 중요합니다.