안녕하세요 :D
엑셀을 사용하다 보면 자주 쓰는 함수들이 몇 가지가 있는데요.그중 실무에서 자주 사용하고 있는 vlookup 함수와 index, match 함수를 활용하여 시트 내 원하는 값을 찾아오는 방법에 대해 알아보겠습니다.
■ 구글시트 관련글
#2 구글 스프레드시트 조건부 서식 중복값 찾기 및 삭제
1. vlookup 함수 활용법
엑셀에서 lookup 함수는 vlookup, hlookup, lookup 이렇게 3가지가 있지만 그중 많이 사용하는 함수는 vlookup 함수가 아닐까 합니다. vlookup 함수에 대해 간단하게 알아보자면 '배열의 첫번째 열(세로)에서 값을 검색하여, 지정한 열(세로)의 같은 행에서 데이터 값을 찾는 함수'라고 생각해주면 될 것 같습니다. 수식은 아래와 같습니다.
=VLOOKUP([찾고자 하는 데이터 값], [찾고자 하는 범위], [찾으려는 값이 들어있는 열 번호], [정확한 일치 여부])
위와 같이 말로 설명하는 것보다 직접 실습을 해보시면 빠르게 이해하실 수 있을 거예요.
아래 예제 파일에서 [품목] 데이터 값을 기준으로 [공급가]와 [판매가]를 vlookup 함수를 활용하여 구하고자 합니다.
기준으로 한 데이터 값 [품목]은 C열에 들어있습니다. 그리고 검색 대상 범위는 [표1] H3:J7 열에 있습니다. 검색 대상 범위를 지정할 때는 기준이 되는 데이터 값이 무조건 왼쪽 끝으로 와야 합니다. 예를 들어 공급가/품목/판매가 순으로 표가 작성되어 있다면 품목의 열을 맨 왼쪽으로 옮겨서 작업해주어야 값을 찾을 수가 있습니다.
그리고 찾으려고 하는 값인 [공급가]의 열 번호는 기준으로 잡은 품목부터 순서대로 1,2 두 번째에 있습니다. 해당 내용을 수식으로 표현하자면 아래와 같이 표현할 수 있습니다.
=VLOOKUP(C3,H3:J7,2)
이후 정확하게 일치하는 데이터 값을 찾을 것인지 유사 일치하는 데이터를 찾을 것인지 아래 수식으로 적용해 주시면 됩니다. 대부분 정확히 일치하는 값을 사용합니다.
| TRUE ( 1 ) | 유사 일치 |
| FALES ( 0 ) | 정확히 일치 |
=VLOOKUP(C3,H3:J7,2,0)
여기서 끝이 아닙니다! 많은 양의 데이터를 작성할 때, 엑셀 기능 중 좋은 것이 동일한 수식을 채울 수 있다는 점입니다. 이 때 필요한 것이 바로 절대 참조 기능입니다. F4키를 이용해 범위 부분만 절대 참조로 바꿔 줍니다. 아래와 같이 설정하면 범위 부분은 드래그를 하여도 주소가 변하지 않아 검색 범위가 변경되지 않습니다.
이와 같이 판매가도 구할 수 있으니 나중에 한번 해보시기 바랍니다.
=VLOOKUP(C3,$H$3:$J$7,2,0)
2. INDEX, MATCH 함수 활용법
위에서 설명한 vlookup은 찾고자 하는 값이 무조건 검색 범위의 가장 왼쪽 열에 위치해야 한다는 점이 있습니다. 모든 데이터가 기준이 되는 값이 왼쪽에 있으면 좋겠지만 그렇지 않은 경우도 많겠죠. 그럼 이 경우에는 어떻게 해야 할까요?
INDEX 함수와 MATCH함수를 이용하면 간단하게 해결이 가능합니다.
INDEX 함수는 '선택한 범위에서 해당 위치에 있는 값을 찾아주는 함수'입니다.
=INDEX([참조할 값 범위], [행 번호], [열 번호])
MATCH 함수는 '선택한 범위 내에서 원하는 데이터의 행과 열 위치를 구해주는 함수'입니다.
일치 여부는 1,0,-1로 쓸 수 있으며 해당 내용은 아래와 같습니다
| 1 | 보다 작음 |
| 0 | 정확히 일치 |
| -1 | 보다 큼 |
=MATCH([기준값], [비교 대상 범위], [일치 여부])
그럼 아래 예제에서 INDEX함수와 MATCH함수 두 개를 아래와 같이 사용하여 [업체명]을 찾아보겠습니다.
=INDEX([참조할 값 범위], MATCH([기준값], [비교 대상 범위], [일치 형태]))
INDEX 함수에서 참조할 값 범위는 즉, 찾고자 하는 값은 [표1] G3:G7까지 있는 범위의 값을 구하고자 합니다. 그리고 데이터의 행과 열 위치를 구해주는 함수인 MATCH 함수를 작성해 줍니다. 기준값은 C3 열에 있는 값이고 해당 값과 비교할 범위 값은 H3:H7까지 있는 범위입니다. 그 후 정확히 일치해야 하니 0 을 넣어줍니다.
마지막으로 셀을 긁어서 다 채워줄 예정이니 범위 부분에 F4키를 이용하여 절대 참조 설정을 해줍니다. 그럼 아래와 같은 수식을 작성할 수 있습니다. 이후 드래그해주면 값이 채워지는 걸 볼 수 있죠?
=INDEX($G$3:$G$7,MATCH(C3,$H$3:$H$7,0))
이상 실무에서 가장 많이 쓰이는 vlookup함수와 index함수 match함수를 알아봤습니다. 제가 알고 있는 기준에서 열심히 쓴 거라 참고 용도로만 봐주시길 바랍니다! 그래도 알고 두 개의 함수를 알고 계신다면 업무시간을 단축시킬 수 있으니 꼭 기억해두세요!
