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

엑셀에서 데이터를 찾을 때 vlookup 함수를 많이 사용한다. 

(직장인이 엑셀에서 가장 많이 사용하는 기능 순위에서 sum 다음으로 많이 사용하는게 vlookup이라던가... )


vlookup을 사용하기 위한 조건이 있다. 

첫번째는 vlookup함수는 찾는 표에서 기준이 되는 값의 오른쪽 데이터만 검색 가능하다. 

그러니까 표에서 검색 기준이 되는 키 값의 오른쪽에 있는 데이터만 검색 가능하다는 뜻이다. 

두번째는 검색범위를 설정할 때 검색범위의 제일 왼쪽에 검색하려는 데이터가 오도록 범위를 설정해야 한다.

 

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

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

그러면 오른쪽에 있는 데이터로 왼쪽 값을 찾을 때는 어떻게 해야 할까???

lookup 함수를 사용해보자. 

 

lookup함수

lookup 함수는 검색범위에서 찾는 값의 위치를 찾아서 출력범위의 동일 위치의 값을 반환한다. 
기본 사용방법은 아래와 같다.  

lookup(찾는 값, 검색범위, 출력범위)

아래 그림 같은 경우 기초 자치단체 이름으로 왼쪽에 있는 지역을 찾아보자. 

=LOOKUP(D2, $B$2:$B$5, $A$2:$A$5)

수식은 D값을 B열의 검색범위에서 찾고, A열의 동일 위치 값을 반환한다. 



간단한 사용법에 비해서 가장 큰 단점(?)은 검색범위가 오름차순으로 정렬되어 있어야 한다는 점이다. 
만약 검색범위가 정렬되어 있지 않으면 제대로 검색하지 못한다. 

 

검색범위가 정렬되지 않은 데이터라면 index, match함수를 사용해야 한다. 

 

 

 index,match함수

index함수는 선택한 범위(배열)에서 세로 몇 번째, 가로 몇 번째에 있는 값을 반환한다. 
일반적으로 말로하는 가로 세로와 순서가 반대다. ^^;;

첫번째 인수로 범위, 두번째 인수로 행 위치, 세번째 인수로 열 위치를 지정한다.

=INDEX($B$2:$D$4, 1, 2)

이 수식은 첫번째 인수 범위 중에서 세로 1번, 가로 2번에 있는 값을 반환한다.

match함수는 찾는 값이 범위에서 몇번째 위치에 있는지를 반환한다. 
(범위는 행 방향이거나 열 방향이거나 상관없다)

=MATCH(D2,$B$2:$B$5,0)

이 수식은 첫번째 인수인 찾는 값을 두번째 인수 범위에서 몇번째에 있는지 반환한다. 
세번째 인수인 0은 정확한 값을 반환하는 옵션이다(-1은 검색값보다 작은 값, 1은 큰 값을 찾는다)

 

이제 기초자치단체의 이름을 가지고 왼쪽에 있는 지역을 검색해보자. 

수식 =INDEX($A$2:$B$5, MATCH(D2,$B$2:$B$5,0), 1) 은

index함수 범위 중에서 match함수의 반환값을 행 위치로, 1을 열 위치로 지역을 찾는다.