엑셀 VLOOKUP 함수 사용법 총정리! 초보자를 위한 기본 구조부터 실무 활용 예제까지

엑셀을 사용하다 보면 수많은 자료 중에서 내가 원하는 정보만 찾아야 하는 경우가 참 많습니다.

예를 들어 직원명부에서 사원번호를 입력하면 직원 이름과 소속 부서가 자동으로 나타나게 하거나, 상품코드를 입력하면 상품명과 판매가격을 바로 확인하고 싶은 경우가 있지요.

이런 작업을 일일이 눈으로 찾아서 입력한다면 시간도 많이 걸리고 실수할 가능성도 높아집니다.

이럴 때 알아두면 매우 유용한 엑셀 함수가 바로 VLOOKUP 함수입니다.

VLOOKUP은 엑셀을 사용하는 직장인이나 사업자, 사무직 종사자라면 한 번쯤 익혀두면 좋은 대표적인 데이터 조회 함수입니다.

이번 글에서는 엑셀을 처음 사용하는 분들도 쉽게 이해할 수 있도록 VLOOKUP 함수의 기본 개념부터 사용 방법, 조건을 셀에 입력하여 활용하는 방법, 실무 예제와 자주 발생하는 오류까지 차근차근 알아보겠습니다.

1. VLOOKUP 함수란 무엇인가?

VLOOKUP은 영어의 Vertical Lookup을 줄인 말로, 우리말로 표현하면 '수직으로 찾아보기'라는 의미입니다.

쉽게 설명하면 엑셀에 입력된 표에서 내가 원하는 값을 찾아 그 값과 같은 행에 있는 다른 정보를 가져오는 함수입니다.

예를 들어 다음과 같은 직원명부가 있다고 가정해 보겠습니다.

사원번호	이름	부서	직급
1001	김철수	영업팀	과장
1002	이영희	관리팀	대리
1003	박민수	총무팀	사원
1004	최지영	영업팀	차장
1005	정현우	관리팀	부장

여기서 사원번호 1003을 입력했을 때 직원 이름인 박민수와 소속 부서인 총무팀을 자동으로 찾아오도록 할 수 있습니다.

이것이 바로 VLOOKUP 함수의 기본적인 역할입니다.

VLOOKUP 함수의 대표적인 활용 사례

• 직원번호를 입력하여 직원 이름과 부서 조회
• 상품코드를 입력하여 상품명과 판매가격 조회
• 거래처번호를 입력하여 거래처명과 연락처 조회
• 고객번호를 입력하여 고객등급 확인
• 제품번호를 입력하여 재고수량 확인
• 회원번호를 입력하여 회원정보 조회

즉, 반복적으로 자료를 검색해야 하는 업무에서 상당히 유용하게 활용할 수 있습니다.

2. VLOOKUP 함수의 기본 구조

VLOOKUP 함수는 다음과 같은 기본 구조로 사용합니다.

=VLOOKUP(찾을값,찾을범위,가져올열번호,일치옵션)

엑셀에서 실제 사용하는 함수의 인수 이름은 다음과 같습니다.

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

처음에는 괄호 안에 들어가는 항목이 많아 복잡하게 느껴질 수 있지만, 각각의 의미를 이해하면 어렵지 않습니다.

한마디로 정리하면 다음과 같습니다.

1. 무엇을 찾을 것인지?
2. 어디에서 찾을 것인지?
3. 몇 번째 열의 정보를 가져올 것인지?
4. 정확하게 일치하는 값을 찾을 것인지?

이 네 가지를 엑셀에 알려주는 것입니다.

728x90

3. VLOOKUP 함수의 인수 알아보기

VLOOKUP 함수에는 기본적으로 네 가지 인수가 있습니다.

인수	의미	설명
lookup_value	찾을 값	사원번호, 상품코드 등
table_array	찾을 범위	검색할 데이터가 있는 표
col_index_num	열 번호	결과를 가져올 열의 순서
range_lookup	일치 옵션	정확한 일치 또는 유사 일치

