엑셀 VLOOKUP 함수는 셀 범위에서 원하는 값을 찾을 때 사용하는 함수입니다.
VLOOKUP이라는 함수 이름은 Vertical Lookup을 줄여서 쓴 것으로 다음과 같은 자료에서 '범위를 수직으로 내려가면서 값을 찾는다'는 의미를 담고 있습니다.
※ 만약 찾을 자료가 위와 같은 세로 모양이 아니라 가로로 되어 있다면 VLOOKUP 함수가 아니라 수평으로 값을 찾아주는 HLOOKUP 함수를 사용하면 됩니다.
구문(Syntax)
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP (값을 찾을 키값, 범위, 열번호, [일치여부])
범위(table_array)의 첫번째 열을 수직으로 내려가면서 키값(lookup_value)를 찾은 다음,
같은 행에 있는 지정된 열(col_index_num)의 값을 반환한다.
인수 :
- lookup_value : 범위에서 원하는 값을 찾기 위한 키값
- table_array : 값을 찾을 범위
- col_index_num : 값을 찾을 범위에서 가져올 값이 있는 열의 위치
- [range_lookup] : 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정(TRUE-근사값,FALSE-일치하는 값)
(range_lookup이 TRUE(근사값 찾기)이거나 생략되어 있으면 첫 번째 열이 오름차순으로 정렬되어 있어야 한다. 첫 번째 열이 정렬되어 있지 않으면 잘못된 값이 반환될 수 있다)
1. 기본 사용법
1) 상품코드로 상품명 찾기
아래와 같이 상품 정보가 있다고 할 때 상품코드에 해당하는 상품명을 찾아 보겠습니다.
상품코드 'A003'에 해당하는 상품명 'USB허브'를 찾으려면
다음과 같이 수식을 입력합니다.
=VLOOKUP("A003",B5:D14,2,FALSE) => 'USB허브'
위의 표에서 상품코드 'A003'에 해당하는 상품명 'USB허브'를 찾아 줍니다.
상품명 'USB허브'를 찾는 과정을 자세히 살펴보면 다음과 같습니다.
① [B5:D14] 범위의 첫 번째 열에서 수직으로 내려가면서 상품코드 'A003'을 찾은 후에
② 같은 행의 2번째 열에서 'USB허브'를 찾는다.
2) 상품코드로 단가 찾기
상품코드 'A007'에 해당하는 단가 207,000원을 찾으려면
다음과 같이 수식을 입력합니다.
상품명을 찾는 수식과 비교해 보면, 단가는 3번째 열에 있으므로 세 번째 인수를 2대신 3으로 입력하는 것만 다릅니다.
=VLOOKUP("A007",B5:D14,3,FALSE) => 207000
위의 표에서 상품코드 'A007'에 해당하는 단가 207000 원을 찾아 줍니다.
단가 207000 원을 찾는 과정을 자세히 살펴보면 다음과 같습니다.
① [B5:D14] 범위의 첫 번째 열에서 수직으로 내려가면서 상품코드 'A007'을 찾은 후에
② 같은 행의 3번째 열에서 207000 원을 찾는다.
2. 실무 예제
위에서는 함수의 원리 이해를 위한 간단한 예를 살펴보았는데 이번에는 실무 예제를 다루어 보겠습니다.
아래와 같이 판매 실적 자료를 만들어야 하는데 '상품코드'는 이미 입력되어 있고 상품정보에서 '상품명'과 '단가'를 가져오는 경우입니다.
1) 상품명 가져오기
상품명을 가져오기 위해 [D29] 셀에 다음 수식을 입력합니다.
=VLOOKUP(C29,$B$5:$D$14,2,FALSE)
앞에서 살펴본 수식과 거의 비슷합니다. 상품코드를 직접 입력하는 대신 셀에 입력된 상품코드를 참조하고 값을 찾을 범위가 절대참조(다른 곳에 수식을 복사해서 붙여 넣을 때 참조 위치가 밀리지 않고 그대로 유지되도록 하기 위해)로 바뀐 것만 다릅니다.
2) 단가 가져오기
단가를 가져오기 위해 [E29] 셀에 다음 수식을 입력합니다.
=VLOOKUP(C29,$B$5:$D$14,3,FALSE)
마지막으로 [D29], [E29] 셀에 입력된 수식을 복사하여 아래의 나머지 범위에 붙여 넣으면 판매 실적 자료가 완성됩니다.
※ VLOOKUP 함수 사용 시 주의
1) 찾는 값이 없으면 #N/A 오류 발생
VLOOKUP 함수를 사용하면서 가장 자주 보는 오류는 아마 #N/A일겁니다. #N/A오류는 '찾는 값이 없다'는 것을 뜻합니다.
주로 키값을 잘못 입력하거나 범위를 잘못 지정해서 오류가 발생합니다.
=VLOOKUP("A999",B5:D14,2,FALSE) => #N/A
표에 없는 'A999'라는 키값을 찾을 때 오류 발생
=VLOOKUP("A003",E5:G14,2,FALSE) => #N/A
찾는 키값은 제대로 입력했지만 값을 찾는 범위를 잘못 지정했을 때 오류 발생
2) 첫 번째 열이 아니라 다른 열에 키 값이 있다면?
VLOOKUP 함수로 범위에서 값을 찾을 때는 아래와 같이 찾는 키값이 항상 범위의 첫번째 열에 있어야 합니다.
찾는 키 값이 첫번째 열이 아니라 다른 열에 있다면 VLOOKUP 함수로는 해결이 안되므로 다음과 같이 다른 방법으로 해결해야 합니다.
XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결) - XLOOKUP 함수는 기존 VLOOKUP, HLOOKUP 함수의 몇 가지 단점(찾는 키값이 항상 1열에 있어야 하고, 가로로 된 표는 VLOOKUP 대신 HLOOKUP… …
VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 - 다음과 같은 표에서 상품코드 'A005'에 해당하는 상품 '마우스패드'를 찾아야 한다면 어떻게 해야 할까요? 찾는 키값이 첫 번째 열이 아니라 다른… …
관련 글
HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기 - 엑셀의 HLOOKUP 함수는 셀 범위에서 원하는 값을 찾을 때 사용하는 함수입니다. HLOOKUP이라는 함수 이름은 Horizontal Lookup을 줄여서 쓴 것으로 다음과… …
MATCH 함수 - 범위에서 값의 위치 찾기 - 엑셀의 MATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환합니다. 단순히 위치만 찾아 주므로 위치만 가지고는 쓸 일이 많이 없습니다.… …
INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 - 엑셀의 INDEX 함수는 테이블이나 범위에서 행과 열에 해당하는 값을 찾아줍니다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있습니다. INDEX 함수는… …
INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 다음 그림과 같이 상품정보에서 단가를 연도별로 관리한다고 할때 단가를 찾으려면 상품코드에 연도 조건을 추가해서 찾아야 합니다. 이 경우에 VLOOKUP 함수나… …
자세하고 쉽게 설명해주시네요. 감사합니다
정확한 정보가 적혀있어서 직독직해로 이해할 수 있었습니다!
갠적으로 절대참조, 상대참조가 이해가 가지 않았는데 독학으로 학습하면서 LOOKUP을 익혔네요:) 잘 배워갑니다!!!
상세한 설명 감사합니다. 그런데 수식을 복사해서 붙여넣었을 때 모든 값이 동일한데, 그럼 맨 앞의 값은 일일이 바꿔주어야 하나요?
예를 들어, D29는
=VLOOKUP(C29,B5:D14,2,FALSE)인데, 이것을 아래 행에 붙여넣으면 똑같이 무선마우스가 도출됩니다.
그렇다면 C29를 C30으로 일일이 바꿔주어야 하나요?
그대로 복붙하면 전부 같은 함수가 되고, 긁으면 모든 숫자가 올라가서 범위를 벗어나버립니다.
수식입력줄에 있는 수식을 복사해서 붙여넣어서 그런 것 같습니다.
첨부된 실습파일에서 D29셀을 선택한 상태에서 그냥 복사(Ctrl+C)해서 아래 범위에 붙여넣으면 정상적으로 작동합니다.
긁었을 때 범위가 바뀌는 문제는 절대참조를 상대참조로 바꿔서 그렇습니다. 실습파일에 있는대로 절대참조로 해서 붙여넣어야 합니다.
참조에 대해서는 다음 링크 참고하세요.
https://xlworks.net/excel-formula-reference/
함수 설명 내용이 이해하기 쉽게 되어 있네요. 감사합니다.
좋은 자료 감사합니다. 너무 자세히 쉽게 설명이 되어 있네요~~
고마워요. 명확한 설명이 많은 도움이 되었습니다. 🙂
감사합니다!!
쉬운 설명 감사해요!!ㅠㅠ