방대한 데이터가 담긴 엑셀 스프레드시트에서 특정 정보를 손쉽게 찾아 추출하고 싶었던 적이 있으신가요? 엑셀의 VLOOKUP 함수를 익혀두면 단 하나의 강력한 함수만으로 이런 검색 작업을 빠르게 처리할 수 있습니다.
VLOOKUP은 매개변수가 많고 활용 방식도 다양해서 많은 분들이 어렵게 느끼는 함수입니다. 하지만 이 글에서는 VLOOKUP의 모든 사용법과 이 함수가 왜 그토록 강력한지 차근차근 알려드리겠습니다.
엑셀 VLOOKUP 함수의 매개변수 이해하기
엑셀에서 아무 셀이나 선택한 뒤 =VLOOKUP(이라고 입력하면, 사용할 수 있는 모든 함수 매개변수를 보여주는 팝업이 나타납니다.
각 매개변수가 어떤 의미를 가지는지 하나씩 살펴보겠습니다.
- lookup_value: 스프레드시트에서 찾으려는 값
- table_array: 검색 대상이 되는 셀 범위
- col_index_num: 결과를 가져올 열의 번호
- [range_lookup]: 일치 방식 (TRUE = 근사값, FALSE = 정확한 일치)
단 네 가지 매개변수만으로도 대규모 데이터 세트 안에서 다양하고 실용적인 데이터 검색이 가능합니다.
간단한 VLOOKUP 예제로 배워보기
VLOOKUP은 초급 엑셀 함수 과정에서 다루지 않는 함수이기 때문에, 간단한 예제부터 시작해 보겠습니다.
이번 예제에서는 미국 학교들의 SAT 점수가 담긴 대형 스프레드시트를 사용합니다. 이 시트에는 450개가 넘는 학교와 각 학교별 독해, 수학, 작문 SAT 점수가 함께 들어 있습니다.
이렇게 방대한 데이터 세트에서 원하는 학교를 눈으로 일일이 찾아내는 것은 상당히 시간이 걸리는 작업입니다.
그 대신 표 옆의 빈 셀에 간단한 조회 양식을 만들 수 있습니다. '학교' 필드 하나와 독해, 수학, 작문 점수를 위한 세 개의 추가 필드만 만들면 됩니다.
다음으로 이 세 필드가 실제로 작동하도록 VLOOKUP 함수를 설정해야 합니다. 독해(Reading) 필드에 다음 순서대로 함수를 입력합니다.
- =VLOOKUP(을 입력합니다.
- 학교(School) 필드를 선택합니다. 이 예제에서는 I2 셀입니다. 그리고 쉼표를 입력합니다.
- 찾으려는 데이터가 포함된 전체 셀 범위를 선택한 후 쉼표를 입력합니다.
범위를 선택할 때는 검색 기준이 되는 열(여기서는 학교 이름 열)부터 시작해 데이터가 있는 나머지 열과 행을 모두 포함하면 됩니다.
참고: 엑셀의 VLOOKUP 함수는 검색 열의 오른쪽에 있는 셀만 검색할 수 있습니다. 따라서 이 예제에서는 학교 이름 열이 찾으려는 데이터 열보다 반드시 왼쪽에 위치해야 합니다.
- 독해 점수를 가져오려면 맨 왼쪽 선택 열에서 세 번째 열을 지정해야 합니다. 3을 입력하고 쉼표를 입력합니다.
- 마지막으로 정확한 일치를 의미하는 FALSE를 입력하고 )로 함수를 닫습니다.
완성된 VLOOKUP 함수는 다음과 같습니다.
=VLOOKUP(I2,B2:G461,3,FALSE)
함수 입력을 마치고 Enter를 누르면 독해 필드에 #N/A 오류가 표시됩니다.
이는 학교 필드가 비어 있어 VLOOKUP이 찾을 값이 없기 때문입니다. 하지만 원하는 고등학교 이름을 입력하면 해당 행의 올바른 독해 점수가 즉시 표시됩니다.
VLOOKUP 대소문자 구분 문제 해결하기
데이터 세트에 표기된 것과 동일한 대소문자로 학교 이름을 입력하지 않으면 검색 결과가 나오지 않는다는 사실을 알게 될 것입니다.
이는 VLOOKUP 함수가 대소문자를 구분하기 때문입니다. 특히 검색 대상 열의 대소문자 표기가 일관되지 않은 대규모 데이터 세트에서는 꽤 번거로운 문제가 될 수 있습니다.
이 문제를 우회하는 방법은 검색 전에 검색값을 소문자로 변환하도록 강제하는 것입니다. 검색 대상 열 바로 옆에 새 열을 만들고 다음 함수를 입력합니다.
=TRIM(LOWER(B2))
이렇게 하면 학교 이름이 소문자로 변환되고, 이름 좌우에 남아 있을 수 있는 불필요한 공백 문자까지 함께 제거됩니다.
Shift 키를 누른 상태에서 마우스 커서를 첫 번째 셀의 오른쪽 아래 모서리에 올리면 커서가 두 개의 가로선 모양으로 바뀝니다. 이때 마우스를 더블클릭하면 전체 열이 한 번에 자동 채워집니다.
마지막으로, VLOOKUP이 이 셀들의 수식이 아닌 텍스트 값을 참조하도록 모든 값을 일반 값으로 변환해야 합니다. 전체 열을 복사한 뒤 첫 번째 셀에서 마우스 오른쪽 버튼을 클릭하고 '값만 붙여넣기'를 선택하면 됩니다.
새 열의 데이터가 정리되었다면, VLOOKUP 함수의 검색 범위 시작점을 B2 대신 C2로 변경해 새 열을 사용하도록 수정합니다.
=VLOOKUP(I2,C2:G461,3,FALSE)
이제 항상 소문자로 검색어를 입력하기만 하면 언제나 정확한 검색 결과를 얻을 수 있습니다.
VLOOKUP의 대소문자 구분 한계를 스마트하게 극복할 수 있는 유용한 엑셀 팁입니다.
VLOOKUP 근사 일치(Approximate Match) 활용하기
앞서 살펴본 정확한 일치 방식은 비교적 straightforward하지만, 근사 일치 방식은 조금 더 복잡합니다.
근사 일치는 숫자 범위를 검색할 때 가장 빛을 발합니다. 올바르게 사용하려면 검색 범위가 미리 잘 정렬되어 있어야 한다는 점을 기억하세요. 가장 대표적인 예가 숫자 점수에 해당하는 학점(등급)을 찾는 VLOOKUP 함수입니다.
교사가 한 해 동안의 학생 숙제 점수 목록과 최종 평균 열을 관리하고 있다면, 최종 점수에 해당하는 학점이 자동으로 계산되어 표시되면 무척 편리할 것입니다.
VLOOKUP 함수를 사용하면 이것이 충분히 가능합니다. 필요한 것은 각 숫자 점수 구간에 맞는 학점이 담긴 조회표를 시트 오른쪽에 배치해 두는 것뿐입니다.
이제 VLOOKUP 함수와 근사 일치를 조합하면 올바른 숫자 구간에 해당하는 학점을 자동으로 찾아낼 수 있습니다.
이 VLOOKUP 함수의 구성 요소는 다음과 같습니다.
- lookup_value: F2, 최종 평균 점수
- table_array: I2:J8, 학점 조회 범위
- index_column: 2, 조회표의 두 번째 열
- [range_lookup]: TRUE, 근사 일치
G2 셀에서 VLOOKUP 함수를 완성하고 Enter를 누른 뒤, 앞서 설명한 자동 채우기 방법으로 나머지 셀까지 채우면 모든 학점이 알맞게 입력됩니다.
여기서 중요한 점은, 엑셀의 VLOOKUP 함수가 각 학점이 할당된 점수 구간의 하단 경계부터 다음 학점 구간의 상단 경계까지 검색한다는 것입니다.
따라서 "C"는 낮은 경계값(75)에 할당되고, "B"는 자체 학점 구간의 최솟값에 할당됩니다. VLOOKUP은 60~75 사이의 모든 값에 대해 가장 가까운 근사값인 60("D")의 결과를 찾아냅니다.
엑셀의 VLOOKUP은 오랫동안 제공되어 온 매우 강력한 함수로, 통합 문서 어디에 있든 일치하는 값을 찾아내는 데 유용하게 활용됩니다.
다만 참고할 점은, 월간 Office 365 구독 사용자라면 이제 더 새로운 XLOOKUP 함수를 사용할 수 있다는 것입니다. XLOOKUP은 더 많은 매개변수와 향상된 유연성을 제공합니다. 반기 구독 사용자는 2020년 7월 업데이트가 배포될 때까지 기다려야 했습니다.