엑셀 VLOOKUP 함수, 이것만 알면 됩니다! - 실무 핵심 요약
엑셀 VLOOKUP 함수 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 엑셀 VLOOKUP 함수 실무 핵심 요약예제파일
엑셀 VLOOKUP 함수는 6글자로 요약할 수 있습니다. 바로 '찾아주는 함수'인데요.
VLOOKUP 함수는 세로로 넓게 입력된 표의 첫번째 열(맨 왼쪽)에서 값을 찾은 뒤, 오른쪽에 다른 데이터를 출력하는 함수입니다.
VLOOKUP 함수는 세로로 길게 입력된 표에서 원하는 데이터를 찾아주는 함수입니다 VLOOKUP 함수는 아래 4단계만 기억하면 누구나 쉽게 사용할 수 있습니다.
찾을값 선택 → 범위 드래그 F4 → 열번호 → 0 엔터!
VLOOKUP 함수는 4단계만 기억하면 누구나 쉽게 사용할 수 있습니다 VLOOKUP 함수 4단계 공식을 사용해서, 커피 가격을 검색하는 서식을 만들어보겠습니다.
- VLOOKUP 함수 기초 : 예제파일을 실행 후, [VLOOKUP 함수 기초] 시트로 이동합니다. G4 셀을 선택한 뒤 원하는 메뉴를 입력합니다. 이번 강의에서는 '오늘의커피'를 검색해볼게요. 메뉴를 입력했으면, H4셀을 선택 후 VLOOKUP 함수를 단계별로 입력해보겠습니다.
VLOOKUP 함수로 찾을값을 입력합니다 - 우선 등호 입력합니다. 이후 VL 까지만 입력하면 VLOOKUP 함수가 자동 완성됩니다. 그 상태에서 TAB 키를 누르면 VLOOKUP 함수를 빠르게 입력할 수 있습니다.
등호 입력 후, VL 만 입력하면 VLOOKUP 함수가 자동 완성됩니다 - VLOOKUP 인수 설명 : 첫번째 인수는 찾을값입니다. G4셀을 선택한 후, 쉼표(,)를 입력합니다.
첫번째 인수로 찾을값을 입력합니다 - 두번째 인수로는 참조범위인데요. 메뉴부터 VENTI사이즈 까지 범위를 넓게 선택 후, F4키를 눌러 절대참조로 변경합니다. 보통 실무에서는 VLOOKUP 함수를 자동채우기해서 사용하는데, 아주 특별한 상황을 제외하고 참조범위는 항상 고정되도록 입력합니다.
두번째 인수로 참조범위를 선택 후, F4를 눌러 절대참조로 변경합니다 오빠두Tip : 셀 참조방식에 대한 보다 자세한 내용은 기초입문강의 - 셀 참조방식의 모든 것 영상강의를 참고해주세요. - 세번째 인수는 열번호 입니다. 표에서 TALL 사이즈 가격은 2번째 열에 있습니다. 열 번호로 2를 넣어주세요.
불러올 값의 열번호로 2를 입력합니다 - 마지막 인수로 0을 넣어서 마무리 한 뒤, ENTER키로 입력하면 오늘의커피 TALL 사이즈 가격인 3,800원이 검색됩니다.
마지막 인수로 0을 넣은 후, 엔터키로 마무리합니다
VLOOKUP 함수, 알고 보니 정말 간단하죠? 하지만 VLOOKUP 함수를 사용할 때에는 아래 2가지 주의사항을 반드시 숙지해야 합니다..
찾을값은 참조범위의 첫번째 열(맨 왼쪽)에 있어야 합니다.
VLOOKUP 함수에서 찾을 값은 참조범위의 첫번째 열 (맨 왼쪽)에 있어야 합니다. 만약 찾을 값이 범위 중간에 있거나 오른쪽에 있을 경우, 왼쪽 방향으로는 검색할 수 없습니다.
찾을 값은 항상 참조범위의 맨 왼쪽에 있어야 합니다 만약 찾을 값이 참조범위의 중간이나 오른쪽에 있을 경우, INDEX/MATCH 함수 또는 XLOOKUP 함수(365 버전)를 사용해서 해결합니다.
찾을값은 항상 고유해야 합니다.
VLOOKUP 함수로 찾을 값은 항상 고유값이여야 합니다. 만약 찾을값이 여러개 있을 경우, VLOOKUP 함수는 맨 위의 값을 검색하는데요.
찾을 값은 항상 고유값이여야 합니다. 중복값이 있으면, 맨 위의 값이 검색됩니다. 만약 찾을값이 여러개 있고, 여러개 결과를 동시에 반환해야 할 경우 VLOOKUP 여러개 값 불러오기 공식 또는 FILTER 함수(365버전)을 사용해서 해결합니다.
이제 VLOOKUP 함수를 사용해서 실전 예제로 직원정보를 검색하는 직원 검색 툴을 제작해보겠습니다.
- 목록상자 만들기 : 예제파일의 두번째 시트인 [VLOOKUP 함수 실전] 시트로 이동합니다. 우선 직원을 선택할 수 있는 목록상자를 추가하겠습니다. I3셀을 선택 후, [데이터] - [데이터 유효성 검사]로 이동합니다. 데이터 유효성 검사 대화상자가 나오면, 제한 대상으로 '목록'을 선택 후, 원본 범위로 직원ID가 입력된 B3:B18 범위를 입력합니다.
데이터유효성 검사로 직원 ID를 선택하는 목록상자를 추가합니다 - [확인] 버튼을 누르면 직원목록을 선택할 수 있는 목록상자가 완성됩니다.
목록상자가 추가되었습니다 오빠두Tip : 데이터 유효성 검사 기능에 대한 보다 자세한 설명과 예제는 기초 입문 강의 - 데이터 유효성 검사의 모든 것 영상강의를 참고하세요. - 열 번호 입력하기 : VLOOKUP 함수를 사용할 때 머리글 위로 열번호를 입력하면 함수를 더욱 편리하게 입력할 수 있습니다. 표에서 이름과 성별, 나이는 각각 두번째, 세번째, 네번째에 있으므로 머리글 위로 2,3,4 를 순서대로 입력합니다.
머리글 위로 각 머리글의 열번호를 추가합니다 오빠두Tip : 머리글 위로 열번호를 입력하면, 다른 데이터가 필요할 때 열번호만 바꿔서 손쉽게 데이터를 갱신할 수 있습니다. - VLOOKUP 함수 입력 : 4단계 공식에 따라 찾을값, 참조범위, 열번호, 0 을 순서대로 입력합니다. 단, 수식을 오른쪽으로 자동채우기해야 되므로 찾을값인 I3셀을 절대참조로 입력하는 것을 주의해주세요.
=VLOOKUP($I$3,$B$2:$G$18,J1,0)
VLOOKUP 함수를 입력합니다. 찾을값은 절대참조로 입력하는 것을 주의합니다 - 수식을 오른쪽으로 자동채우기하면 선택한 직원의 이름, 성별, 나이를 조회나는 직원검색 툴이 완성됩니다. 만약 직원의 다른 정보가 필요할 경우, 머리글 위의 열번호를 바꿔주면 데이터가 실시간으로 갱신됩니다.
수식을 오른쪽으로 자동채우기 하면 직원검색 서식이 완성됩니다
VLOOKUP 함수를 사용하다보면, 표가 가로로 넓어서 열번호를 쉽게 확인하기 어려울 때가 있는데요. 그럴 경우, 이름상자를 사용하면 열번호를 손쉽게 확인할 수 있습니다.
VLOOKUP 함수 참조범위의 첫번째 머리글 부터 오른쪽으로 드래그하면 선택한 범위의 개수가 [R] x [C] 와 같이 표시되는데요. 여기서 R은 행의개수, C는 열의 개수입니다. 따라서 머리글을 드래그한 뒤, 이름상자에 찍힌 C 숫자가 바로 열번호가 됩니다.
범위를 드래그 한 뒤, 이름상자로 열번호를 쉽게 확인할 수 있습니다 오빠두Tip : 출력할 값의 머리글부터 왼쪽 방향으로 드래그해도 열번호를 동일하게 확인할 수 있습니다.COLUMNS 함수를 VLOOKUP 함수와 같이 사용하면 여러 VLOOKUP 함수를 편리하게 입력할 수 있습니다.
- COLUMNS 함수 살펴보기 : 예제파일에서 J8셀을 선택한 뒤, 아래와 같이 COLUMNS 함수를 입력합니다. 범위를 입력할 때 첫번째 셀인 B2는 절대참조로 입력하는 것을 주의합니다.
=COLUMNS($B$2:C2)
COLUMNS 함수를 입력하면 선택한 범위의 열 개수가 계산됩니다 오빠두Tip : $셀:셀 형식의 범위를 '확장범위'라고 합니다. 확장범위를 자동채우기 하면, 첫번째 셀은 고정된 상태로 한칸씩 확장되는 범위를 만들 수 있습니다. 확장범위에 대한 자세한 설명은 셀 참조방식 기초 입문강의를 참고해주세요. - 이후 수식을 오른쪽으로 자동채우기 하면 2부터 시작하는 연속된 순번이 반환되는데요. 이 순번을 VLOOKUP 함수의 열번호로 사용합니다.
수식을 자동채우기 하면 연속된 순번이 반환됩니다 - VLOOKUP-COLUMNS 함수 공식 : 이제 COLUMNS 함수 위로 VLOOKUP 함수를 입력합니다. 예제파일 J8셀에 아래 수식을 입력합니다. 이전과 동일하게 수식을 오른쪽으로 자동채우기 해야하므로, 찾을값인 I8셀은 절대참조로 입력합니다.
COLUMNS 함수를 열번호로 하는 VLOOKUP 함수를 입력합니다 - 수식을 오른쪽으로 자둥채우기하면, 선택한 직원의 이름부터 직급까지 정보가 한 번에 검색됩니다.
수식을 자동채우기 하면 여러개의 VLOOKUP 함수가 한 번에 입력됩니다
- VLOOKUP 함수 기초 : 예제파일을 실행 후, [VLOOKUP 함수 기초] 시트로 이동합니다. G4 셀을 선택한 뒤 원하는 메뉴를 입력합니다. 이번 강의에서는 '오늘의커피'를 검색해볼게요. 메뉴를 입력했으면, H4셀을 선택 후 VLOOKUP 함수를 단계별로 입력해보겠습니다.
