[엑셀 2016] VLOOKUP 함수의 활용 및 사용법

▶지정된 범위의 첫번째 열에서 값을 검색하고, 검색된 값의 열에서 지정된 몇 번째 행의 데이터 값을 알려주는 VLOOKUP() 함수에 대해서 알아보겠습니다.

 

◆ 구조

기본적인 구조는 VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) 으로 이루어져 있습니다. ▶Lookup_value(첫열 찾는 값) : 지정된 범위의 처음 열에서 찾으려는 값이다.

▶table_array(지정 범위) : 첫열의 찾는 값과 목표로 하는 값이 포함된 영역이다.

▶col_index_num(행 이동) : 첫열 찾는 값과 일치하는 열에서 몇 번째 행의 값을 나타낼지 선택한다.

▶range_lookup(찾기 옵션) : 선택 요소로 근사값을 찾으려면 1 or TRUE, 정확한 값을 찾으려면 0 or FALSE를 선택한다. 생략 시 근사값으로 설정된다. 아무래도 정확한 값을 사용하는 경우가 많다.

 

◆ 사용

과일의 종류를 찾고 지정된 행의 이동으로 수량을 찾아내는 수식을 작성해 보았습니다.

 

▶Lookup_value(첫열 찾는 값)을 l2로 지정하여 배라는 값을 지정된 범위 B2:F5(빨간 네모 박스)의 첫열에서 찾게 되고, 지정된 범위(빨간 네모 박스)에서 행으로 두번째 칸의 값이 나오게 된다. 찾기 옵션은 정확한 값으로 설정했다.

 

▶빨간 네모 범위의 배에서 두번째 행인 5가 나오게 된다.

 

▶첫열 찾는 값을 l2셀로 지정했기 때문에 과일의 종류를 다르게 입력하면 해당 과일 서울의 수량이 나오게 된다.

▶위에서 사용한 수식중에 col_index_num(행 이동) 부분을 수정하게 되면 각 지역별로 값을 나오게 할 수 있다.  

- ex) 3으로 하게 되면 경기도의 수량이, 4로 하게 되면 충청도의 수량이 나온다.

 

 

◆ 응용

◎match() 함수와 연계

▶vlookup() 함수의 경우 열에서 지정된 값과 같은 값은 찾을 수 있지만, 행에서는 찾을 수 없다.

 

▶위의 예제를 보더라도 배와 같은 값을 찾을 수는 있지만 서울이나 경기도의 수량을 찾기 위해서는 col_index_num(행 이동)을 일일이 수정하는 번거로움이 있다.

 

▶col_index_num(행 이동) 부분을 MATCH(I3,B1:F1,0)로 변경해 주었다. 이렇게 해주면 MATCH()함수가 l3셀의 지역이름을 B1:F1에서 찾아 몇 번째 값인 지 알려주게 된다. 결국 col_index_num(행 이동) 부분의 값을 MATCH() 함수가 자동으로 찾아주게 된다.  

 - 이때 MATCH(I3,B1:F1,0) 함수의 행 범위는 VLOOKUP()함수의 행 범위와 같도록 하는 것이 좋다.

 

▶과일의 종류와 지역이름을 작성하면 해당 항목의 수량이 나오게 된다.

 

◎간단한 검수

▶데이터 검수를 하는 방법은 다양하게 있지만, vlookup()함수를 이용해 간단한 리스트의 누락 여부를 확인하는 방법으로 사용이 가능하다.

 

▶매달 판매량 리스트를 작성한다고 가정했을때, 2월의 과일리스트는 누락이 발생되어 1월의 과일리스트와 2월의 과일리스트가 다른 경우이다. 리스트의 수량이 적다면 눈으로 쉽게 찾을 수 있지만 종류가 수백가지라면 눈으로 찾는 것은 매우 비효율적인 일이다.

 

▶h4셀에 VLOOKUP(B4,$I$4:$I$11,1,0) 함수를 작성 해준다. 범위 지정시 I4:I11 부분을 작성하고 F4를 눌러주면 $표시가 생기면서 드래그를 하더라도 범위가 고정이 된다. 

 

▶그림과 같이 H4셀을 드래그하여 나머지 셀에 수식을 채워 넣는다.

 

▶위와 같은 결과 값이 나오게 된다. 1월의 과일 종류를 2월 과일 리스트에서 찾아 결과값을 나타내게 된다. B4셀의 값을 I4:I11에서 찾고 그 행의 첫 번째 값을 H4셀에 나오게 한것이다. 마찬가지로 B5셀의 값을 I4:I11에서 찾고 그 행의 첫 번째 값을 H5셀에 나오게 한것이다. 나머지 셀도 동일 작업의 반복으로 나오는 값이다. 하지만 수박은 2월의 과일 리스트에 없기 때문에 오류가 나게 된다. 결국 오류가 나게 되는 과일이 누락된 과일이 되는 것이다.

 

▶참고로 HLOOKUP() 함수의 경우 VLOOKUP() 함수의 행과 열을 바꾸기만 하면 똑같은 사용법을 가지고 있으니 이번 글을 이해만 하면 매우 쉽다. VLOOKUP() 함수는 실제 업무에서 많이 사용되고 있으니 잘 활용하는것이 좋다.