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

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

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

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

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

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

일치옵션
[선택인수]
찾을값의 일치옵션입니다. 기본값은 TRUE (유사일치) 이며, 유사일치시 찾을값보다 '작거나 같은 값중 최대값'을 조회합니다.

  • 실무에서는 FALSE 또는 0을 입력하여 '정확히일치' 옵션으로 사용하는것이 일반적입니다.
  • 유사일치로 사용할 경우 참조범위의 첫번째열은 반드시 '오름차순'으로 정렬되어야 합니다.

예제파일 다운로드

VLOOKUP 함수 알아보기

함수 설명

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

VLOOKUP 함수는 엑셀 실무에서 가장 많이 사용되는 필수함수로 실무자라면 반드시 숙지해야하는 중요함수입니다.

호환성
 Windows 버전  모든 버전에서 사용 가능합니다.
 Mac 버전  모든 버전에서 사용 가능합니다.
사용예시
엑셀 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 함수 공식을 작성할 수 있습니다.

관련 함수 공식
VLOOKUP 참조 범위의 마지막 값을 반환합니다.
INDEX / MATCH 함수의 다양한 실전 응용방법을 알아봅니다.
여러 조건을 만족하는 VLOOKUP 검색 공식입니다.
INDEX/MATCH 함수보다 더욱 간단하게 작성 가능한 VLOOKUP 양방향 검색 공식입니다.
서로 다른 여러 범위를 VLOOKUP 함수로 조회합니다.

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

5 23 투표
게시글평점
guest
30 댓글
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) 하고 나중에 그 아이디 바꿔가면서도 적용되게 하려면 어떻게 해야 하는 건가요?

풍경소리
풍경소리
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 3 월 전 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 오후
게시글평점 :
     

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

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