엑셀 VLOOKUP과 INDEX&MATCH로 원하는 값 쉽게 찾기
엑셀은 데이터 분석과 정리에서 매우 유용한 도구로 자리 잡고 있습니다. 특히 VLOOKUP과 INDEX, MATCH 함수는 데이터 검색과 관련하여 매우 강력한 기능을 제공합니다.
이번 글에서는 이 두 가지 함수의 사용 방법과 함께 실무에서의 활용 예를 자세히 살펴보겠습니다.
VLOOKUP 함수의 이해와 사용법
VLOOKUP 함수는 "Vertical Lookup"의 약자로, 세로 방향으로 데이터를 검색하는 함수입니다. 이 함수는 특정 값이 포함된 열에서 원하는 정보를 찾는 데 사용됩니다.
VLOOKUP의 기본 형식은 다음과 같습니다.
=VLOOKUP(검색 기준값, 검색 대상 범위, 찾으려는 값이 들어있는 열 번호, [정확한 일치 여부])
여기서 각 인수의 의미를 설명하겠습니다. 첫 번째 인수는 검색할 값으로, 보통 특정 셀의 값을 참조합니다.
두 번째 인수는 검색할 데이터가 포함된 범위입니다. 세 번째 인수는 범위 내에서 반환할 열 번호를 지정합니다.
마지막 인수는 TRUE 또는 FALSE로, 정확한 일치를 원할 경우 FALSE를 사용합니다.
VLOOKUP 함수의 예시
다음의 표를 통해 VLOOKUP의 사용 예를 살펴보겠습니다.
| 면적 (㎡) | 금액 (원) |
|---|---|
| 5,000-6,000 | 100,000 |
| 6,000-7,000 | 200,000 |
| 7,000-8,000 | 300,000 |
위의 데이터를 바탕으로, 특정 면적에 해당하는 금액을 찾고자 할 때 C2 셀에 다음과 같은 VLOOKUP 함수를 입력할 수 있습니다.
=VLOOKUP(B2, $A$2:$B$4, 2, FALSE)
이 함수는 B2 셀에 있는 면적을 기준으로 A열에서 해당 면적에 맞는 금액을 B열에서 찾아 반환합니다. 만약 B2 셀에 5,230이 입력되어 있다면, 결과는 100,000이 나올 것입니다.
VLOOKUP 함수의 한계
VLOOKUP 함수는 매우 유용하지만 몇 가지 한계도 존재합니다. 첫째, 이 함수는 기본적으로 왼쪽에서 오른쪽으로만 검색이 가능합니다.
즉, 찾고자 하는 값이 검색 범위의 첫 번째 열에 위치해야 합니다. 둘째, VLOOKUP은 첫 번째로 일치하는 값만 반환하므로 같은 값이 여러 번 있는 경우 원하는 결과를 얻지 못할 수 있습니다.
아래의 표는 VLOOKUP 함수의 한계 및 대안으로 사용될 수 있는 INDEX와 MATCH 함수의 조합을 보여줍니다.
| 한계점 | 설명 | 대안 |
|---|---|---|
| 왼쪽에서 오른쪽으로만 검색 | 검색 범위의 첫 열에만 값을 찾을 수 있음 | INDEX와 MATCH 함수 조합 |
| 첫 번째 일치값만 반환 | 동일한 값이 있을 경우 첫 번째 값만 반환됨 | INDEX와 MATCH 함수 조합 |
| 여러 조건을 만족해야 할 경우 | 단일 조건만 사용할 수 있음 | 다중조건 검색 구현 |
INDEX와 MATCH 함수의 이해
INDEX와 MATCH 함수는 각각 독립적인 함수이지만 함께 사용할 때 강력한 조합을 이룹니다. INDEX 함수는 특정 범위 내에서 특정 위치의 값을 반환하는 함수입니다.
기본 형식은 다음과 같습니다.
=INDEX(검색 범위, 행 번호, [열 번호])
MATCH 함수는 특정 값이 범위 내에서 몇 번째 위치에 있는지를 반환합니다. 기본 형식은 다음과 같습니다.
=MATCH(찾을 값, 검색 범위, [일치 유형])
이 두 함수를 조합하면 VLOOKUP의 한계를 극복할 수 있습니다. 예를 들어, 특정 면적에 해당하는 금액을 찾고 싶을 때 다음과 같은 방식으로 사용할 수 있습니다.
INDEX와 MATCH 함수의 예시
위의 VLOOKUP 예제를 INDEX와 MATCH를 사용하여 구현해 보겠습니다. 이전의 면적과 금액 데이터는 동일하므로 다음과 같은 방식으로 함수를 작성할 수 있습니다.
=INDEX($B$2:$B$4, MATCH(B2, $A$2:$A$4, 1))
이 식은 B2 셀에서 면적을 찾고, 그 면적에 해당하는 금액을 B열에서 반환합니다. MATCH 함수는 B2 셀의 값이 A2:A4 범위의 몇 번째 위치에 있는지를 찾고, INDEX 함수는 그 위치에 해당하는 B열의 값을 반환합니다.
INDEX와 MATCH 함수의 장점
INDEX와 MATCH 함수 조합의 가장 큰 장점은 데이터가 어느 방향에 위치하든 상관없이 원하는 값을 찾을 수 있다는 것입니다. 또한, 다중 조건을 만족하는 검색도 가능합니다.
예를 들어, 연도와 팀 이름을 모두 만족하는 값을 찾고 싶을 때는 다음과 같은 방식으로 사용할 수 있습니다.
=INDEX(타율범위, MATCH(1, (연도범위=조건연도)*(팀범위=조건팀), 0))
여기서 조건연도와 조건팀은 사용자가 입력한 값을 참조합니다. 이 수식은 두 조건을 모두 만족하는 행 번호를 찾고, 그에 해당하는 타율 값을 반환합니다.
다중조건 검색의 구현
다중조건 검색을 구현하기 위해서는 배열 수식을 사용해야 합니다. 배열 수식은 여러 개의 계산을 한 번에 처리할 수 있는 수식입니다.
위에서 설명한 다중조건 검색을 배열 수식을 사용하여 구현해 보겠습니다.
배열 수식 사용 예시
예를 들어, 2016년과 넥센 팀의 타율을 찾고자 할 때 다음과 같은 배열 수식을 사용할 수 있습니다.
{=INDEX(A1:C21, MATCH(1, (A1:A21=2016)*(B1:B21="넥센"), 0), 3)}
이 수식에서는 A1:A21 범위에서 2016년을 찾고, B1:B21 범위에서 넥센 팀을 찾습니다. 두 조건이 모두 만족되는 경우에만 1을 반환하므로, MATCH 함수는 해당 조건을 만족하는 행 번호를 반환합니다.
INDEX 함수는 그 행 번호에 해당하는 타율 값을 반환합니다. 배열 수식을 입력하려면 수식을 작성한 후, Ctrl + Shift + Enter를 눌러야 합니다.
이렇게 하면 수식이 배열 수식으로 인식되고, 결과값이 반환됩니다.
다중조건 검색의 장점
다중조건 검색의 가장 큰 장점은 특정 조건에 따라 결과를 필터링할 수 있다는 점입니다. 예를 들어, 특정 연도와 팀 이름을 입력하여 해당 팀의 타율을 쉽게 찾을 수 있습니다.
이는 데이터 분석 및 보고서 작성 시 매우 유용하게 활용될 수 있습니다. 다른 내용도 보러가기 #2
결론
엑셀의 VLOOKUP 함수와 INDEX, MATCH 함수는 데이터 검색에 있어 매우 중요한 도구입니다. VLOOKUP은 간단한 데이터 검색에 유용하지만, INDEX와 MATCH 함수의 조합은 더 복잡하고 다양한 조건을 처리할 수 있는 강력한 기능을 제공합니다.
특히 다중조건 검색을 통해 원하는 결과를 보다 쉽게 얻을 수 있습니다. 엑셀을 활용하여 데이터를 정리하고 분석할 때, 이러한 함수들을 잘 활용하면 업무의 효율성을 크게 향상시킬 수 있습니다.
엑셀의 다양한 기능을 익혀 업무에 적극적으로 활용해 보시기 바랍니다.
댓글
댓글 쓰기