엑셀을 사용하다보면 유용하게 사용할 수 있는 기능이 vlookup 함수입니다. vlookup 함수는 어떤 데이터의 모음에 해당하는 열이 있을 때 해당 열의 정보와 Matching이 되는 정보를 다른 데이터모음에서 가져오는 기능입니다. 예를 들어, 1학년 4반 영희, 철수, 길동이의 이름만 알고 있는 상태에서 해당 1학년 4반 세 명의 아이들의 시험성적이 별도의 테이블로 작성이 되어 있다면, '별도의 테이블'에서 영희, 철수, 길동이의 성적을 매칭하여 불러오는 것이 바로 vlookup 함수입니다.
구버전의 엑셀에서는 vlookup 함수를 사용하기 위해 엑셀의 셀에 해당 함수를 직접 써야만 했었는데, 현재 우리가 사용하는 대부분이 엑셀에서는 vlookup함수 마법사를 이용하기 때문에 어렵지 않게 vlookup 함수를 적용할 수 있어요.
이런 vlookup 함수를 사용하다보면 의외로 오류발생으로 인해 함수가 제대로 적용되지 않을 때가 있는데요. 가장 쉽게 발생하는 오류 중의 하나가 함수에 '절대값'을 적용하지 않아 발생하는 것이고, 다음으로 '셀서식'을 제대로 설정하지 않아 발생하는 경우도 있습니다.
그럼 사무업무에 가장 유용하게 사용되는 엑셀함수인 vlookup 함수를 적용하는 방법과 오류발생시 조치방법에 대하여 간단하게 알아보도록 하겠습니다. 그럼 출발할까요?
vlookup 함수의 정의와 구성
vlookup 함수는 특정 데이터모음 X열이 있을 때, 해당 X열과 연결되는 특정 정보를 다른 데이터모음에서 매칭시켜 가져오는 함수입니다. vlookup함수는 다음과 같이 구성됩니다.
f(x) = vlookup(A,B,C,D)
여기서 A는 표의 첫 열에서 찾으려는 값이고, B는 데이터를 추출하려는 표, C는 값을 추출할 열, D는 추출할 값의 정확한 값(False) 또는 유사한 값(True)을 의미합니다.
vlookup 함수를 사용(적용)하는 방법
▼ 먼저 테이블이 작성이 되어 있어야겠죠? '문구류 판매 현황'과 '단가표' 테이블이 있습니다.
▼ 우리가 해야할 것은 문구류 종류에 따른 단가를 매칭시키는 것입니다. 그런데 문구류 종류만 나와있고 이름이 단가가 없는 상황이군요.
▼ 다행히도 단가표가 따로 마련되어 있네요. 문구류 종류가 모두 기재되어 있는 단가표입니다.
▲ 정보를 가져올 테이블(여기서는 위의 '단가표' 테이블이 되겠죠)은 매칭이 필요한 테이블 (여기서는 위의 '문구류 판매현황' 테이블입니다.)과 같은 Sheet에 있어도 되고, 다른 Sheet에 있어도 되며 심지어는 별도의 excel 파일에 있어도 됩니다. (단, 별도의 excel파일은 데이터 매칭이 필요한 엑셀과 같은 폴더 내에 위치시키는 것이 좋습니다.) 특정 Sheet만 출력을 해야하는 경우가 많으므로 데이터 Sheet와 별도로 Sheet를 만들어서 데이터를 가져오는 것을 추천합니다.
▼ 데이터 매칭을 위하여 매칭이 필요한 첫 번째 셀을 클릭합니다.
▼ 엑셀 편집화면의 함수마법사를 클릭해주세요. 'fx'라고 표시된 부분입니다.
▼ 함수마법사에서 'vlookup' 함수를 선택합니다.
▼ 만약 함수마법사에 vlookup 함수가 없다면 검색창에서 vlookup을 검색한 후 함수를 선택하시면 됩니다.
▼ 함수 마법사가 열였습니다. 첫 번째 인수를 입력하기 위해 함수박스를 클릭합니다. 참고로 함수마법사 하단 부분에는 해당 순서가 의미하는 바가 설명되어 있습니다.
함수인수는 총 네 가지 입니다. 위에서부터 차례로 인수를 A, B, C, D라고 했을 때, A는 vlookup_value이며 표의 첫 열에서 찾으려는 값을 의미합니다. B는 Table_array로 데이터를 추출하려는 표를 의미하고, C는 Col_index_num이며 값을 추출할 열을 의미합니다. D는 Range_lookup으로 추출할 값의 정확한 값(False) 또는 유사한 값(True)을 의미합니다.
▼ 표의 첫 열에서 제가 찾고자 하는 값은 '구분'열의 '테이프'입니다. '테이프' 셀을 클릭 후 '엔터'키를 쳐주세요.
▲ 표의 첫 열이라고 해서 순번열 데이터 '1'이 아닌 찾고자 하는 데이터의 열의 첫 번째 데이터를 선택해야 됨을 주의하세요. (즉, '테이프'를 선택해야 함)
▼ 두 번째로 데이터를 추출하려는 표를 선택하기 위해 함수박스를 클릭해주세요.
▼ 테이블의 필요한 데이터 부분만 마우스로 드래그하여 테이블 범위를 지정해줍니다. 별도 창으로 테이블 함수마법사에 테이블 범위가 표시가 됩니다. 현재는 테이블 범위가 K5:L14로 되어 있습니다.
▲ 여기서 엑셀 전체가 아닌 선택한 테이블을 기준으로 1열과 2열이 되는 것을 기억해주세요.
▼ 함수 마법사로 돌아온 상태에서 바로 'F4' 키를 눌러서 선택한 테이블 범위를 절대값으로 변경시켜줍니다.
▲ 테이블 범위가 절대값으로 적용이 되야 정상적으로 데이터 매칭이 가능합니다. 주의할 점은 마우스로 Tabel_array의 범위를 마우스로 클릭하면 안된다는 것입니다. 마우스로 범위를 클릭하면 클릭한 부분만 절대값으로 변경이 되어 정상적인 데이터 추출이 안됩니다. 함수 마법사로 돌아온 후 바로 'F4키를 눌러주세요.
▼ 정상적으로 반영이 되면 선택했던 테이블 모든 행과 열에 절대값이 적용됩니다. (K열, 5행, L열, 14행 모든 범위에 절대값 표시인 $가 표시되면 정상입니다.)
▼ 세 번째로 값을 추출할 열을 직접 입력해줍니다. 우리가 매칭시키고자 하는 정보는 문구종류에 따른 단가입니다. 선택한 테이블 영역기준으로 2번째 열이므로 숫자 '2'를 넣어주면 되겠네요.
▼ 네 번째로 추출할 값의 정확도를 입력합니다. 숫자 '0' 또는 'false'값을 입력해주세요. 함수인수가 모두 입력이 되었다면 아래의 '확인' 버튼을 눌러주세요.
▲ Range_lookup은 추출할 데이터의 정확도를 의미합니다. '0' 또는 'false'는 정확하게 매칭되는 값, '1' 또는 'true'는 유사하게 매칭되는 값을 읽어옵니다. 데이터 전산관리를 해야하는 경우라면 반드시 '0' 또는 'false'를 입력해주세요.
▼ 첫 번째 데이터가 매칭이 되었네요.
▼ 아래에도 동일한 함수 적용을 위하여 해당 셀의 우측하단 모서리를 잡고 (+표시됨) 아래로 드래그 해주세요.
▼ 그럼, 짜잔~~ 모든 데이터가 정상적을 매칭이 되었습니다.
vlook 함수 증상별 오류 조치방법들
vlook 함수에는 입력하는 인자가 많아서 오류가 발생하기 쉬운데요. 가장 흔히 발생하는 몇가지 오류사례와 그에 따른 조치방법에 대해 알아보도록 하겠습니다.
▼ vlookup 함수의 두 번째 인수인 Table_arrray에서 절대값을 잘못적용하여 발생하는 경우 발생하는 문제 및 조치방법
▼ 해당 필드에 마우스커서를 두지 말고 'F4' 키를 눌러야 한다고 했었죠. 아래에는 K열, 5행, L열, 14행 모든 범위에 절대값 표시인 $가 표시되어 정상적으로 절대값이 반영이 된 경우가 표시되어 있습니다.
▼ 만약 Table_array 필드에 마우스커서를 둔 상태에서 'F4' 키를 누른다면 함수가 어떻게 표시될까요? 아래에는 K열, 5행에는 상대값이 반영이 되었고, L열, 14행에만 정상적으로 절대값이 반영되었습니다.
▼ 이렇게 되면 어떤 결과가 발생할까요? 일단 아래의 좌측 '문구류 판매현황' 자료에 데이터가 정상적으로 반영이 안되어 있네요. (#N/A로 표시되됨) 문구류 판매현황의 '단가' 부분 행을 하나씩 클릭해보겠습니다. 첫 번째 행을 더블클릭했을 때에는 우측 단가표의 범위가 모두 지정되어 있네요. 통과.
▼ 그런데, 문구류 판매현황의 '단가' 부분 두 번째 행을 더블클릭했더니 우측 단가표의 범위의 행이 한 칸 내려가버렸네요. '연필' 데이터가 선택영역을 벗어나서 매칭이 안되는 상황이네요.
▼ 마찬가지로 문구류 판매현황의 '단가' 부분 세 번째 행을 더블클릭했더니 한 칸 더 내려왔습니다. '연필'과 '볼펜' 데이터가 매칭이 안됩니다. 이런 식으로 한 칸씩 밀리게되는 현상이 발생하는 것을 알 수 있습니다.
▼ 그럼 정상적으로 절대값이 반영되었다면 어떨까요?
▼ 문구류 판매현황의 '단가' 부분 첫 번째 행을 더블클릭하면 우측 단가표의 데이터 전체선택. 통과.
▼ 문구류 판매현황의 '단가' 부분 두 번째 행을 더블클릭해도 우측 단가표의 데이터가 전체선택되었습니다. 통과.
▼ 문구류 판매현황의 '단가' 부분의 세 번째 행을 더블클릭해도 마찬가지. 통과. 절대값으로 적용하면 범위가 정상적으로 반영됩니다.
2. 함수가 적용되지 않고 함수식이 그대로 써지는 경우의 조치방법
▼ 함수식 적용이 안되고 상단과 같이 함수식만 보여지는 경우죠.
▼ 서식을 '텍스트'로 놓고 입력을 했을 경우에 발생하는 문제입니다. 이런 경우, 함수식만 '텍스트'로 기재가 되죠. 함수가 적용되는 필드는 '텍스트'가 가 아닌 다른 서식을 이용해주세요. (가급적 서식을 '숫자'로 해주면 좋아요.)
3. #REF! 오류 발생시 조치방법
▼ #REF!는 알맞은 위치로 연결이 안되었기 때문에 해당 값을 찾을 수 없는 참조(Reference) 에러메세지 입니다.
▼ 예를 들어 아래에서 선택한 영역은 1열과 2열까지만인데요, Col_index_num 필드에 3열에 해당하는 '3'을 적으면 해당 열에는 값이 존재하지 않기 때문에 오류가 발생합니다. (정상값인 '2'를 넣어줘야 정상적으로 반영이 되겠죠?) #REF! 오류가 발생했을 때에는 참조값이 제대로 설정이 되어있는지 확인해주세요.
4. 특정 데이터만 #N/A 라고 표시되는 경우 조치방법
▼ 해당 데이터를 추출하려는 표에 텍스트가 없거나 잘 못 기재된 경우가 대부분입니다.
▼ 문구류 판매현황의 '지우개'와 '화이트' 부분과 단가표의 '지우갱'과 '화이티'로 표시되어 데이터가 서로 매칭이 되지 않는 경우입니다. 어느 한 부분을 수정해야 정상적으로 반영이 되겠죠?
5. 데이터 전체가 #N/A라고 표시되는 경우의 조치방법
▼ 데이터 전체에 메세지가 발생하면 멘붕이 옵니다. 함수가 분명히 제대로 함수를 넣은 것 같은데... 하고 말이죠. 이런 경우에 취할 수 있는 몇 가지 방법을 알려드리겠습니다.
▼ 우선, 데이터를 추출하려는 표의 선택영역이 잘못된 경우입니다. 우리가 확인해야 매칭해야 하는 데이터는 '구분', 즉, '문구종류'인데 이 데이터를 추출하려면, 반드시 데이터를 추출하려는 표(아래에서 단가표)의 첫 번째 열이 선택이 되어야 합니다. 그런데 아래에서는 '구분'열이 두 번째 열로 되어있군요.
▼ '구분'열을 테이블의 첫 번째 열로 변경시켜줍니다.
▼ 그럼 정상적으로 데이터 매칭이 됩니다.
▲ 데이터 전체가 #N/A로 표시되는 오류가 발생하는 것은 함수를 함수적용이 제대로 안되어 있거나 서식이 제대로 설정이 안되서 그런것일 수도 있습니다. 변환된 셀의 함수를 클릭하여 함수인수를 하나씩 점검해서 위와 같은 절체대로 진행이 되었는지 다시 확인합니다.
▼ 다음으로 일반적인 경우입니다. 이것저것 다 확인해봐도 모르겠다 싶으면 다음과 같이 조치해보세요. 원본표와 데이터 매칭이 필요한 표 전체를 선택합니다. 아래에서는 '문구류 판매현황'과 '단가표' 모두가 선택이 되어야 하겠죠.
▼ 그 다음, 서식을 모두 '숫자'로 적용해줍니다.
▼ 셀 선택 후 함수를 처음부터 다시 입력해주세요.
▼ 데이터가 정상적으로 입력이 되어있고, 함수도 이상없이 입력했다면, '데이터 전체가 #N/A로 표시되는 오류'가 정상적으로 해결됩니다.
마치며...
엑셀에는 참 많은 함수가 있죠. 하지만, vlookup만큼 익혀두면 도움이 되는 함수는 없는 것 같습니다. vlookup 함수를 아는 것만으로도 여러 개의 엑셀파일과 sheet, 데이터가 있을 때 간단하게 매칭을 시킬 수 있거든요. 저도 vlookup 함수를 자주 사용하지만, 한 번씩 오류를 겪곤 합니다. 대개는 제가 위에 설명드린 내용으로 발생한 오류라서 하나씩 하다보면 해결이 됩니다. 그렇지 않은 경우는 데이터 자체에 문제가 있는 경우도 있지만 말이죠. 아무튼 데이터 추출과 매칭업무를 간단하게 할 수 있도록 도와주는 vlookup 함수. 알아두면 필요한 때가 있을테니 꼭 익혀두시기 바랍니다. 그럼 이만.
