엑셀 VLOOKUP 함수란 무엇인가?
마이크로소프트 엑셀에는 다양한 용도로 활용할 수 있는 수많은 함수가 내장되어 있습니다. 오늘 소개할 함수는 바로 VLOOKUP입니다. VLOOKUP은 'Vertical Lookup', 즉 '세로 방향 조회'를 의미하며, 표에서 특정 값을 위에서 아래로 검색한 뒤 그에 대응하는 결과값을 반환하는 함수입니다.
VLOOKUP은 엑셀이 제공하는 여러 조회 및 참조(Lookup & Reference) 함수 중 하나이자, 가장 널리 쓰이는 핵심 함수입니다. 스프레드시트에서 원하는 정보를 세로 방향으로 검색하고 그에 맞는 값을 자동으로 가져올 수 있기 때문에, 표 형태의 데이터에서 특정 값을 추출할 때 매우 유용합니다. 예를 들어 긴 상품 목록에서 특정 상품의 가격만 찾아내야 할 때, 이 함수 하나면 몇 초 만에 해결됩니다.
VLOOKUP 사용 시 전제 조건
VLOOKUP을 적용하려는 참조 테이블에는 반드시 고유한 값(unique value)을 가진 열이 있어야 합니다. 중복된 값이 존재하면 함수가 어떤 항목과 일치해야 하는지 판단하지 못해 엉뚱한 결과를 반환할 수 있기 때문입니다. 지금 당장 와닿지 않더라도 아래 예제를 따라 하다 보면 자연스럽게 이해될 것입니다.
예제로 배우는 VLOOKUP 실전 활용
이번 예제에서는 상품 이름과 가격이 담긴 표를 만들고, VLOOKUP을 이용해 상품 이름만 입력하면 가격이 자동으로 나타나도록 구성해 보겠습니다. 물론 이 예제에서는 데이터가 눈앞에 있지만, 여러 통합 문서와 시트에 걸쳐 수백 개의 상품이 관리되는 실무 환경이라면 이 함수의 진가를 제대로 발휘하게 됩니다.
직접 엑셀 시트를 만들어도 되고, 샘플 파일을 내려받아 마이크로소프트 엑셀에서 열어도 좋습니다. 이해하기 쉽도록 간단한 예제로 진행합니다.
1단계: 함수 삽입하기
화면 왼쪽에는 상품 이름과 카테고리가 정리된 표가 있습니다. 목표는 오른쪽 표에 상품 이름만 입력하면, 왼쪽 표에서 해당 상품의 가격과 카테고리가 자동으로 불러와지도록 만드는 것입니다.
먼저 셀 H2에 'Scarf(스카프)'라는 값이 입력되어 있다고 가정합니다. 스카프의 가격을 구하기 위해 I2 셀에 VLOOKUP을 적용해 보겠습니다. I2 셀을 클릭한 뒤 상단 메뉴에서 [수식(FORMULAS)] 탭으로 이동하고, [함수 삽입(Insert Function)]을 클릭하거나 단축키 SHIFT + F3를 누릅니다. 그러면 함수 삽입 창이 나타납니다.
검색창에 VLOOKUP을 입력하고 [이동(Go)] 버튼을 클릭합니다. 목록에서 VLOOKUP이 선택된 상태로 확인을 누르면 함수 인수 설정 화면으로 넘어갑니다.
2단계: 함수 인수(Function Arguments) 이해하기
VLOOKUP의 함수 인수 창에는 총 네 개의 인수가 표시됩니다. 처음 세 개는 굵게 표시되어 있는데, 이는 필수 입력 항목이라는 뜻이며 네 번째는 선택 사항입니다.
- Lookup_value(찾을 값): 검색 기준이 되는 단일 값입니다. 이 예제에서는 고유 식별자 역할을 하는 상품 이름이 해당됩니다.
- Table_array(참조 테이블): 값을 검색할 전체 참조 범위입니다. Table_array 옆의 작은 아이콘을 클릭한 후 드래그하여 제목 행을 제외한 표 전체를 선택합니다. 이때 F4 키를 눌러 셀 주소를 절대 참조($A$2:$C$16)로 고정하세요. 절대 참조로 지정하지 않으면 수식을 다른 셀로 드래그할 때 참조 범위가 함께 밀려 오류가 발생합니다.
- Col_index_num(열 번호): VLOOKUP이 참조 테이블에서 고유 식별자를 찾은 후, 몇 번째 열의 값을 반환할지 지정합니다. 참조 테이블에서 가격은 상품 이름 기준 두 번째 열에 있으므로 2를 입력합니다. 여기서 '2'는 워크시트 전체에서 두 번째 열이라는 의미가 아니라 table_array 범위 내에서 두 번째 열이라는 점에 유의하세요. 만약 찾으려는 데이터가 두 번째 시트에 있다면, table_array를 해당 시트에서 선택하면 됩니다.
- Range_lookup(일치 옵션): 참조 테이블에서 고유 식별자와 가장 근사한 값을 찾을지 여부를 결정합니다. 근사값 일치를 사용하려면 참조 테이블이 반드시 오름차순으로 정렬되어 있어야 하는데, 이 예제에서는 정렬되어 있지 않으므로 FALSE(정확히 일치)를 입력합니다.
모든 인수를 입력한 뒤 확인을 누르면, I2 셀에 스카프의 가격이 자동으로 표시됩니다.
3단계: 카테고리도 함께 불러오기
같은 방식으로 J2 셀(카테고리 열)에도 VLOOKUP 수식을 적용하면 상품의 카테고리까지 자동으로 가져올 수 있습니다. 이때 수식에서 변경할 부분은 딱 한 곳입니다. 카테고리가 참조 테이블의 세 번째 열에 있으므로 Col_index_num 값을 3으로 바꿔주면 됩니다.
4단계: #N/A 오류 깔끔하게 처리하기
이제 I2와 J2 셀을 아래로 드래그하여 수식을 복사할 수 있습니다. 다만 옆에 상품 이름을 입력하기 전까지는 해당 셀에 #N/A 오류가 표시됩니다. 이를 방지하려면 엑셀의 ISBLANK 함수와 IF 함수를 함께 활용하면 됩니다.
#N/A가 표시된 I3 셀을 클릭한 후, 수식 입력줄에서 기존 수식을 다음과 같이 수정합니다.
=VLOOKUP(H3,$A$2:$C$16,2,FALSE)
위 수식을 아래와 같이 변경합니다.
=IF(ISBLANK(H3),"",VLOOKUP(H3,$A$2:$C$16,2,FALSE))
이렇게 수정하면 H3 셀에 상품 이름이 입력되기 전까지 I3 셀은 빈 칸으로 유지되어 훨씬 깔끔한 표를 만들 수 있습니다.
마치며
지금까지 엑셀 VLOOKUP 함수의 기본 개념부터 실전 활용법, 오류 처리까지 살펴보았습니다. 아래 GIF는 두 번째 시트에 있는 참조 테이블을 활용해 VLOOKUP을 적용하는 과정을 보여주는 데모입니다. 여러 시트에 흩어진 데이터를 다룰 때도 같은 원리로 참조 범위만 다른 시트에서 선택하면 되니, 실무에서 꼭 한 번 직접 따라 해보시기 바랍니다.
