엑셀 VLOOKUP 함수는 범위의 첫번째 열을 기준으로 값을 찾아, 다른 열의 값을 반환하는 함수입니다.
=VLOOKUP(찾을값, 참조범위, 열번호, [일치옵션])
일반적인 사용시, '절대참조(F4)'로 입력합니다.
첫 번째 열은 1부터 시작합니다.
- 실무에서는 주로 FALSE(또는 0) 을 입력하여 '정확히 일치'로 검색합니다.
- 유사일치는 찾을 값보다 '작거나 같은 값'을 검색하며, 유사 일치로 검색할 경우 참조범위의 첫번째 열은 반드시 오름차순으로 정렬되어야 합니다.
VLOOKUP 함수는 범위의 첫번째 열(맨 좌측)을 기준으로 값을 찾아 다른 열의 값을 반환하는 엑셀의 대표적인 함수입니다. 함수의 'V'는 세로(Vertical)을 의미하고, 비슷한 함수로 HLOOKUP 함수는 가로(Horizontal) 방향의 데이터를 검색합니다.
VLOOKUP 함수를 처음 사용할 경우, 아래 4단계 순서대로 작성하면 편리하게 적용할 수 있습니다.
초보자를 위한 VLOOKUP 함수 기초 사용법은 아래 영상 강의에서 알기 쉽게 정리했으니 참고해주세요!👇
실무에서 필요한 대부분의 검색 업무는 VLOOKUP 함수로 해결할 수 있지만, 아래의 경우에는 다른 함수를 사용합니다.
- 가로로 입력된 자료 : HLOOKUP 함수 또는 XLOOKUP 함수를 사용합니다.
(XLOOKUP 함수는 엑셀 2021 이후 버전부터 제공됩니다.) - 검색 기준이 첫번째 열이 아닌 경우 : INDEX/MATCH 공식을 사용합니다.
- 여러 검색 결과를 반환해야 할 경우 : VLOOKUP 여러개 공식 또는 FILTER 함수를 사용합니다.
(FILTER 함수는 엑셀 2021 이후 버전부터 제공됩니다.)
VLOOKUP 함수는 유사일치를 기본 값으로 검색합니다. 실무에서는 일치 옵션을 '정확히 일치(=FALSE, 0)'로 사용하는 것이 일반적입니다.
=VLOOKUP(찾을값,범위,열번호,TRUE)/ / (또는 1) 유사일치 검색 [기본값] =VLOOKUP(찾을값,범위,열번호,FALSE)/ / (또는 0) 정확한 일치
만약 유사일치로 검색할 경우, 참조범위의 맨 좌측 열(첫번째 열)은 반드시 오름차순으로 정렬되어야 합니다.
유사 일치는 찾을 값보다 작거나 같은 값 중 가장 큰 값을 검색합니다. 만약 찾을 값이 범위의 최소값보다도 작아 반환할 값이 없을 경우 VLOOKUP 함수는 #N/A 오류를 반환합니다.
=VLOOKUP(100,{70;80;95;98;102;110},1) / / 100보다 작은 값중 가장 큰 값인 98을 반환합니다.
실무에서 VLOOKUP 함수를 사용할 때는 다음과 같이 참조 범위를 절대참조($)로 고정하는 것이 일반적입니다.
=VLOOKUP($B$7,$B$10:$E$18,3,0)/ / 참조범위를 절대참조로 고정합니다.
위와 같이 참조 범위를 절대참조로 설정하면, 여러 값을 검색하기 위해 VLOOKUP 함수를 아래로 자동채우기해도 참조범위는 고정할 수 있습니다.
다른 시트의 데이터를 검색하려면 범위 앞에 '시트명'!을 추가합니다. 일반적으로 다른 시트의 범위를 마우스로 선택하여 범위를 지정하면, 자동으로 참조 범위 앞에 해당 시트명이 추가됩니다.
=VLOOKUP("사과",'Sheet2'!A2:B10,2,0) / / Sheet2 시트의 A2:B10 범위를 검색합니다.
VLOOKUP 함수의 찾을 값은 반드시 참조 범위의 첫번째 열에 위치해야 합니다. 즉, VLOOKUP 함수는 첫번째 열을 기준으로 오른쪽으로만 값을 조회할 수 있습니다. 만약 찾을 값 기준 왼쪽으로 검색해야 할 경우, INDEX/MATCH 공식 또는 XLOOKUP 함수를 사용합니다.
=VLOOKUP($B$7,$B$10:$E$18,-1,FALSE) / / 열번호로 음수는 입력할 수 없습니다.
VLOOKUP 함수 사용 시, 주로 발생하는 오류와 그 원인은 다음과 같습니다.
- #N/A 오류 :
TRUE(유사일치) - 찾을 값이 참조 범위 첫 번째 열에 없고, 범위 내 최소값보다 작을 경우 발생합니다.
FALSE(정확히 일치) - 찾을 값이 참조 범위 첫 번째 열에 없을 경우 발생합니다.
또는, 찾을 값에 물결표()가 포함된 경우 발생할 수 있습니다. 이 경우, 찾을 값의 물결표()를 두 개(~~)로 변경하여 해결할 수 있습니다. - #REF! 오류 :
참조 범위의 열 개수보다 큰 열번호를 입력한 경우 발생합니다. - #VALUE! 오류 :
찾을 값의 글자 길이가 255자를 초과했거나, 열 번호에 숫자가 아닌 값을 입력했거나, 열 번호로 0보다 작은 수를 입력한 경우 발생합니다. - #NAME? 오류 :
수식에서 잘못된 함수명을 사용(예: VLOKUP)하거나 찾을 값의 문자를 큰따옴표(" ")로 감싸지 않은 경우 발생합니다.
VLOOKUP 함수를 사용할 때, 참조 범위에 열을 추가하거나 삭제하면 결과값이 변하거나 #REF 오류가 발생할 수 있습니다. 이는 열번호가 고정된 상태에서 참조 범위의 너비가 달라지면, 열번호에 해당하는 값이 달라지거나 없는 열을 참조하게 되어서 그렇습니다.
이러한 문제를 해결하기 위해, MATCH 함수를 사용하여 열번호를 동적으로 지정할 수 있습니다. (=VLOOKUP 가로-세로 검색)
=VLOOKUP(찾을값,참조범위,MATCH(머릿글,머릿글범위,0),[일치옵션])
- 찾을값: VLOOKUP 함수에서 찾고자 하는 값
- 참조범위: 검색할 데이터 범위
- 머릿글: 반환할 값이 있는 열의 머릿글.
- 머릿글범위: 참조 범위의 머릿글 범위.
- 일치옵션: VLOOKUP 함수의 일치 옵션
예를 들어, 아래와 같은 데이터에서 "사과"를 찾아 가격을 출력하려면, 다음과 같이 수식을 작성합니다.
=VLOOKUP("사과",A1:D11,MATCH("가격",A1:D1,0),0) / / A1:D11 범위에서 "사과"의 가격을 머리글 위치에 상관없이 검색합니다.
