엑셀 VLOOKUP 함수, 이것만 알면 됩니다! - 실무 핵심 요약

실습 가이드

엑셀 VLOOKUP 함수는 6글자로 요약할 수 있습니다. 바로 '찾아주는 함수'입니다.

VLOOKUP 함수는 세로로 넓게 입력된 표의 첫번째 열(맨 왼쪽)에서 값을 찾은 뒤, 오른쪽에 위치한 다른 데이터를 출력하는 함수입니다.

VLOOKUP 함수는 아래 4단계만 기억하면 누구나 쉽게 사용할 수 있습니다.

이제 4단계 공식을 활용해 커피 가격을 검색하는 서식을 직접 만들어보겠습니다.

1. VLOOKUP 함수 기초 : 예제파일을 실행한 뒤 [VLOOKUP 함수 기초] 시트로 이동합니다. G4 셀을 선택한 뒤 검색할 메뉴를 입력합니다. 이번 강의에서는 '오늘의커피'를 입력해보겠습니다. 메뉴를 입력했다면 H4 셀을 선택한 후 VLOOKUP 함수를 단계별로 입력합니다.

   

2. 먼저 등호(=)를 입력합니다. 이어서 VL 까지만 입력하면 VLOOKUP 함수가 자동완성됩니다. 이 상태에서 TAB 키를 누르면 VLOOKUP 함수를 빠르게 입력할 수 있습니다.

   

3. VLOOKUP 인수 설명 : 첫번째 인수는 찾을값입니다. G4 셀을 선택한 후 쉼표(,)를 입력합니다.

   

4. 두번째 인수는 참조범위입니다. 메뉴부터 VENTI 사이즈까지 범위를 넓게 선택한 후 F4 키를 눌러 절대참조로 변경합니다. 실무에서는 VLOOKUP 함수를 자동채우기로 사용하는 경우가 많은데, 아주 특별한 상황을 제외하고는 참조범위를 항상 고정되도록 입력하는 것이 원칙입니다.

   

   오빠두Tip : 셀 참조방식에 대한 보다 자세한 설명은 기초입문강의 - 셀 참조방식의 모든 것 영상강의를 참고해주세요.
   [related_posts postid="40927"]
   
5. 세번째 인수는 열번호입니다. 표에서 TALL 사이즈 가격은 두번째 열에 있으므로, 열 번호로 2를 입력합니다.

   

6. 마지막 인수로 0을 입력해 마무리한 뒤 ENTER 키를 누르면 오늘의커피 TALL 사이즈 가격인 3,800원이 검색됩니다.

   

VLOOKUP 함수, 알고 보면 정말 간단합니다. 다만 VLOOKUP 함수를 사용할 때에는 아래 두 가지 주의사항을 반드시 숙지해야 합니다.

찾을값은 참조범위의 첫번째 열(맨 왼쪽)에 있어야 합니다.

VLOOKUP 함수에서 찾을 값은 참조범위의 첫번째 열(맨 왼쪽)에 위치해야 합니다. 만약 찾을 값이 범위 중간이나 오른쪽에 있다면, 왼쪽 방향으로는 검색할 수 없습니다.

찾을 값이 참조범위의 중간이나 오른쪽에 있을 경우에는 INDEX/MATCH 함수 또는 XLOOKUP 함수(365 버전)를 사용해 해결합니다.

찾을값은 항상 고유해야 합니다.

VLOOKUP 함수로 찾을 값은 항상 고유값이어야 합니다. 동일한 찾을값이 여러 개 존재할 경우, VLOOKUP 함수는 가장 위에 있는 값을 반환하기 때문입니다.

찾을값이 여러 개이고 그 결과를 동시에 반환해야 하는 경우에는 VLOOKUP 여러개 값 불러오기 공식 또는 FILTER 함수(365 버전)를 사용해 해결합니다.

이제 VLOOKUP 함수를 활용해 직원정보를 검색하는 직원 검색 툴을 직접 제작해보겠습니다.

