[엑셀 브이룩업] 엑셀 함수 제6탄 VLOOKUP 함수

엑셀 함수 VLOOKUP 함수 브이룩업

원하는 값을 Table_array(선택영역)의 첫 열에서 찾아 그 값이 있는 그 행에서 지정한 열의 값을 찾아 그 값을 표시합니다.

◇ 구문

● Lookup_value: table_array의 첫 번째 열에 기록된 내용 중 사용자가 찾고자 하는 검색 조건으로 숫자, 참조 영역, 또는 문자열로 지정할 수 있습니다.

● Table_array: 검색하려는 데이터들이 포함된 셀을 시작으로, 비교하여 가져올 데이터가 있는 열까지의 영역으로 table_array의 첫 열에는 lookup_value와 비교하는 포함이 되어 있어야 합니다.

●Col_index_num : table_array의 첫 번째 열에서 lookup_value가 기록된 행을 찾은 후 동일 행에서 얻고자 하는 데이터가 포함되어 있는 열 번호를 말합니다.
따라서 col_index_num로지 정한 숫자가 1보다 작거나 table_array의 전체 열수보다 크면 각각 #VALUE! 오류, #REF! 오류가 나타납니다.

● Range_lookup : 정확하게 일치하는 값을 찾을 때는 false 입력하고, 근삿값을 정확히 찾으려면 true를 입력하거나 생략을 합니다.
단 근삿값을 정확히 찾으려면 table_array 영역의 첫 열 기준으로 반드시 올림차순(1, 2, 3,..., a, bc,... 가나다.. 순)으로 정렬을 되어야 정확한 근삿값을 얻을 수 있습니다.

◇ 예제

표 1에서 이름으로 나이를 찾아서 표 2에 기입해 보시기 바랍니다.
먼저 표 1의 데이터가 현재 5개만 있지만 이 데이터가 수천 개라 생각하고 편집 메뉴의 데이터를 찾기 위해 표 1의 "이름" 항목의 이름 들의 범위를 지정합니다.

단축키 Ctrl+F 를 눌러 찾기 대화 상자를 열고 표 2의 첫 번째 이름부터 표 1에서 찾아서 이름에 해당하는 나이 항목을 값을 확인하고 표 2에 기입할 것입니다.

"강충덕"의 나이는 표 1의 순번 4의 이름에서 찾아질 것이고 이름에서 오른쪽 옆으로 3번째 칸에서 나이 30을 가져와 표 2에 입력할 것입니다.

 

 다음으로 표 2의 두 번째 이름을 또 같은 방법으로 찾아 나이를 입력할 것입니다.
만약 수천 개의 데이터가 있다면 나이 항목을 다 채우기 위해 많은 시간이 필요하게 될 것입니다.

이번에는 VLOOKUP 함수를 한번 이용해 보겠습니다.

순번 1의 수식을 살펴보면 =VLOOKUP(B95, $B$87:$D$91,3, FALSE)으로 B9는 이름 항목의 "강충덕" 즉 찾기 대화 상자의 찾을 내용을 입력하는 것과 같습니다.

그리고 두 번째 인수인 $B$87:$D$91는 찾을 이름과 나이가 들어 있는 데이터의 범위입니다. 찾기 대화 상자를 이용할 때 이름 부분을 범위로 잡아 그 범위 안의 데이터만 찾았던 것처럼 table_array의 범위도 찾을 값 즉 이름을 포함해야 하며 찾을 값이 항상 제일 앞 열에 있어야 합니다.

표 2의 이릉들이 표 1의 B열에 있으므로 B열부터 꼭 범위를 잡아야지만 나잇값을 가져올 수 있습니다. 만약 A87:D91로 범위를 잡았다면 표 1의 순번 항을 범위로 잡아 놓고 찾기 대화 상자로 이름 "강충덕"을 찾는 거나 마찬가지가 되는 것입니다. A열에는 "강충덕" 이름이 없으므로 그리고 셀 범위를 절대 주소로 지정한 것은 함수를 복사해서 사용할 경우 창 초범 위가 변경되기 때문에 이를 방지하기 위해서입니다.