각 항목을 조금 더 자세히 알아보겠습니다.

① lookup_value : 찾을 값

내가 검색하고 싶은 값을 의미합니다.

예를 들어 사원번호 1003을 찾고 싶다면 1003을 입력하거나 해당 값이 입력된 셀을 지정하면 됩니다.

② table_array : 찾을 범위

검색할 자료가 들어 있는 표의 범위를 지정합니다.

예를 들어 직원명부가 A2에 있다면 다음과 같이 입력합니다.

A2:D6

수식을 다른 셀로 복사할 예정이라면 다음과 같이 절대참조를 사용하는 것이 좋습니다.

$A$2:$D$6

셀 주소 앞뒤에 $ 기호를 붙이면 수식을 복사하더라도 지정한 범위가 변경되지 않습니다.

③ col_index_num : 가져올 열 번호

검색한 값과 같은 행에서 몇 번째 열의 정보를 가져올 것인지 지정합니다.

예를 들어 검색 범위가 A2이라면 다음과 같습니다.

• A열 : 1
• B열 : 2
• C열 : 3
• D열 : 4

여기서 주의할 점은 엑셀 시트의 실제 열 번호가 아니라 지정한 검색 범위 안에서의 순서라는 것입니다.

④ range_lookup : 일치 옵션

조회할 값을 어떤 방식으로 찾을 것인지 지정합니다.

입력값의미설명

FALSE 또는 0	정확한 일치	입력한 값과 정확하게 같은 값 검색
TRUE 또는 1	유사 일치	정확한 값이 없으면 조건에 맞는 가까운 값 검색

이 항목은 특히 중요합니다.

상품코드나 사원번호처럼 정확하게 일치해야 하는 자료를 조회할 때는 대부분 FALSE를 사용하는 것이 좋습니다.

마지막 인수를 생략하면 기본적으로 TRUE가 적용되므로 초보자는 특별한 이유가 없다면 FALSE를 입력하는 습관을 들이는 것이 안전합니다.

4. VLOOKUP 실무 예제 ① 상품코드로 상품명 찾기

이제 실제 업무에서 사용하는 상황을 가정하여 연습해 보겠습니다.

다음과 같은 상품관리표가 있다고 가정하겠습니다.

상품코드	상품명	판매가격	재고수량
P001	사과	2,000	100
P002	배	3,000	80
P003	포도	5,000	50
P004	딸기	6,000	40
P005	수박	15,000	20

상품코드 P003에 해당하는 상품명을 찾아보겠습니다.

① 상품코드를 직접 입력하여 조회하기

=VLOOKUP("P003",A2:D6,2,FALSE)

수식의 의미는 다음과 같습니다.

• P003 : 찾을 상품코드
• A2 : 상품정보가 있는 범위
• 2 : 두 번째 열의 상품명을 가져오기
• FALSE : 정확하게 일치하는 값 찾기

<결과> 포도

이처럼 간단한 수식만으로 원하는 상품명을 가져올 수 있습니다.

② 상품코드를 셀에 입력하여 조회하기

실무에서는 수식 안에 상품코드를 직접 입력하기보다는 특정 셀에 상품코드를 입력하고 해당 셀을 참조하는 방법이 더욱 편리합니다.

예를 들어 다음과 같이 구성하겠습니다.

• 상품관리표 : 상품정보를 정리한 표의 이름

• A2:D6 : 실제 엑셀에서 데이터가 입력된 셀 범위

• F2 : 조회할 상품코드를 입력하는 셀

• G2 : 조회 결과인 상품명을 표시하는 셀

 

F2에 P003을 입력하고 G2에 다음 수식을 작성합니다.

=VLOOKUP(F2,$A$2:$D$6,2,FALSE)

그러면 G2에 포도라는 결과가 표시됩니다.

