엑셀 vlookup 함수 이해와 활용하기

엑셀 vlookup 함수는 활용도가 높은 함수 중에 하나입니다.

vlookup 함수의 사용 예로는 물품에 정해진 단가를 얻기 위해 단가표를 참조하는데 사용할 수 있고 성적같은 점수나 수치의 등급표를 참조하는데 데도 사용할 수 있습니다.

이렇게 엑셀 vlookup 함수가 단가표, 등급표와 같이 정해진 표를 참조하는 부분이 있다는 사실을 아는 것으로 반은 배운겁니다.

그러면 예를 통해서 vlookup 함수의 특징을 살펴보겠습니다.

엑셀 vlookup 함수

문제 상황 1) 단가표 참조하기

두 개의 표가 있습니다. 오른편에는 물품에 대한 단가표가 있고 왼편의 표는 아직 완성되지 않았지만 구입 물품에 대한 표입니다.

구입한 물품(벽걸이 시계, 시계-B, 책장-A, 침대-B)과 수량은 채워졌습니다. 이젠 구입 물품에 대한 단가를 채워넣야할 텐데 오른쪽 단가표를 보고 수동으로 채워넣어야 할까요? 물론 가능합니다만 물품의 종류가 100개, 1000개,...라면 난감하지 않을까요?

단가표에 물품의 개수가 많더라도 앞서 언급한 것처럼 vlookup 함수를 사용하여 단가표를 참조하면 쉽게 단가를 알아낼 수 있습니다. 문제 해결을 위해 vlookup 함수의 형식을 알아보겠습니다.

vlookup(품명, 단가단가가 있는 열, 옵션)

이해하기 쉽도록 예제에 맞게 인수를 구체적으로 적었습니다.

설명 : 

단가표의 첫 번째 열에서 찾으려는 품명 걸이시계와 일치하는 값이 있는 행을 찾습니다.

② ①에서 찾은 행(11), 단가가 있는 열(H)에 일치하는 셀을 단가표에서 찾아서 반환합니다. 이 예에서는 셀주소 H11입니다.

예제의 D2 셀에 들어갈 수식은 다음과 같습니다. 

=VLOOKUP(B2, $G$3:$H$11, 2, FALSE)

단가표에 해당하는 주소가 절대 참조로 되어있습니다. 아래 그림처럼 자동채우기를 해야하기 때문입니다. 절대참조와 자동채우기의 관계에 대한 것은 다음 글을 참고하시길 바랍니다.

엑셀 절대참조와 상대참조 차이점


※ 옵션이 FALSE면 ①에서 정확히 일치하는 값을 찾습니다. TRUE거나 생략된 경우는 다음 예제에서 설명합니다.



문제 상황 2) 범위에 따라서 값의 등급 매기기

성적을 다룰 때 90점 이상은 수, 80점 이상은 우, ... 이렇게 점수에 따른 등급을 얻는 방법에 대해서 알아보겠습니다. 앞에서 다룬 예제처럼 단가표에서 정확히 일치하는 값을 찾는 것은 아니지만 이 경우도 범위와 등급에 대한 표가 필요합니다.

그리고 다음과 같은 조건이 지켜져야 합니다.

vlookup 함수로 참조할 표에서 범위에 따른 등급을 얻기위해 필요한 조건 

① 참조할 표의 첫 번째 열이 오름차순으로 정렬되어 있어야 합니다.(중요)

② vlookup 함수의 4번째 인수는 TRUE 또는 생략합니다.

vlookup 함수의 네 번재 옵션이 TRUE 또는 생략되면 정확히 일치하지 않은 경우 이 값보다 작은 값 중에 가장 큰 값을 찾습니다.

예를들어 표의 vlookup 함수가 참조하는 표의 첫 번째 열의 값이 10, 20, 30, 40 이렇게 있을 때 찾을 값이 33이면 33과 일치하는 값이 없으므로 33보다 작은 값 중에 가장 큰 값인 30을 찾습니다.( ※ 다시 강조 : 이 때 vlookup 함수의 4번째 인수는 생략되어 있던가 TRUE여야 합니다.) 다음 예제를 보세요.

옵션이 TRUE 인경우(등급표에 정확히 일치하는 값이 없다면 33보다 작은 값 중 가장 큰 값인 30에 해당하는 등급 "" 반환)

