엑셀 VLOOKUP 함수 사용법 및 실전예제 총정리 :: 참조 함수

세로로 입력된 자료에서 원하는 값을 찾아 옆에 위치한 다른 값을 참조하는 VLOOKUP 함수의 사용법 및 주의사항을 알아봅니다.

홈페이지 » 엑셀 VLOOKUP 함수 사용법 총정리 :: 참조 함수

엑셀 VLOOKUP 함수 사용법 및 예제 총정리 :: 참조함수

VLOOKUP 함수 목차 바로가기
함수 구문
= VLOOKUP ( 찾을값, 참조범위, 열번호, [일치옵션] )
VLOOKUP 쉽게 사용하는 방법 (4단계만 기억하세요!)

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

  1. 찾을값을 선택합니다. (예제로 '오늘의커피' 가격을 검색해볼께요!)

    엑셀 VLOOKUP 함수 사용법 클릭
    찾을 값을 선택합니다.

  2. 값이 입력된 범위를 드래그~ 한 뒤, F4키로 절대참조!
    VLOOKUP 예제 범위 드래그
    찾을 범위를 드래그한 뒤, F4키를 눌러 절대참조로 변경합니다.
    오빠두Tip : 찾을 범위에서 찾을값은 반드시 첫번째열(맨 왼쪽) 입력되어 있어야 합니다!
  3. 출력할 값의 열 번호 입력!

    VLOOKUP 열번호 입력
    출력할 값의 열번호를 입력합니다.

  4. 마지막 인수로 0 입력 후 엔터!

    마지막 인수 0
    일치옵션은 0(=정확히일치)로 입력합니다. (99.9% 정확히 일치로 사용!)

  5. 오늘의커피 가격이 검색되었습니다~😊

    엑셀 VLOOKUP함수 설명
    짜잔! 값이 검색 완료되었습니다.

인수 알아보기
엑셀 vlookup 함수 설명
엑셀 VLOOKUP 함수 설명
인수 설명
찾을값 참조범위의 첫번째열(맨 좌측)에서 검색할 값입니다.
참조범위 참조할 전체범위입니다. (일반적인 사용시, '절대참조($)'로 입력합니다.)
열번호 출력할 값이 참조범위에서 위치하는 열번호를 입력합니다.

열번호는 참조범위의 첫번째 열(=1)을 시작으로 1씩 증가합니다.

일치옵션
[선택인수]
찾을값의 일치옵션입니다. 기본값은 TRUE (유사일치) 이며, 유사일치시 찾을값보다 '작거나 같은 값중 최대값'을 조회합니다.
  • 실무에서는 FALSE 또는 0을 입력하여 '정확히일치' 옵션으로 사용하는것이 일반적입니다.
  • 유사일치로 사용할 경우 참조범위의 첫번째열은 반드시 '오름차순'으로 정렬되어야 합니다.

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀함수] 엑셀 VLOOKUP 함수 사용법
    예제파일

VLOOKUP 함수 알아보기

함수 설명

VLOOKUP 함수는 표의 맨 좌측에서 찾으려는 값을 검색한 뒤, 동일한 행에 위치한 다른 값을 출력하는 함수입니다. (=찾아주는 함수!)

VLOOKUP 함수는 엑셀 실무에서 가장 많이 사용되는 필수함수로 실무자라면 반드시 숙지해야하는 중요함수인데요. 대부분의 경우에는 VLOOKUP 함수로 해결할 수 있지만, VLOOKUP 함수로 해결할 수 없는 상황에는 아래 함수/공식을 사용합니다.

사용예시
엑셀 VLOOKUP 함수 사용예제
VLOOKUP 함수는 실무자가 반드시 알아야 할 필수함수입니다.
관련 기초함수

엑셀 VLOOKUP 함수 사용시 주의사항