상품관리표와 조회할 상품코드 P003 입력 화면

G2에 VLOOKUP 수식을 입력한 화면

상품명에 포도가 표시된 결과 화면

이제 F2의 상품코드를 P001로 변경하면 사과가 표시되고, P004로 변경하면 딸기가 표시됩니다.

수식은 그대로 두고 조회할 상품코드만 변경하면 되는 것이지요.

상품코드 변경에 따른 상품명 변경 표시된 결과 화면

5. VLOOKUP 실무 예제 ② 상품가격과 재고수량 가져오기

이번에는 동일한 상품코드를 이용하여 판매가격과 재고수량까지 가져와 보겠습니다.

앞에서 사용한 상품관리표를 그대로 활용합니다.

① 판매가격 가져오기

=VLOOKUP(F2,$A$2:$D$6,3,FALSE)

세 번째 열인 판매가격을 가져오라는 의미입니다.

상품코드 P003을 입력했다면 결과는 5,000입니다.

② 재고수량 가져오기

=VLOOKUP(F2,$A$2:$D$6,4,FALSE)

네 번째 열인 재고수량을 가져옵니다.

결과는 50입니다.

이처럼 VLOOKUP은 검색하는 기준과 범위는 그대로 두고 세 번째 인수인 열 번호만 변경하면 다른 정보도 가져올 수 있습니다.

실제 회사 업무에서는 직원번호 하나로 이름, 부서, 직급을 각각 조회하거나 거래처 코드 하나로 거래처명, 담당자, 연락처 등을 가져오는 데 활용할 수 있습니다.

6. VLOOKUP 조건을 셀에 입력하여 사용하는 방법

VLOOKUP을 조금 더 편리하게 활용하려면 찾을 값뿐 아니라 가져올 열 번호도 셀에 입력하여 사용하는 방법을 알아두면 좋습니다.

예를 들어 상품코드는 F2에 입력하고, 가져올 항목의 열 번호는 G2에 입력하도록 만들어 보겠습니다.

셀	입력 내용
F2	P003
G2	2

H2에 다음 수식을 입력합니다.

=VLOOKUP(F2,$A$2:$D$6,G2,FALSE)

이제 G2의 숫자를 변경하면 결과도 함께 바뀝니다.

• G2에 2 입력 : 상품명 표시
• G2에 3 입력 : 판매가격 표시
• G2에 4 입력 : 재고수량 표시

이 방법의 장점은 수식을 매번 수정하지 않아도 된다는 점입니다.

다만 열 번호를 직접 입력해야 하므로 숫자를 잘못 입력하면 엉뚱한 결과가 나타날 수 있습니다.

실무에서는 데이터 유효성 검사 기능을 활용하여 상품명, 판매가격, 재고수량을 선택할 수 있도록 목록을 만들어두면 더욱 편리합니다.

7. VLOOKUP에서 자주 사용하는 조건과 응용 함수

실무에서는 단순히 값을 찾는 것뿐 아니라 오류를 방지하거나 조회 조건을 조금 더 편리하게 만드는 방법도 자주 사용합니다.

몇 가지 대표적인 응용 방법을 알아보겠습니다.

① 조회 결과가 없을 때 '조회 결과 없음' 표시하기

VLOOKUP으로 찾는 값이 존재하지 않으면 일반적으로 #N/A 오류가 나타납니다.

이런 오류 대신 알아보기 쉬운 문구를 표시하려면 IFERROR 함수를 함께 사용합니다.

=IFERROR(VLOOKUP(F2,$A$2:$D$6,2,FALSE),"상품 없음")

상품코드가 존재하면 상품명을 표시하고, 존재하지 않으면 '상품 없음'이라는 문구를 표시합니다.

② 조회할 셀이 비어 있으면 결과도 비워두기

조회할 상품코드를 입력하지 않았을 때 오류가 나타나지 않도록 하려면 IF 함수를 추가합니다.

