VLOOKUP 함수 및 드롭다운 목록상자 만들기

VLOOKUP 함수 및 드롭다운 목록상자 만들기

지난번 필터링에 대한 내용에 이어 데이터 조회에 많이 사용하는 VLOOKUP 함수 입니다. 

특정 데이터를 기준으로 

해당 데이터와 같은 열에 있는 데이터를 조회할 수 있습니다. 

VLOOKUP 의 경우 사용법은 다음과 같습니다. 

​

사용법은 아래의 표로 예시를 들어 보겠습니다.

​

표에서 보면 성명/사번/내선전화/연차개수 의 정보가 담겨 있습니다. 

우리가 찾고 싶은 것은 '마이클의 내선전화 번호' 입니다. 

728x90

​
여기서 마이클은 키워드가 됩니다. 

검색할 영역에는 키워드가 들어간 영역(C열)과 내선전화를 검색할 영역(E열)이 반드시 포함되어야 합니다.


영역을 지정할 때 특성이 있는데

키워드가 있는 열의 우측열만 데이터 검색이 됩니다. 

그러므로 영역지정은 이렇게 됩니다. 

 

C열과 E열만 검색할 건데 왜 D열은 왜 넣고, F열까지 지정하느냐고요?

어차피 다음 항목으로 열을 지정할 것이기 때문입니다.


키워드가 있는 가장 좌측열이 기준이 되는 1열(C열), 그다음 우측으로 2, 3, 4열(D,E,F열)이 됩니다. 

만약 사번으로 내선번호를 검색한다면 사번열(D열)이 1열이 됩니다.

그리고 마지막으로 TRUE/FALSE를 넣어줍니다. 

TRUE는 유사값 

FALSE 는 정확한 값만 찾기 입니다.

우리는 정확한 값을 찾을 예정이므로 FALSE를 사용합니다. 


정리하면

이렇게 됩니다. 

C열 13번의 키워드(마이클)로 C6:F10 범위에서 키워드가 있는 열로부터 3번째 떨어진 열(E열)에서 키워드와 같은 열의 데이터를 정확하게 반환하라

입니다. 

그리하여 결과값은 내선전화 번호 507이 됩니다. 

이를 드롭다운과 응용하면 아래와 같은 검색기능을 만들 수 있습니다. 

​

성명에 드롭다운기능을 넣어 목록의 이름을 선택하면 해당열의 사번이 출력되도록 하였습니다.

VLOOKUP에 드롭다운 항목을 키워드로 사용하여 해당값이 출력되는 형태입니다. 

​
그럼 드롭다운은 어떻게 적용하는지 확인해 볼꼐요

​

우선 D열 2행을 선택합니다.

그리고 상단 데이터 탭에서 데이터 유효성 검사를 클릭합니다.

​

제한대상을 목록으로 설정하고

원본은 목록을 가져올 범위를 선택하여 줍니다. 

우리는 이름을 키워드로 사용할 것이기 때문에 

성명이 들어간 C열 6행 부터 10행까지 선택하였습니다.

​

선택후 확인을 누르면 

예시와 같이 역삼각형 그림이 있는 사각형이 해당 셀옆에 나타납니다.

해당 버튼을 클릭하면 

우리가 선택한 영역의 데이터가 목록화되어 나타나는 것을 알 수 있습니다.

여기에 VLOOKUP을 활용하면 목록에서 성명을 골라 우리가 원하는 데이터를 취할 수 있습니다.

예시는 성명을 고르면 사번을 조회가능하도록 하였습니다.

함수를 볼까요?

​

키워드의 부분이 D행 2열로 지정하여, 목록이 변경될 때마다 해당 성명의 사번이 출력되는 것을 볼수 있습니다.

FALSE 부분은 숫자 0으로 입력하여도 되고 FALSE로 입력하여도 됩니다.(TRUE = 1, FALSE = 0)

자 그럼 VLOOKUP 과 드롭박스를 활용한 검색 부분은 완료되었으나 
VLOOKUP의 아쉬운 기능이 키워드열의 우측열만 데이터가 검색 가능하다는 것입니다. 

키워드 열로 부터 좌측에 있는 데이터는 VLOOKUP 함수로 가져올수 없기 때문입니다.


만약 예시에서 내선전화번호를 고르면 
해당 번호의 성명이 출력하도록 하고 싶다면


VLOOKUP 사용시 열을 복사 하거나 잘라내어 우측으로 옮겨주어야 합니다.

하지만 VLOOKUP이 아닌 다른함수를 사용하면 가능합니다.
INDEX 와 MATCH 함수를 함께 사용해야하는데 이것은 다음편에 다루도록 하겠습니다.