1. 일치 옵션
엑셀 오름차순 정렬
일치옵션을 유사일치로 사용할 경우 첫번째 열은 반드시 오름차순으로 정렬되어야 합니다.
  • VLOOKUP 함수의 [일치옵션]은 2가지가 있으며, 기본값은 TRUE 또는 1 (=유사일치) 입니다.
    - TRUE (또는 1) = 유사일치
    - FALSE(또는 0) = 정확한 일치
  • 실무에서는 FALSE (정확한 일치)로 대부분 사용합니다.
  • 일치옵션으로 TRUE (유사일치)를 사용할 경우 참조범위의 맨 좌측 열(첫번째 열)은 반드시 오름차순으로 정렬되어야 합니다.
  • 만약 일치옵션이 TRUE(유사일치)이고 참조범위 첫번째 열에서 정확히 일치하는 값이 없을경우, 찾을값보다 작거나 같은 값 중 최대값을 반환합니다.
    만약 찾을값이 범위의 최소값보다도 작아 반환할 값이 없을 경우 VLOOKUP 함수는 #N/A 오류를 반환합니다.
2. 다른 시트에서 조회하기
= VLOOKUP ( "사과", 'Sheet2'!A2:B10, 2, FALSE )

참조범위 앞에 '시트명'! 을 입력할 수도 있습니다. [ '시트명'!범위 ]로 값을 입력하면 다른시트의 값을 조회할 수 있으며, 또는 참조범위를 마우스로 직접선택하면 자동으로 참조범위에 시트명이 추가됩니다.

엑셀 다른 시트 참조
범위앞에 '시트명!'을 입력하여 다른 시트를 참조할 수 있습니다.
3. 참조범위를 절대참조($)로 입력하기
=VLOOKUP($B$7,$B$10:$E$18,3,FALSE)

VLOOKUP 함수는 대부분의 실무에서는 '고정된 참조범위'를 대상으로 사용합니다. 즉, 여러 값을 검색하기 위해 함수를 아래방향으로 자동채우기 하더라도, 참조범위는 고정되도록 만들어주게 됩니다.

따라서 이런 경우에는, 참조범위를 반드시 F4키를 눌러 절대참조($) 형식으로 입력해야 합니다. 그렇지 않으면, 자동채우기 할 시 참조범위도 같이 이동하게 되어 VLOOKUP 함수의 결과값으로 옳지않은 값이 출력됩니다.

VLOOKUP 함수 절대참조
참조범위는 F4키를 눌러 절대참조하는것이 일반적입니다.
4. VLOOKUP 함수는 왼쪽으로 조회가 불가능
=VLOOKUP($B$7,$B$10:$E$18,-1,FALSE)
'// 열번호로 음수 값은 입력할 수 없습니다.

VLOOKUP 함수의 찾을값은 반드시 범위의 맨 왼쪽(첫번째 열)에 있어야 합니다. 다시말해, VLOOKUP 함수는 찾을값의 오른쪽방향으로만 값을 조회할 수 있습니다.

만약 찾을값 범위가 맨 왼쪽에 있지 않거나, 또는 찾을값 범위에서 왼쪽방향으로 값을 조회해야 할 경우 XLOOKUP 함수(오피스 365 또는 최신버전) 또는 INDEX/MATCH 함수를 사용합니다.

자주묻는질문

Q1. VLOOKUP 함수 사용시 #N/A, #REF, #VALUE, #NAME 오류가 출력됩니다.

VLOOKUP 함수 사용시, 상황에 따라 아래 오류가 출력 될 수 있으며, 오류가 출력되는 일반적인 원인은 다음과 같습니다.

오류 원인
 #N/A
  • 일치 옵션에 따라 #N/A 오류 발생원인이 다릅니다.
    일치옵션이 TRUE(유사일치)인 경우
    : 찾을값이 참조범위 첫번째 열에 없고, 범위안의 최소값보다도 작을 경우 #N/A 오류를 반환합니다.
    일치옵션이 FALSE(정확한일치)일 경우
    : 찾을값이 참조범위 첫번째 열에 없을때 #N/A 오류를 반환합니다.
  • 또는 찾을값에 "~(물결표)"가 들어있을경우 #N/A 오류를 반환할 수 있습니다.
    이는 와일드카드 사용시 주의사항으로, "~(물결표)"가 들어있을 시 물결표를 2개(~~)로 변경하여 검색합니다.
 #REF! 참조범위의 넓이보다 큰 수의 열번호를 입력하면 #REF! 오류를 반환합니다.