=IF(F2="","",IFERROR(VLOOKUP(F2,$A$2:$D$6,2,FALSE),"상품 없음"))

이 수식은 다음과 같이 작동합니다.

• F2가 비어 있으면 빈칸 표시
• F2에 상품코드가 있으면 상품명 검색
• 상품코드가 없으면 '상품 없음' 표시

실무에서 조회용 양식을 만들 때 매우 유용합니다.

③ 일부 문자만 알고 있을 때 검색하기

상품명이나 거래처명 중 일부만 알고 있는 경우에는 와일드카드를 활용할 수 있습니다.

대표적으로 별표 *와 물음표 ?가 있습니다.

• * : 문자가 없거나 여러 개 있는 경우
• ? : 임의의 문자 한 개

예를 들어 상품명이 사과로 끝나는 자료를 찾고 싶다면 다음과 같이 사용할 수 있습니다.

=VLOOKUP("*사과",A2:D6,2,FALSE)

상품코드가 아닌 상품명 열을 기준으로 검색하려면 상품명 열이 검색 범위의 첫 번째 열이 되도록 범위를 지정해야 합니다.

④ 다른 시트에 있는 자료 조회하기

실무에서는 조회할 자료가 다른 시트에 정리되어 있는 경우가 많습니다.

예를 들어 현재 시트의 A2에 거래처코드가 있고, 다른 시트인 거래처목록에 거래처 정보가 있다면 다음과 같이 작성합니다.

=VLOOKUP(A2,거래처목록!$A$2:$D$100,2,FALSE)

거래처목록 시트의 A열에서 거래처코드를 찾고 같은 행의 두 번째 열 정보를 가져오는 것입니다.

시트 이름에 공백이 포함되어 있다면 작은따옴표를 사용합니다.

=VLOOKUP(A2,'거래처 목록'!$A$2:$D$100,2,FALSE)

⑤ 다른 엑셀 파일에 있는 자료 조회하기

다른 엑셀 파일에 있는 자료도 조회할 수 있습니다.

다만 외부 파일을 참조하는 경우 파일의 위치나 이름이 변경되면 연결에 문제가 발생할 수 있으므로 주의해야 합니다.

예를 들어 외부 파일을 열어둔 상태에서 해당 범위를 선택하면 엑셀이 자동으로 참조 주소를 만들어 주므로 초보자는 직접 경로를 입력하기보다 마우스로 범위를 선택하는 방법을 추천합니다.

8. VLOOKUP을 활용한 실무 예제 ③ 직원명부 자동 조회

이번에는 회사에서 활용할 수 있는 직원명부 예제를 살펴보겠습니다.

다음과 같은 직원자료가 있다고 가정하겠습니다.

사원번호	성명	부서	직급	입사일
1001	김철수	영업팀	과장	2020-03-02
1002	이영희	관리팀	대리	2021-05-10
1003	박민수	총무팀	사원	2022-07-15
1004	최지영	영업팀	차장	2019-02-01
1005	정현우	관리팀	부장	2015-09-01

이 자료를 이용하여 사원번호를 입력하면 성명, 부서, 직급을 자동으로 조회하는 양식을 만들어 보겠습니다.

① 조회할 사원번호 입력

F2 셀에 1003을 입력합니다.

② 성명 조회

=VLOOKUP(F2,$A$2:$E$6,2,FALSE)

결과 : 박민수

③ 부서 조회

=VLOOKUP(F2,$A$2:$E$6,3,FALSE)

결과 : 총무팀

④ 직급 조회

=VLOOKUP(F2,$A$2:$E$6,4,FALSE)

결과 : 사원

이렇게 만들어두면 사원번호만 변경해도 해당 직원의 정보가 자동으로 바뀝니다.

직원 수가 수백 명, 수천 명인 회사에서도 이러한 조회 양식을 활용하면 반복적인 자료 검색 업무를 줄일 수 있습니다.

