엑셀 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 함수를 떠올려 보길 바랍니다.