티스토리 뷰
엑셀에서 VLOOKUP 함수는 데이터를 조회하는 데 매우 유용하지만, 중복된 값을 처리하는 데는 한계가 있습니다. 특히, 동일한 조건에 해당하는 여러 값을 가져와야 할 때 VLOOKUP은 첫 번째 값만 반환하는 문제가 발생합니다. 이 글에서는 VLOOKUP의 중복값 처리 방법을 3가지로 정리하여, 실무에서 유용하게 활용할 수 있는 팁을 제공하겠습니다.
VLOOKUP의 한계와 중복값 처리 필요성
VLOOKUP 함수는 특정 값을 기준으로 데이터를 검색하여 결과를 반환하는 함수입니다. 그러나 중복된 값이 존재할 경우, VLOOKUP은 첫 번째로 발견된 값만을 반환합니다. 이로 인해 데이터 분석이나 보고서 작성 시 필요한 모든 정보를 얻지 못하는 경우가 많습니다. 따라서 중복값을 처리하는 방법을 알아두는 것이 중요합니다.
VLOOKUP의 기본 사용법
-
구문:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) - 인수 설명:
-
lookup_value: 찾고자 하는 값 -
table_array: 검색할 데이터 범위 -
col_index_num: 반환할 열 번호 -
range_lookup: 정확한 일치를 원할 경우 FALSE
이러한 기본 사용법을 이해한 후, 중복값을 처리하는 방법으로 넘어가겠습니다.
1. INDEX와 MATCH 함수 조합하기
INDEX와 MATCH 함수를 조합하여 중복값을 처리하는 방법은 매우 유용합니다. 이 방법은 VLOOKUP의 한계를 극복할 수 있습니다.
사용법
```excel
=INDEX(출력범위, SMALL(IF(조건범위=조건, ROW(조건범위)-MIN(ROW(조건범위))+1), ROWS(A$1:A1)))
```
- 설명:
-
출력범위: 반환할 값이 있는 범위 -
조건범위: 조건을 만족하는 범위 -
조건: 찾고자 하는 조건 -
ROWS(A$1:A1): 결과를 순차적으로 반환하기 위한 인수
이 수식을 배열 수식으로 입력해야 하며, 입력 후 CTRL + SHIFT + ENTER를 눌러야 합니다.
예시
| 과일 | 가격 |
|---|---|
| 사과 | 1000 |
| 바나나 | 500 |
| 사과 | 1200 |
| 포도 | 1500 |
위의 표에서 "사과"의 모든 가격을 가져오고 싶다면, 위의 수식을 사용하여 결과를 얻을 수 있습니다.
2. FILTER 함수 사용하기 (오피스 365 이상)
오피스 365 이상에서는 FILTER 함수를 사용하여 중복값을 쉽게 처리할 수 있습니다. 이 함수는 조건에 맞는 모든 값을 반환합니다.
사용법
```excel
=FILTER(출력범위, 조건범위=조건)
```
- 설명:
-
출력범위: 반환할 값이 있는 범위 -
조건범위: 조건을 만족하는 범위 -
조건: 찾고자 하는 조건
예시
위의 표에서 "사과"의 모든 가격을 가져오고 싶다면, 다음과 같이 입력합니다.
```excel
=FILTER(B2:B5, A2:A5="사과")
```
이 수식은 "사과"의 가격을 모두 반환합니다.
3. CONCATENATE와 COUNTIF 함수 조합하기
COUNTIF 함수를 사용하여 중복된 값을 고유한 값으로 변환한 후, VLOOKUP을 사용하는 방법입니다.
사용법
- COUNTIF를 사용하여 중복된 값을 카운트합니다.
- CONCATENATE를 사용하여 고유한 값을 생성합니다.
- VLOOKUP을 사용하여 결과를 반환합니다.
예시
| 교사 | 학생 |
|---|---|
| 김영주 | 철수 |
| 김영주 | 영희 |
| 김인수 | 민수 |
위의 표에서 각 교사에 해당하는 학생을 찾고 싶다면, 다음과 같은 수식을 사용할 수 있습니다.
```excel
=IFERROR(VLOOKUP(CONCATENATE(교사, COUNTIF(교사범위, 교사)), 학생범위, 2, FALSE), "없음")
```
이 수식은 중복된 교사에 대해 고유한 값을 생성하여 VLOOKUP을 통해 학생을 반환합니다.
FAQ
Q1: VLOOKUP의 한계는 무엇인가요?
A1: VLOOKUP은 중복된 값이 있을 경우 첫 번째 값만 반환하며, 좌측 열에서 값을 찾을 수 없습니다.
Q2: INDEX와 MATCH의 장점은 무엇인가요?
A2: INDEX와 MATCH는 위치에 상관없이 값을 찾을 수 있으며, 중복값 처리에 유리합니다.
Q3: FILTER 함수는 어떤 경우에 사용하나요?
A3: FILTER 함수는 오피스 365 이상에서 사용 가능하며, 조건에 맞는 모든 값을 쉽게 반환할 수 있습니다.
Q4: CONCATENATE와 COUNTIF를 조합하는 이유는 무엇인가요?
A4: 중복된 값을 고유한 값으로 변환하여 VLOOKUP을 통해 정확한 결과를 얻기 위해서입니다.
Q5: 배열 수식은 어떻게 입력하나요?
A5: 배열 수식은 입력 후 CTRL + SHIFT + ENTER를 눌러야 정상적으로 작동합니다.
마무리
VLOOKUP 중복값 처리 방법에 대해 알아보았습니다. INDEX와 MATCH, FILTER, CONCATENATE와 COUNTIF를 조합하여 중복값을 효과적으로 처리할 수 있습니다. 이러한 방법들을 활용하여 엑셀에서 데이터 분석을 더욱 효율적으로 진행해 보세요. 경험을 댓글로 공유해 주세요!