9. VLOOKUP 사용 시 반드시 알아야 할 유의점

VLOOKUP은 편리한 함수이지만 몇 가지 제한사항과 주의할 점이 있습니다.

① 반드시 검색 범위의 첫 번째 열에서 찾아야 합니다.

VLOOKUP은 지정한 범위의 가장 왼쪽 열에서 검색합니다.

예를 들어 상품코드가 B열에 있고 상품명이 A열에 있다면, 상품코드를 기준으로 왼쪽에 있는 상품명을 가져오는 것은 일반적인 VLOOKUP으로 어렵습니다.

이런 경우에는 XLOOKUP 또는 INDEX와 MATCH 함수를 활용하는 방법이 있습니다.

② 정확한 일치가 필요한 경우 FALSE를 입력합니다.

사원번호, 상품코드, 거래처번호 등은 정확하게 일치해야 합니다.

따라서 다음과 같이 작성하는 것이 안전합니다.

=VLOOKUP(F2,$A$2:$D$6,2,FALSE)

마지막 인수를 생략하면 유사 일치가 적용될 수 있으므로 주의해야 합니다.

③ 조회 범위의 열 번호를 정확하게 지정합니다.

검색 범위가 A2이라면 A열이 1번이고 D열이 4번입니다.

실제 엑셀의 열 문자나 번호가 아닌 지정한 범위에서의 순서를 기준으로 계산해야 합니다.

④ 수식을 복사할 때 절대참조를 사용합니다.

다음과 같이 작성하면 수식을 복사할 때 조회 범위가 변경되지 않습니다.

=VLOOKUP(F2,$A$2:$D$6,2,FALSE)

반대로 $ 기호 없이 작성하면 수식을 아래로 복사할 때 범위가 이동할 수 있습니다.

⑤ 숫자와 문자의 형식이 같은지 확인합니다.

겉으로 보기에는 동일한 값이라도 한쪽은 숫자이고 다른 쪽은 문자로 저장되어 있다면 검색이 되지 않을 수 있습니다.

특히 사원번호나 상품코드처럼 앞에 0이 들어가는 자료는 데이터 형식을 주의해서 관리해야 합니다.

⑥ 중복된 값이 있는지 확인합니다.

동일한 상품코드나 사원번호가 여러 번 존재한다면 VLOOKUP은 기본적으로 첫 번째로 발견한 값을 가져옵니다.

따라서 조회 기준이 되는 값은 가능하면 중복되지 않도록 관리하는 것이 좋습니다.

⑦ 유사 일치를 사용할 때는 정렬 상태를 확인합니다.

마지막 인수를 TRUE 또는 1로 사용하는 경우 검색 기준이 되는 첫 번째 열은 오름차순으로 정렬되어 있어야 합니다.

구간별 수수료나 등급별 할인율처럼 유사 일치가 필요한 업무에서는 기준표를 정확하게 구성해야 합니다.

반응형

10. VLOOKUP 자주 묻는 질문과 답변(FAQ)

Q1. VLOOKUP 함수는 어떤 경우에 사용하나요?

A. 표에 입력된 자료 중 특정 값을 기준으로 같은 행에 있는 다른 정보를 가져올 때 사용합니다.

사원번호로 직원 이름을 찾거나 상품코드로 판매가격을 조회하는 경우가 대표적입니다.

Q2. VLOOKUP에서 #N/A 오류가 발생하는 이유는 무엇인가요?

A. 검색하려는 값이 표에 없거나 숫자와 문자 형식이 서로 다르거나 불필요한 공백이 포함되어 있을 때 발생할 수 있습니다.

조회 값과 원본 자료를 확인하는 것이 좋습니다.

Q3. VLOOKUP에서 FALSE와 TRUE의 차이는 무엇인가요?

A. FALSE는 정확하게 일치하는 값을 찾고, TRUE는 유사하게 일치하는 값을 찾습니다.

