[Excel] 알아두면 제일 잘쓰는 VLOOKUP 함수 사용방법, 추가로 알아두면 좋은 IFERROR

문서 작업을 많이 할 일이 없었다.
기껏해야 사용자에게 배포할 화면설명서나 매뉴얼 작성 등이었는데 주기적으로 자료 추출하는 작업이 생겼는데 해당 작업을 하면서 엑셀 실력이 날이 갈 수록 늘었다. 아 물론 쓰는 기능만 쓴다는게 함정.

 

오늘은 알아두면 제일 잘쓰는 기능인 vlookup 함수의 사용방법과 같이 쓰면 편리한 추가 함수들을 설명하고자 한다.

 

[Excel] 알아두면 제일 잘쓰는 vlookup 함수 사용방법

 

VLOOKUP 함수 설명 및 사용법

 

vlookup은 vertical + lookup의 약자로 찾고자 하는 값과 일치하는 열의 N번째 값을 반환하는 함수다.
엑셀에서 함수 찾기 했을 때 아래와 같이 설명하고 있다.

 

 

 

배열의 첫 열에서 값을 검색하여, 지정한 열의 같은 행에서 데이터를 돌려줍니다.
기본적으로 오름차순으로 표가 정렬됩니다. (아마 정렬되어 있어야 한다를 말하는 것 같다.)

 

각 변수값을 설명한 뒤에, 실제 사례를 알아보자.

변수명	설명
lookup_value	검색하고자 하는 값, 이 값이 일치하면 그 행을 기준으로 데이터를 탐색한다.
table_array	값을 탐색하고자 하는 범위가 되는 영역 ★여기서 주의할 점 혹은 알아두면 좋은 점★ - 값을 탐색할 열은 첫 번째 열에 위치해 있어야 한다. - 값을 탐색할 열을 기준으로 '오름차순' (123, 가나다 순) 으로 정렬해준다. - 대부분의 경우 이 범위는 변경되지 않으므로 절대경로로 지정해주는게 좋다.   범위 지정 후 F4를 누르면 절대경로로 지정 가능!
col_index_num	일치하는 값이 있는 경우 같은 행의 몇 번째 값을 VLOOKUP 함수의 반환값으로 할지를 결정 (table_array에 지정한 영역에서의 열의 순서임. 엑셀 시트의 절대적인 순서 아님!)
range_lookup (생략가능, 기본값 TRUE)	값을 찾을 때, 정확하게 일치하는 값만 사용할건지 유사한 값으로 찾을 건지 결정 - FALSE : 정확하게 일치하는 값만 사용 - TRUE : 유사한 값이라도 찾아서 사용

(세상에.. 표에 열심히 작성해둔 내용이 날라갔다니 ^_ㅜ 다시 써야지 뭐)

 

영역 지정하고 F4 누르면 절대경로로 지정 가능

 

 

아무리 봐도 감이 안잡히니까 검색해서 들어오셨을테니까 예시 시작!

 

특정 시스템에 마지막으로 접속한 최종 접속일자를 받아와야 하는 일이 있다고 가정해보자.

아래 캡쳐 이미지와 같이
1) 뽑을 대상 정보를 받아오고 2) 시스템의 최종 접속일자를 DB에서 추출했다.

뽑을 대상 정보의 사원번호를 이용해서 DB에서 추출한 시스템 최종 접속일자를 가져오려고 한다.

물론 저렇게 데이터가 적으면 그냥 복사 붙여넣기 하는게 빠르겠지만, 매우 많은 데이터를 다룬다는 가정하에 함수를 사용할 예정이다.
그리고 손으로 옮기는 것보다 함수 한 번 작성해서 채워넣는게 더 빠를지도 모른다.

 

 

 

최종적으로 1번 테이블의 노란색 음영 부분에 값이 채워지도록 하는 것이 목표다.

 

vlookup을 가시적으로 보여주면 아래와 같다.

1번 테이블의 노란색으로 표시해둔 사원번호 값을 기준으로 2번 테이블을 뒤졌을 때,
일치하는 값이 있으면 해당 행에서 최종 접속일자 값이 포함된 두 번째 열의 데이터를 읽어들이는 것

 

 

 

그리고 위의 설명에 맞추어 아래와 같이 VLOOKUP 함수를 작성해보았다.

=VLOOKUP(검색할 기준 데이터, 찾을 데이터가 포함된 기초데이터 테이블 영역,
원하는 데이터가 위치한 열 번호, 유사데이터 허용여부)

 

 

 

 

결과는 아주 잘 찾아와진걸로 확인된다. 강조를 위해 음영을 추가하고 빨간 글씨색으로 변경하였다.

 

 

 

하나의 함수를 잘 작성했으니 자동채우기로 1번 테이블의 마지막 데이터까지 쭉 찾아와야지!
클릭하고 드래그 혹은 오른쪽 하단 자동채우기를 위한 포인트 더블클릭을 했더니 음? 중간중간 데이터가 다 안들어온 부분이 있다.

 

 

알아보기 쉽게 일치하는 데이터는 색을 동일하게 칠해보았다.

 

 

찾아보니 1202 신세경, 1302 조병규는 접속 이력이 없다.
찾고자 하는 데이터가 없는 경우에는 #N/A로 표시되는데 이게 보기 싫으니까 깔끔하게 보일 수 있도록 함수를 한 번 수정해보자.

 

IFERROR 함수로 오류 해결하기

 

IFERROR 함수의 사용법은 아주 간단하다, 에러가 발생할 수도 있는 값을 첫 번째 인자로 두고 만약 에러가 발생한다면 표시될 내용을 두 번재 인자에다가 입력하는 것이다.

 

=IFERRPR(에러가 발생할 수 있는 데이터, 에러 발생 시 표시할 내용)

 

위의 내용에서 최종 접속일자가 없는 대상자들에게는 '접속이력 없음' 이라는 메시지를 띄우도록 기존 함수를 바꿔보자.
IFERROR로 기존에 작성한 VLOOKUP 함수를 감싸는 형태로 작성할 거고 이렇게 해도 잘 나오던 결과는 그대로 잘 나오며, 문제가 있는 데이터만 원하는 형태로 바뀌어서 나온다.

 

 

이렇게 변경된 수식을 기존에 수식이 적용되었던 셀에 다 적용해보자.

 

 

드디어 완성

 

 

드디어 원하는대로 데이터를 모두 추출했다.

오늘의 포스팅이 여러분의 빠른 퇴근을 도왔기를 바라며!
도움되셨으면 주변에 있는 광고 아무거나 꾹 눌러주세요 ㅎ_ㅎ