옵션이 FALSE인 경우(등급표에 정확히 일치하는 값이 없으므로 #N/A 에러 발생)

다음 표는 성적에 등급표시를 하기위해 vlookup 함수를 사용한 예입니다.

함수의 옵션값을 TRUE로 한 후 표의 범위를 사용하기 위해 오름차순 정렬이 되어 있는 등급표를 참조합니다. 나머지는 앞서 예제와 동일합니다.

자동채우기를 해서 나머지 칸도 완성해 보겠습니다.

엑셀에서 이런 문제는 IF 함수를 사용한 다중 조건으로 해결할 수도 있습니다. 90점이상이면 "수", 80이상이면 "우", 70이상이면 "미", .... 이렇게 말이죠. (자세한 수식은 생략합니다.) 

하지만 조건이 많아질수록 수식이 길어져서 복잡해집니다. 따라서 다중조건으로 해결해야하는 문제가 있다면 엑셀 VLOOKUP 함수를 떠올려 보길 바랍니다.

이 댓글을 비밀 댓글로
  1. 이전 댓글 더보기
    • 김수현
    • 2017.07.21 16:19
    정리 잘되어 있어요.
    당신의 능력에 감탄합니다ㅠ
    • 멋진넘
    • 2017.07.25 10:59
    완전 쉽게 설명해주셔서 넘 넘 감사합니다. 정말 잘 쓰겠습니다!!!!! 꾸벅꾸벅
    • 뚜교이
    • 2017.07.27 16:26
    이해 정말 잘됩니다!! 감사합니다
    • 훌륭한설명입니다
    • 2017.08.28 15:53
    감사합니다 최고입니다
    • 준하린
    • 2017.10.12 19:48
    감사해요, 설명이 쉬워 잘 배우고 갑니다.
    자주올듯해요...
    • bae
    • 2017.10.16 09:42
    안녕하세요 취직한지 얼마 안된 신입사원입니다. 엑셀 만지면서 어려운게 많았는데 블로그에서 잘 배워갑니다. 감사합니다. ^^
    • 직장인
    • 2017.10.17 16:47
    가물가물했는데 보자마자 이해되게 잘 정리해주셨네요 최고입니다. ^^
    • 엑셀함수파헤치기
    • 2017.10.26 10:51
    다른 건 이해가 안 되었는데 아주 유용했습니다. 감사합니다.
    • ㄴㄴ
    • 2017.11.02 11:22
    진짜 감사해요!!
    • Wayne
    • 2017.11.10 13:16
    Gg
    • Wayne
    • 2017.11.10 13:17
    정말감사합니다.
    다른 블로그 봐도 이해 않되서 계속 찾았는데..
    한방에 쉽게 이해했습ㄴ다.
    • 쪼라
    • 2017.12.04 12:02
    친절하게 알려주셔서 도움이 많이 됐어요! 그리고 질문 하나 남겨도 될까용? 예로 단가표에 없는 품목일 땐 그 단가 가격에 아무것도 안들어가게끔(?) 빈 셀이 되도록 하려면 수식을 어떻게 써야할까요ㅠㅠ..? 사이즈에 따라 달라지는 품목이 있어서 단가표에 따로 만들지 않았는데 전체 셀 선택해서 수식입력하니 #N/A로 떠서 하나하나 다 없애야하더라구요..그냥 아예 #N/A가 안뜨게할 수 있는 방법이 있나용??
    • 안녕하세요. 잘 봐주셔서 감사합니다.
      다음과 같이 해주시면 됩니다.
      = VLOOKUP(B3, $H$3:$I$11,2,FALSE)
      위와 같은 수식을 쓰고 계시다면 IFERROR 함수를 사용해서
      =IFERROR(VLOOKUP(B3, $H$3:$I$11,2,FALSE), "";)
      이렇게 해주시면 됩니다.
      IFERROR 함수는 두 개의 인자를 받는데 첫 번째에 원래 사용할 식을 넣고 두 번째 인자로는 에러가 발생할 경우 표시할 내용을 넣으면 됩니다. ""로 빈 문자를 넣었으므로 에러가 날 경우 아무것도 표시 안되게 됩니다.
    • adf
    • 2017.12.05 18:46
    친절한 설명감사합니다~ 많이 배웠습니다!
    • 2real
    • 2017.12.14 15:32
    정말 쉽게 이해했습니다.
    Vlookup 쓸려고 고생고생 했었는데..
    정말 감사합니다. ^^
    • 1
    • 2017.12.21 14:42
    와..대박 감사합니다
    vlookup에서 오름차순을 몰라서 한참을 헤맸는데 감사합니다!!
    • 기러기
    • 2018.01.04 13:46
    이해했어요 ㅠㅠㅠㅠㅠ감사합니다
    • 조영호
    • 2018.03.13 17:58
    Vlookup해서 값을 못 찾은 경우 0 으로 표시할수 있을까요? 찾아온 값을 sum 해야하는데 #n/a가 있으니 sum이 안되네요
    • 선물모아
    • 2018.03.16 23:45
    당신은 진정 지식인 감사합니다
    • 지나가는사람
    • 2018.07.22 09:44
    궁금해서 찾아봤는데 도움이 많이 되었습니다 감사합니다
    • 제곤
    • 2018.08.08 10:06
    쩐다 올려주신분 정말 최고입니다 천재천재~