일반적인 코드나 번호 조회에서는 FALSE를 사용하는 것이 좋습니다.

Q4. VLOOKUP으로 왼쪽에 있는 값을 가져올 수 있나요?

A. 일반적인 VLOOKUP은 검색 범위의 첫 번째 열을 기준으로 오른쪽 방향의 값을 가져옵니다.

왼쪽에 있는 값을 가져오려면 XLOOKUP이나 INDEX와 MATCH 등의 다른 함수를 활용하는 것이 좋습니다.

Q5. VLOOKUP으로 여러 개의 정보를 동시에 가져올 수 있나요?

A. 기본적으로는 가져올 열 번호를 변경하여 각각의 수식을 작성합니다.

최신 엑셀에서는 CHOOSECOLS 등 다른 함수와 결합하여 여러 항목을 효율적으로 조회하는 방법도 있습니다.

Q6. 조회 결과가 없을 때 0이나 안내 문구를 표시할 수 있나요?

A. 가능합니다. IFERROR 함수를 함께 사용하면 됩니다.

=IFERROR(VLOOKUP(F2,$A$2:$D$6,2,FALSE),"자료 없음")

Q7. 다른 시트에 있는 자료도 검색할 수 있나요?

A. 가능합니다. 시트 이름과 검색 범위를 함께 지정하면 됩니다.

Q8. VLOOKUP 함수는 엑셀 초보자도 쉽게 배울 수 있나요?

A. 물론입니다. 찾을 값, 검색할 범위, 가져올 열 번호, 정확한 일치 여부라는 네 가지 원리만 이해하면 기본적인 조회 작업은 충분히 할 수 있습니다.

Q9. VLOOKUP 함수와 XLOOKUP 함수는 무엇이 다른가요?

A. VLOOKUP은 검색 범위의 첫 번째 열을 기준으로 결과를 가져오는 반면, XLOOKUP은 검색할 범위와 결과를 가져올 범위를 각각 지정할 수 있어 좌우 방향으로 자유롭게 조회할 수 있습니다.

Q10. VLOOKUP은 최신 엑셀에서도 사용할 수 있나요?

A. 네. VLOOKUP은 현재 사용되는 Microsoft Excel에서도 널리 지원되는 함수입니다. 기존 업무자료와의 호환성이 좋다는 장점이 있습니다.

엑셀 숫자로 된 금액을 한글로 변경하기 / 숫자를 텍스트로 변경

엑셀 통계 함수 AVERAGEIF와 AVERAGEIFS, 초보자도 쉽게 이해하기

11. 마무리하며

지금까지 엑셀에서 자주 사용하는 VLOOKUP 함수의 기본 개념부터 실무 활용 방법까지 살펴보았습니다.

처음에는 수식이 길고 괄호 안에 들어가는 인수도 많아 조금 어렵게 느껴질 수 있습니다.

하지만 한 번만 직접 입력해 보고 결과를 확인하면 생각보다 쉽게 이해할 수 있습니다.

특히 회사에서 직원명부나 거래처 자료, 상품별 가격표, 재고관리표 등을 다루는 분이라면 VLOOKUP 함수 하나만 제대로 익혀도 업무에 많은 도움이 됩니다.

엑셀을 잘 활용하는 방법은 복잡한 수식을 무조건 외우는 것이 아니라, 반복적으로 해야 하는 작업을 함수로 줄여나가는 데 있습니다.

오늘 소개한 상품코드 조회 예제부터 직접 따라 해보시고, 익숙해지면 직원관리나 거래처관리 자료에도 적용해 보시기 바랍니다.

작은 기능 하나를 익히는 것부터 시작해도 엑셀을 활용하는 능력은 조금씩 달라질 것입니다.

다음 글에서는 VLOOKUP의 불편한 점을 개선한 XLOOKUP 함수의 사용법과 실무 활용 방법을 별도로 자세히 알아보겠습니다.