1. 목록상자 만들기 : 예제파일의 두번째 시트인 [VLOOKUP 함수 실전] 시트로 이동합니다. 먼저 직원을 선택할 수 있는 목록상자를 추가하겠습니다. I3 셀을 선택한 후 [데이터] - [데이터 유효성 검사]로 이동합니다. 데이터 유효성 검사 대화상자가 나타나면, 제한 대상으로 '목록'을 선택한 후 원본 범위로 직원ID가 입력된 B3:B18 범위를 입력합니다.

   

2. [확인] 버튼을 누르면 직원목록을 선택할 수 있는 목록상자가 완성됩니다.

   

   오빠두Tip : 데이터 유효성 검사 기능에 대한 보다 자세한 설명과 예제는 기초 입문 강의 - 데이터 유효성 검사의 모든 것 영상강의를 참고하세요.

   엑셀 데이터 유효성 검사의 모든 것 - 기초 입문 강의

3. 열 번호 입력하기 : VLOOKUP 함수를 사용할 때 머리글 위에 열번호를 입력해두면 함수를 더욱 편리하게 작성할 수 있습니다. 표에서 이름, 성별, 나이는 각각 두번째, 세번째, 네번째 열에 위치하므로 머리글 위에 2, 3, 4를 순서대로 입력합니다.

   

   오빠두Tip : 머리글 위에 열번호를 입력해두면, 다른 데이터가 필요할 때 열번호만 바꿔서 손쉽게 데이터를 갱신할 수 있습니다.
4. VLOOKUP 함수 입력 : 4단계 공식에 따라 찾을값, 참조범위, 열번호, 0을 순서대로 입력합니다. 단, 수식을 오른쪽으로 자동채우기해야 하므로 찾을값인 I3 셀을 절대참조로 입력하는 점에 주의해주세요.
   =VLOOKUP($I$3,$B$2:$G$18,J1,0)

   

5. 수식을 오른쪽으로 자동채우기하면 선택한 직원의 이름, 성별, 나이를 한 번에 조회하는 직원검색 툴이 완성됩니다. 다른 정보가 필요할 경우, 머리글 위의 열번호만 바꿔주면 데이터가 실시간으로 갱신됩니다.

   

VLOOKUP 함수를 사용하다 보면, 표가 가로로 넓어서 열번호를 빠르게 확인하기 어려운 경우가 있습니다. 이럴 때 이름상자를 활용하면 열번호를 손쉽게 확인할 수 있습니다.

VLOOKUP 함수 참조범위의 첫번째 머리글부터 오른쪽으로 드래그하면 선택한 범위의 개수가 [R] x [C] 형식으로 표시됩니다. 여기서 R은 행의 개수, C는 열의 개수를 의미합니다. 따라서 머리글을 드래그한 뒤 이름상자에 표시되는 C 숫자가 곧 열번호가 됩니다.

COLUMNS 함수를 VLOOKUP 함수와 함께 사용하면 여러 개의 VLOOKUP 함수를 한층 편리하게 입력할 수 있습니다.

1. COLUMNS 함수 살펴보기 : 예제파일에서 J8 셀을 선택한 뒤 아래와 같이 COLUMNS 함수를 입력합니다. 범위를 입력할 때 첫번째 셀인 B2는 절대참조로 입력하는 점에 주의합니다.
   =COLUMNS($B$2:C2)

   

   오빠두Tip : $셀:셀 형식의 범위를 '확장범위'라고 부릅니다. 확장범위를 자동채우기하면 첫번째 셀은 고정된 채 한 칸씩 확장되는 범위를 만들 수 있습니다. 확장범위에 대한 자세한 설명은 셀 참조방식 기초 입문강의를 참고해주세요.
2. 이후 수식을 오른쪽으로 자동채우기하면 2부터 시작하는 연속된 순번이 반환됩니다. 이 순번을 VLOOKUP 함수의 열번호로 활용합니다.

   

3. VLOOKUP-COLUMNS 함수 공식 : 이제 COLUMNS 함수 위에 VLOOKUP 함수를 입력합니다. 예제파일 J8 셀에 아래 수식을 입력합니다. 앞선 단계와 마찬가지로 수식을 오른쪽으로 자동채우기해야 하므로, 찾을값인 I8 셀은 절대참조로 입력합니다.

   

4. 수식을 오른쪽으로 자동채우기하면 선택한 직원의 이름부터 직급까지 모든 정보가 한 번에 검색됩니다.