견적서를 작성할 때 상품코드만 입력하면 상품명과 사양, 가격들이 자동으로 채워진다면 얼마나 편할까? 엑셀에는 이미 그런 기능이 있다. 바로 VLOOKUP 함수다.
이번 시간에는 VLOOKUP 함수로 원하는 값을 불러오는 방법을 예제를 통해 알아보자.
VLOOKUP 함수
VLOOKUP은 엑셀에서 가장 많이 사용하는 함수 중 하나다. 이 함수는 지정한 값을 기준으로 표안에서 다른 열에 있는 데이터를 불러올 수 있다.
특히 견적서처럼 상품에 관한 일련 정보들을 연달아 함께 입력해야 하는 경우 상품 코드만 입력하면 관련 정보들을 불러야 입력할 수 있기 때문에 유용하게 사용할 수 있다.
VLOOKUP 함수 기본 입력 형식
엑셀에서 함수를 입력했을때 표시되는 기본적인 수식 구조는 아래와 같다.
한글 번역이 어색해서인지 한국어로 표시할 적당한 이름이 없어서인지 모르겠지만, 용어들이 낯설고 선뜻 이해하기 어렵다.
풀어서 설명하면 아래와 같다.
- 검색할 값 : 데이터를 찾을 때 기준이 되는 값 (예: 상품코드)
- 표 범위 : 검색할 값을 기준으로 원하는 값을 찾아야할 데이터의 범위
- 열 인덱스 번호 : 검색할 값을 기준으로 원하는 데이터가 위치한 열의 순서
- 범위 검색 : 정확히 일치하는 값을 찾을지 비슷한거라도 찾을지 결정하는 옵션
뭔가 이렇게 풀어놔도 어쩌면 이해하기 어렵긴 마찬가지일듯 하다.
함수가 실제로 어떻게 기능하는지 하나하나 천천히 살펴보자.
VLOOKUP 함수 사용방법
우선 아래처럼 상품코드를 입력하면 원하는 상품정보가 자동으로 채워지는 표를 만들어보자.
예제 파일을 보며 함께 해보는 것도 좋은 방법이다. 예제파일은 아래 버튼을 눌러 다운로드 받을 수 있다.
📥 예제파일 다운로드수식 삽입
자동으로 채워야할 셀 중 우선 상품명 제일 윗칸(여기서는 C14)을 선택해 “=vlookup”을 수식 입력을 시작한다.
검색할 값 선택 (입력 기준값)
수식에서 가장 먼저 지정할 값은 상품코드가 입력될 셀이다. 여기서는 상품코드 아래 첫 행인 B14셀을 마우스로 클릭한다.
vlookup함수는 이 셀에 입력될 값을 기준으로 상품 데이터에서 상품명을 불러온다.
표 범위 지정 (불러올 데이터 범위)
다음은 불러올 값들이 있는 표의 범위를 지정한다. 위에서 셀을 선택한 뒤 “,”를 입력한 후 다음 인수인 표 범위를 마우스로 드래그해 선택한다.
마우스로 드래그한 범위가 인수(B4:B9)로 입력되는걸 확인할 수 있다.
엑셀에서 데이터의 범위를 지정할 땐 주의할 점이 있다.
셀의 귀퉁이를 끌거나 control + D 로 자동채우기를 할 경우 지정한 데이터의 범위도 선택한 셀의 위치에 따라 변경되기 때문이다.
따라서 데이터의 범위를 고정하고 싶다면 범위를 절대값으로 지정해야 한다. 방법은 간단하다. 범위를 지정한 뒤 F4키를 누르면 된다.
F4키를 누르면 지정한 범위 B4:F9가 $B$4:$F$9 이런 형식로 바뀐다. 행과 열 문자 앞에 $기호가 각각 하나씩 붙는다. 범위 시작셀과 끝셀의 행과 열 모두를 고정하겠다는 의미다.
이렇게 절대값으로 지정하면 자동 셀채우기를 해도 지정한 범위는 변하지 않는다.
열 인덱스 번호 입력 (불어올 데이터 선택)
다음은 지정한 표 범위에서 불러올 값이 위치한 열의 번호를 입력한다. 우선 지정한 범위 뒤에 “,”를 찍어준 후 상품코드를 기준으로 상품명이 위치한 열의 순번인 2를 입력한다.
여기서 먼저 한가지 짚고 넘어가야할 것이 있다.
vlookup함수는 가장 앞 옆에 있는 검색할 값을 기준으로 원하는 값이 위치한 열 번호를 지정해 해당 값을 가져 오는 함수다.
즉, 표 범위를 지정할 때 검색할 값(여기서는 상품코드)이 가장 앞 열에 오도록 지정해야 한다. 그리고 여기서 검색할 값이 들어있는 열로부터 몇 번째 있는 열이 있는지 입력해주면 해당 값을 가져오게 되는 것이다.
범위 검색 (일치 or 유사)
마지막에 입력할 인수는 검색할 값과 정확히 일치하는 경우에만 값을 가져올지 근사한 값을 찾아 가져올지 정하는 옵션이다.
인수는 true(참)과 false(거짓) 두가지 중 하나를 선택해 입력하면 되고, 입력하지 않을 경우 true가 기본값이다.
true를 입력할 경우, 현재 상품데이터에 없는 값, 예를 들어 P006이라는 코드를 입력했을때 가장 근사값인 P005의 값을 가져오게 된다.
마찬가지로 P000을 입력하면 가장 가까운 P001 위에 있는 것으로 판단해 P001에 해당하는 값 위에 있는 C4셀의 “상품코드”라는 값을 불러오게 된다.
정확히 일치하는 값을 가져오기 위해서는 false를 입력하면 된다.
false로 지정할 경우 정확히 일치하는 값이 없을 경우 오류 메시지 즉, “#N/A”(Not Available : 찾을수 없음)를 표시하게 된다.
마지막 인수는 참/거짓만 선택할 수 있는 값, 즉 불린 값이라 1과 0으로 대체가 가능하다.
| 의미 | 키워드 입력 | 숫자로 대체 |
| 근사값 허용 | TRUE | 1 |
| 정확히 일치 | FALSE | 0 |
즉 위와 같이 입력해도 된다.
이 부분에 대해서는 이후 다른 포스팅으로 자세히 다뤄보도록 하자. 일단 일반적인 업무에서는 근사값을 가져오게 되면 자료에 구멍이 생길 수 있다. 기본적으로 FALSE(정확히 일치)를 사용하면 된다.
마지막 인수를 입력하고 엔터를 누르면 선택한 셀에 VLOOKUP 함수로 만든 수식이 입력된다.
기본적으로 아직 입력값이 없기 때문에 “#N/A”(Not Available : 찾을수 없음)로 표시된다.
나머지 셀 수식 채우기
이제 셀 하나에 VLOOKUP 함수를 이용해 원하는 값을 불러올 수 있게 됐다. 이제 값을 채우길 원하는 다른 셀들에도 VLOOKUP 함수로 수식을 채워보자.
방법은 간단하다.
바로 아래 행은 그대로 자동 채워주기를 하면 된다.
셀의 모서리를 잡아 아래로 드래그하거나 원하는 위치까지 셀을 선택한 후 control + D 를 누르면 셀들에 수식이 채워진다.
자동 채우기를 하는 경우 인수들은 셀의 위치에 따라 자동으로 변경이 된다. 아까 위에서 표 범위를 지정할 때 범위를 절대값으로 지정해준 이유가 바로 여기에 있다.
위의 화면처럼 표 범위에 절대값을 지정해놓지 않은 경우 자동채우기를 했을 때 표범위 인수가 변경되는 걸 확인할 수 있다.
표 범위를 절대값으로 묶어두면 자동 채우기를 해도 원래 지정한 대로 유지가 된다.
열이 다른 셀은 수식을 복사해 붙여넣기 한 후 인수들을 원하는 대로 수정해주면 된다.
수정 후 위에서와 마찬가지로 자동 채우기를 이용해 아래 셀들을 채워주면 된다.
검색할 값(기준값) 인수에 절대값을 적용하면 옆으로 드래그 할 때, 변경되지 않도록 할 수도 있다. F4키를 반복적으로 눌러보면 셀을 고정하거나, 열이나 행을 선택적으로 고정할 수도 있다.
마치며
솔직히 얘기하면 내 경험상 엑셀을 처음 접하는 사람이 이 VLOOKUP 함수를 선뜻 이해하는 걸 본적이 많지 않다. 물론 예외도 있긴했지만.
아무튼 막상 알고나면 정말 말도 안되게 간단하지만, 원래 올챙이적 시절을 개구리는 기억하지 못한다.
같이 일하던 친구들 중 몇몇은 굳이 복잡하게 그런 것까지 써야하냐며 주문 내역을 붙잡고 앉아 한건한건 배송비를 입력하며 매일 30분씩 시간을 보냈다.
또 몇몇은 그 시간이 아까워서인지 내가 작성한 엑셀문서를 뜯어보고, 물어봐가며 금방 배워서 같은 일을 1분도 채 안 걸리는 잡무로 만들었다.
VLOOKUP 함수 하나만 제대로 활용할 수 있게 된다면, 당신의 눈에 엑셀이라는 물건이 달라보일 것이다.