다음으로 이름부터 3번째 칼럼에 나잇값이 있으므로 세 번째 인수는 "3"이 됩니다. 고향을 찾고 싶으면 "3" 대신 "2"를 입력하면 될 것입니다.

마지막으로 찾는 이름이 똑같을 때 만 나잇값을 가져오기 위해 네 번째 인수는 "FALSE"로 하였습니다. "True"를 입력하거나 range_lookup 인수를 생략하면 근삿값으로 찾아서 값을 가져오므로 원하는 값을 얻지 못할 수가 있으니 주의하시기 바랍니다.

 

참고로 =VLOOKUP(B95,$B$87:$D$91,3,) 이 수식처럼 마지막 인수 자리에 " , "는 입력하고 range_lookup 값만 생략했을 경우에는 표 2의 순번 5번째 이름인 "이성현"은 표 1에 없으므로 "#N/A"로 에러 표시를 합니다.

◇ 응용
표 1과 표 2를 비교하여 표 2를 기준으로 품번과 SMR이 바뀐 품목을 찾아보시기 바랍니다.

 

 

표 2 1 칼럼의 VLOOKUP 함수를 보면 =VLOOKUP(B21, $B$7:$C$16, 2, FALSE)는 B21의 값 "MS21252-2LS"으로
$B$7:$C$16 범위의 B열에서 "MS21252-2LS"를 찾아서 "MS21252-2LS"에서 2번째 칼럼의 값 "PAOZZ"을 가져오라는 말입니다.
"FALSE"는 정확히 일치하는 값만 가져오라는 뜻입니다.

2번 칼럼은 VLOOKUP으로 불러온 표 1의 SMR과 표 2의 SMR을 비교하여 같으면 SMR 동일 다르면 "SMR 다름"으로 표현하였습니다.
수식으로는=IF(C21=D21, "SMR 다름", "SMR 다름")과 같습니다.

VLOOKUP함수는 찾는 값이 찾을 범위 내어 없을 경우 "#N/A"에러 값을 반환합니다.
따라서 3번 항목은 ISERROR 함수를 이용하여 에러 여부를 판별하고 IF함수로 판별된 값이 에너일 때에는 "품번 확인"이라고 표기합니다.

수식은 다음과 같습니다. =IF(ISERROR(E21), "품번 확인", "")

4 항목의 값은 1, 2, 3의 항목들을 한꺼번에 수식으로 표현한 것입니다.
1, 2, 3의 항목이 이해가 되셨으면 4 항목은 쉽게 이해가 될 것입니다.

◇ 응용 2
아래 조건을 만족하도록 표 3의 결괏값을 채우시기 바랍니다.
조건 1 : EC가 1일 경우 결과란체 "V"를 입력합니다.
조건 2 : EC가 3일 경우 결과란체 "N"를 입력합니다.
조건 3 : EC가 5일 경우 결과란체 "S"를 입력합니다.
조건 4 : EC가 7일 경우 결과란체 "P"를 입력합니다.
■표 3

이 경우는 IF문으로 쉽게 답을 구할 수가 있지만 VLOOKUP으로 한번 해보겠습니다.
VLOOKUP의 경우 우선 조건을 만족하는 참조 표를 먼저 만들어야 하는 번거로움이 있지만 대신 IF문은 조건이 7개 이상일 경우 적용하기가 곤란하다는 문제점을 VLOOKUP 함수로 해결할 수 있습니다.

먼저 아래와 같이 위의 조건을 만족하는 참조 표를 만듭니다.

조건을 만족하는 참조 표만 잘 만든다면 조건이 많을 경우 IF함수보다 유용할 수도 있을 것입니다.