예를들어 좌우로 3칸(=3개의 열) 인 참조범위를 입력하였는데, 열번호로 4를 입력할 경우 #REF! 오류를 반환합니다.

 #VALUE!  2가지 경우가 있습니다.
  • 찾을값의 글자길이가 255자를 초과할 경우 발생할 수 있습니다. 이 경우 INDEX 함수와 MATCH 함수를 조합하여 문제를 해결합니다.
  • 열번호를 잘못 입력하였을 경우 발생할 수 있습니다. 예를들어 열번호에 숫자가 아닌 텍스트 또는 0보다 작은수를 입력할 경우 #VALUE 오류를 반환합니다.
 #NAME?  수식이 잘못 입력되었을 경우 #NAME 오류를 반환하며 대표적인 원인은 크게 2가지입니다.
  • 찾을값이 텍스트일 경우 반드시 큰따옴표(")로 감싸주어야 합니다.
  • VLOOKUP 함수명을 정확히 입력하였는지 확인합니다.
Q2. VLOOKUP 함수 참조범위에 열을 추가하거나 삭제하면 출력값이 변하거나 #REF! 오류가 발생합니다.

VLOOKUP 함수의 참조범위 중간에 열을 추가하거나 삭제하게되면, 출력되는 값의 위치도 같이 변하게되어 옳지않은 값 또는 #REF 오류가 반환될 수 있습니다.

이 문제를 해결하기 위하여 열번호로 들어가는 인수를 COLUMNS 함수 또는 MATCH 함수로 대체하면 이 문제를 해결할 수 있습니다.

  1. COLUMNS 함수를 사용하는 경우
    = VLOOKUP ( 찾을값, 참조범위, COLUMNS(실제출력범위), [일치옵션] )
    '// 실제출력범위: 참조범위의 첫번째 열부터 실제로 출력되는 열까지의 머릿글입니다.
    VLOOKUP 함수 COLUMN 함수 응용
    COLUMNS 함수를 응용하면 열번호를 동적으로 적용할 수 있습니다.

    예를들어 B:E 범위를 참조범위로 넣고, 3번째 열인 D열의 값을 출력할 경우는 아래와 같습니다.
    - 참조범위 : B:E
    - 실제출력범위: B:D
    - 실제출력되는 범위는 머릿글 범위만 입력해도 괜찮습니다. (예: A1:E1, 등)

  2. MATCH 함수를 사용하는 경우
    = VLOOKUP ( 찾을값, 참조범위, MATCH(머릿글, 머릿글범위,0), [일치옵션] )
    '// 머릿글 : 출력할 값의 머릿글입니다.
    '// 머릿글범위 : 참조범위의 전체 머릿글범위입니다.
    엑셀 VLOOKUP 함수 MATCH 응용 GIF
    MATCH 함수를 사용하면 범위를 가로/세로로 검색할 수 있습니다.

    - 머릿글범위는 참조범위와 동일한 넓이로 입력합니다.
    - MATCH 함수를 응용하면 가로/세로 값을 동적으로 조회하는 VLOOKUP 함수 공식을 작성할 수 있습니다.

[링크] MS OFFICE 공식 홈페이지 VLOOKUP 함수 설명

4.9 34 투표
게시글평점
guest
52 댓글
Inline Feedbacks
모든 댓글 보기
찌이이
찌이이
2020년 2월 9일 12:16 오전
게시글평점 :
     

INDEX랑 MATCH로 조합해서 섰는데 ㅠㅠ

김영석
김영석
2020년 3월 14일 9:02 오전
게시글평점 :
     

COLUMNS 범위를 B4:D4 또는 C4:D4, C4:E4 이렇게 바꿨을때의 차이가 뭔가요??

Joo
Joo
2020년 3월 31일 4:01 오후
게시글평점 :
     

안녕하세용 =VLOOKUP( 하고 아이디 클릭을(B7) 하고 나중에 그 아이디 바꿔가면서도 적용되게 하려면 어떻게 해야 하는 건가요?

216
216
2021년 11월 26일 11:21 오전
답글 남기기  오빠두엑셀

안녕하세요, 좋은 글 정말 감사합니다!
혹시 게시글에 움직이는 이미지로 보여주신 것처럼 리스트 쭉 떨어지게 하려면 다른 수식이 더 필요한건가요?

풍경소리
풍경소리
2020년 4월 4일 6:32 오후
게시글평점 :
     

알고 싶었던 함수인데 너무 감사합니다~

곰야차
곰야차
2020년 4월 7일 6:19 오후
게시글평점 :
     

꼼꼼, 꼼꼼 ㅎㅎ

닥코드
닥코드
2020년 4월 8일 10:13 오전
게시글평점 :
     

좋은 내용 감사합니다.

약돌한우
약돌한우
2020년 4월 15일 12:23 오전
게시글평점 :
     

좋은 자료 감사합니다.

hih****
2020년 4월 25일 2:49 오후
게시글평점 :
     

확실할 설명 너무 감사합니다.

해피엔딩
2020년 5월 9일 8:14 오전
게시글평점 :
     

기본 강의 듣다가 함수 설명을 들어야 할거 같아서 공부했는데 이해가 쏙쏙 됩니다.
감사합니다. ~~

뿌까뿌가
뿌까뿌가
2020년 7월 1일 4:38 오후
게시글평점 :
     

좋은 강의 감사합니다.

뿌까뿌가
뿌까뿌가
2020년 7월 3일 2:34 오후
게시글평점 :
     

감사합니다.

tpHCM
tpHCM
2020년 7월 4일 10:17 오전
게시글평점 :
     

#N/A로 출력되는 값들을 다른 형태로(Ex:0) 나타내고자 하는데 방법이 있을까요?

tpHCM
tpHCM
2020년 7월 4일 11:39 오전
답글 남기기  오빠두엑셀

감사합니다 ^^

류채영
류채영
2020년 7월 9일 1:39 오후
게시글평점 :
     

sheet1의 B8은 어떻게 설정하신 건가요??

엠니아
엠니아
2020년 7월 16일 11:17 오전
게시글평점 :
     

감사합니다

suns****
suns****
2020년 8월 20일 4:04 오후
게시글평점 :
     

감사합니다.

박미정
박미정
2020년 9월 1일 6:53 오전
게시글평점 :
     

A선생님이 2명아동 수업을 진행했을때
1번아동 오전10시~12시까지 이고
2번아동 오전10시30분~12시30분 일경우 A선생님이 1번과 2번아동을 2명 동시간대 수업한 시간과 아동1명만 수업한 시간을 엑셀 자동 서식으로 가능할까요? 가능하다면 도와주세요~~

Last edited 1 년 전 by 박미정
당신을축복하는올빼미
당신을축복하는올빼미
2020년 9월 3일 9:06 오전
게시글평점 :
     

좋은내용 배우고 갑니다

동동이
동동이
2020년 10월 13일 5:45 오후
게시글평점 :
     

많은 도움 되었습니다.

청춘구팔
청춘구팔
2020년 10월 18일 3:56 오후
게시글평점 :
     

감사합니다

이준환
이준환
2020년 10월 27일 9:43 오전
게시글평점 :
     

좋은 강의 감사합니다^^

lake****
lake****
2020년 11월 5일 2:04 오후
게시글평점 :
     

감사합니다~

서승환
서승환
2020년 11월 12일 12:05 오후
게시글평점 :
     

10년만에 엑셀을 할려니 눈에 들어오는게 하나도 없습니다 어떡합니까 도표를 만들어야 되는데 방법이 없을까요

Decker
Decker
2020년 11월 21일 7:31 오후
게시글평점 :
     

유용한 가르침 감사합니다.

나무보살
나무보살
2020년 12월 9일 3:37 오후
게시글평점 :
     

사장 유용하게 자주쓰는 함수 다시한번 공부하고 갑니다

동탄주몽
동탄주몽
2021년 3월 29일 2:42 오후
게시글평점 :
     

VLOOKUP에 MATCH 조합은 매우 유용하네요.. 감사합니다.

황승준
황승준
2021년 4월 1일 5:25 오후
게시글평점 :
     

혹시 수식으로 이루어진 결과값을 불러오려고 할때 특별히 주의해야 할 사항이 있을까요?
수식으로 된 값을 땡겨오려고 하는데 자꾸 "0"를 리턴하네요..
아니면 찾을 값을 left함수를 써서 가공을 했는데 여기서 무슨 문제가 있는걸까요?

팡이
팡이
2021년 4월 2일 10:22 오전
답글 남기기  황승준

이 질문은 팡이라는 아이디로 로그인해서 다시 상세하게 질문 드렸습니다!!

팡이
팡이
2021년 4월 2일 10:22 오전
게시글평점 :
     

안녕하세요! 오빠두 엑셀님. 너무너무 잘 보고 있습니다ㅠ 문서작업을 하다 막히는 부분이 생겨 질문 남깁니다. =IFERROR(VLOOKUP(H4,$M$3:$N$33,2,FALSE),0) <시트명: 명단> 먼저 위와 같은 함수로 결과값(A라 부르겠습니다)을 도출해냈습니다. 그리고 다른 시트에서 A를 불러오기 위해… 더보기 »

팡이
팡이
2021년 4월 2일 3:58 오후
답글 남기기  팡이

해결했습니다..ㅠㅠ 제가 멍청했네요 수고하세요~

이승원
이승원
2021년 4월 27일 11:41 오후
게시글평점 :
     

한셀에선 안되는줄알고 완전히 새로운 방식으로 함수를 만들었는데 문제는 제 손이었군요..ㅠ ctrl+shift+enter를 안해서 안됐던거라니..
오늘 배워간걸로 내일 출근하면 싹다 바꿔야겠어요ㅎㅎ

이승원
이승원
2021년 4월 27일 11:41 오후
게시글평점 :
     

항상 좋은 강의해주셔서 진심으로 감사합니다!

김생민
김생민
2021년 5월 3일 10:57 오전
게시글평점 :
     

좋은 정보 감사드립니다

케인인
케인인
2021년 5월 14일 11:26 오전
게시글평점 :
     

사용예시에서 이메일을 나오게 하는데 왜 3이아니라 2가 되는건지 궁금하네요.

김상진
김상진
2021년 5월 17일 5:40 오후
게시글평점 :
     

안녕하세요 도저히 찾을수가 없어서 혹시나해서 문의드립니다 해당 함수랑 비슷한거긴한데.. ㄱㄱㄱ / 010-4500 / 서울시 강남구 ㄴㄴㄴ/ 010-7897 / 서울시 영등포구 ㄷㄷㄷ/ 010-4567 / 경기도 동두천시 ㄹㄹㄹ/ 010-1234 / 서울시 영등포구… 더보기 »

dreamwalke
dreamwalke
2021년 6월 18일 7:09 오후
게시글평점 :
     

안녕하세요... 자영업을 하고 있습니다. 각 월별로 시트를 만들어서 사용중입니다(1월~12월). VLOOKUP함수를 이용해서 주문번호로 검색해서 각각의 항목들 값을 뽑아내는데요.(예를 들어 이름, 주소, 전화번호 등등) 하나의 시트내에서는 잘 사용을 하고 있습니다만. 이를 전체… 더보기 »

cumanio
cumanio
2021년 9월 18일 12:30 오후
게시글평점 :
     

안녕하세요
다른건 이해 했는데 제가 표시한 부분은 어케 만드는건가요?

asd.jpg
하월시아
하월시아
2021년 11월 8일 5:47 오후
게시글평점 :
     

안녕하세요 함수좀...
A1셀에 함수 결과숫자를 B1함수를 사용하여 A1 셀값을 C1 셀에 값만 보내는 방법을 알껴 주십사와요.

아내에게충성
아내에게충성
2021년 11월 9일 3:02 오후
게시글평점 :
     

오빠두 때문에 퇴근이 조금씩 당겨지고 있습니다~

52
0
여러분의 생각을 댓글로 남겨주세요.x