엑셀] 표에서 원하는 값 찾기(1) - VLOOKUP

업무용으로 엑셀을 사용하는 경우

소스가 되는 데이터들로부터 필요한 데이터들을 추출해서 사용하게 된다. 

소스 데이터는 전체 직원들의 사번을 포함한 명단일 수도 있고, 직급별, 호봉별 급여표같은 조견표일 수도 있다. 

 

이처럼 어떤 기준이 되는 표에서 필요한 데이터를 찾을 때 제일 많이 사용하는 함수 중 하나가 VLOOKUP 함수이다. 

 

보통 기준이 되는 표는 사번이나 제품번호처럼 다른 데이터들과 확실히 구분되는 Key값을 갖는다. 

경험상 데이터 검색시 이런 key 값을 활용하는 것이 정확한 데이터 추출에 도움이 된다

 

아래와 같은 기준 데이터가 있다고 가정하자. 

 

 

 

여기서 사번정보를 이용해서 이름, 집주소 등의 정보를 추출하려고 하는 경우를 살펴보자.

 

VLOOKUP함수는 인수를 네가지를 사용한다. 

도움말에는 이렇게 나오는데(lookup_value, table_array, col_index_num, range_lookup) 

첫번째는 찾는 값, 즉 새로 추출하려고 할 때 기준이 되는 값으로 여기서는 사번에 해당한다. 

두번째는 찾으려는 값이 있는 표.

세번째는 찾으려는 값이 표에서 몇 번째 열에 있는지, 

마지막은 정확도에 해당하는 인수이다. 

 

예를 보면 

 

 

 

첫번째 인수는 찾는 기준이 되는 값인 사번(H2)이고

두번째 인수는 A2:F6의 표

세번째 인수는 표에서 이름이 두번째 열에 있으므로 2

세번째 인수는 정확도를 위해서 FALSE를 표기했다. 

 

 

조금 더 자세히 설명해 보자. 

 

이런 VLOOKUP 처럼 인수를 직접 입력하는게 간단하지 않은 경우는 함수마법사를 사용하는게 편한데

처음부터 함수마법사를 이용하면 함수 이름부터 검색해야하기 때문에 조금 단계가 많다. 

 

 

 

 

이럴 때는 셀에 =vlookup(     

여기까지만 입력하고 단축키 Ctrl + A를 누르면 vlookup과 관련된 함수마법사로 바로 들어갈 수 있다

 

두번째 인수인 Table_array를 선택할 때도 직접 입력하기보다 입력란 옆 아이콘을 이용해서 표를 선택하고

단축키 F4를 누르면 절대참조 방식으로 입력된다. 

보통 엑셀작업에서 자동채움을 사용할 때는 기준이 되는 표는 절대참조 방식으로 입력하는데

이 때 사용하는 단축키가 F4 키다. (F4를 여러번 눌러보면 셀주소가 토글 되는 것을 알 수 있다)

 

 

 

 

 

세번째 인수는 두번째 인수에서 범위로 지정한 표에서 찾는 값이 몇번째 열인지 알려주는 값이다. 

이 예에서는 이름을 찾기 때문에 "2"를 입력했다. 

(만약 2 대신 "5"를 입력했다면 주소값을 반환한다)

 

마지막 인수는 정확도를 위해서 TRUE 또는 FALSE를 입력하는데, 

만약 입력하지 않으면 TRUE가 디폴트 값이다. 

 

매뉴얼이나 엑셀책의 설명에 따르면

TRUE는 표의 왼쪽 첫번째 열이 정렬되었다고 가정하고 사용하고

찾을 값보다 큰 값을 만나거나 찾을 값이 없을 때는 작은 값중에서 가장 큰 값을 반환한단다.  ( ㅡ,.ㅡ)

 

이게 무슨 말인지 아직도 이해를 못하려니와, 

이 방법으로 원하는 데이터를 찾아본 경험도 없다. 

그러니까 정확한 데이터를 찾고자 한다면 무조건 FALSE를 쓴다는 생각으로 VLOOKUP 함수를 사용중이다. ^^;;

 

 

---

 

 

VLOOKUP 함수의 기본 사용법은 이렇게 넘어가고, 

 

자주 헷갈리거나, 오류가 발생하는 경우에 대해서 알아보자. 

 

먼저 VLOOKUP 함수는 찾는 값보다 왼쪽에 있는 값은 찾을 수 없다. 

 

만약 기준이 되는 소스 데이터의 순서가 이름, 사번, 직급..... 이런 순서로 되어 있다면

사번 정보로 사번보다 왼쪽에 있는 이름은 검색할 수 없다는 뜻이다. 

(이럴 때는 소스 데이터의 표를 사번, 이름 순서로 바꿔 놓아야 한다)

 

 

그 다음

추출하고자 하는 데이터의 값(여기서는 사번)이

소스 데이터 표의 첫번째 열과 같아야 한다. 

 

즉, 추출하고자 하는 데이터가 사번을 기준으로 한다면, 소스데이터 표도 첫번째 열을 사번으로 해야 한다. 

그렇지 않다면 다음 그림처럼 #N/A 에러를 보여준다. 

 

 

 

- 엑셀] 표에서 왼쪽에 있는 값 찾기(lookup, index, match 함수)

엑셀] 표에서 왼쪽에 있는 값 찾기(lookup, index, match 함수)