[Excel] 8. 엑셀로 능력있는 직장인이 되는 법. 초급편 (VLOOKUP, INDEX, MATCH 등)

안녕하세요! @gbgg 입니다. 능력있는 직장인이 되는법! 엑셀 자동화 강의 여덟번째 시간입니다!

지난 초급편에서 초급 기초함수와 기초 함수들을 설명드렸는데요,

오늘은 중급용 함수 몇 가지와 그를 활용한 예제를 다뤄보겠습니다.

사실 기초함수나 중급, 고급함수나 단순히 요구하는 값만 넣어주면 되기 때문에

난이도가 큰 의미가 없지만 함수에서 요구하는 값만 넣을 것이 아니고 연계시켜서 사용해야

그 활용도가 높아지기 때문에 이렇게 나누어 보았습니다.

활용 예제와 함께 잘 설명 드릴게요!

---

VLOOKUP과 HLOOKUP함수는 많이 들어보셨을텐데요,

범위 내 데이터를 검색해서 같은 행/열에 있는 값을 반환한다. 말이 굉장히 어렵습니다.

간단하게 예제를 통해 이게 무슨말인지 살펴보겠습니다.

위와 같은 형태의 데이터에서 한가지 데이터(이름) 밖에 알지 못할 때

그 옆에 있는 값들을 알고 싶으면 VLOOKUP을 사용하면 됩니다.

VLOOKUP의 V 뜻이 vertical 이라서 세로라고 햇갈리실 수 있는데

수직으로 데이터를 검사한다는 뜻으로 생각하시면 됩니다.

HLOOKUP은 위와 같은 형태의 데이터 형식에서 한가지 데이터(이름)밖에 알지 못할 때,

그 위아래에 있는 값들을 가져와야 할 때 사용됩니다.

마찬가지로 HLOOKUP의 H는 horizontal 즉 가로로 데이터를 검사한다는 뜻으로 생각하시면 됩니다.

이 두 함수중에 VLOOKUP을 더 많이 사용하는 이유는 단순히 데이터 형태가 수직으로 내려가는 형식이 많기 때문이지 기능이 달라서 그런것이 아닙니다.

그렇다면 더 자주 사용되는 VLOOKUP을 어떻게 사용하는지 한번 알아볼까요?

---

• 검색할 값 : 검색할 값 입니다. 위 표를 참조하자면 "리플"이 되겠죠?
• 검색할 범위 : 검색할 값을 어디서 찾을것인지 그 :전체" 범위를 지정해주면 됩니다.
• 반환 값 위치 : "전체" 범위의 가로 셀 갯수가 5라면 1~5의 번호로 원하는 위치를 출력할 수 있습니다.
• 일치여부 : TRUE나 FALSE 값을 넣을 수 있는데 TRUE는 근사값, FALSE는 정확한 값을 찾아냅니다. 보통 FALSE를 많이 사용합니다.

자 한번 따라해 보겠습니다.

=VLOOKUP(D12,D3:F9,2,FALSE)

혹시 모르니 예제코드 첨부할게요!

자 이렇게 범위 내에서 리플 이라는 항목이 있는 부분을 찾아 검색한 후 그 오른쪽에 있는 연락처 값을 가져와 보았습니다.

주소를 가져오려면 어떻게 해야할까요?

=VLOOKUP(D12,D3:F9,3,FALSE)

숫자 2를 3으로만 바꿔주면 바로 값을 얻을 수 있습니다.

바로 위 그림과 같이 말이죠.

혹시...

이상한점을 느끼셨나요?

왜 전체 범위를 지정할 때 날짜 부분은 포함하지 않았을까요?

여기서 VLOOKUP과 HLOOKUP의 한계가 드러납니다.

VLOOKUP, HLOOKUP 함수는 애초에 설계될 때 부터 지정한 범위의 "첫번째 열" 값만 검색할 수 있도록 만들어졌습니다.
왜 그런것인지는 모르지만 이 문제 때문에 기존 데이터 형식을 바꾸지 않는 한 또는 위 그림처럼 날짜 부분을 아예 포함하지 않는 등 다른 방법을 써야 합니다.

그렇다면 여기서 한번 정리해보겠습니다.

---

자 그렇다면 이제 INDEX, MATCH 함수를 짬뽕해서 데이터를 찾아보겠습니다.

---

• 반환 값 범위 : 반환 받을 값. 즉 검색한 값을 통해 받고 싶은 값의 범위를 지정합니다.
• 검색할 값 : 알고 있는 값. 즉 위에서는 "리플"을 알고 있기 때문에 선택해주면 됩니다.
• 값 범위 : 이 "리플" 이라는 값이 들어가 있는 테이블의 범위를 지정해주면 됩니다.
• 0 : 마찬가지로 0은 일치, 1은 보다작음, -1은 보다 큼 인데 대부분 0을 사용합니다.

생각보다 간단합니다. 오히려 이게 VLOOKUP보다 쉬울수도 있습니다.

자 그럼 아까 VLOOKUP에서 찾지 못했던 테이블 형식 기준으로 날짜 값을 한번 찾아보겠습니다.

=INDEX(C3:C7,MATCH(D12,D3:D7,0))

아래 사진 기준으로 코드는 위와 같습니다.

자 아래와 같이 데이터를 잘 찾아내는 모습을 보실 수 있습니다.

• 혹시 아래와 같이 값이 나온다면?

셀 서식에 들어가 일반 형식으로 되어있는 것을 날짜 형식으로 바꾸시면 됩니다.

---

엑셀 자동화

어쩌면 데이터를 다룰 때 필수라고 볼 수 있는 VLOOKUP 함수와 INDEX, MATCH 함수를 다루어 보았는데요.

이미 많이 사용하고 계신 분도 많으실것 같고 처음 접해보시는 분들도 많으실 것 같은데

이 두 함수의 차이를 명확히 정의해 놓은 글들이 몇 없어 두 가지 경우를 예로 들어 비교해보았습니다.

이 두 함수를 초급편에서의 핵심 함수로 볼 수 있지 않을까 하는 생각도 듭니다.

이렇게 강력한 기능을 지닌 엑셀 함수와 앞 강의에서 포스팅했던 VBA를 통한 자동화 기능을 잘 합친다면 아주 강력한 자동화 엑셀 프로그램을 만들 수 있지 않을까요?

다음 초급편도 기대해 주세요! 좋은 함수로 다시 찾아뵙겠습니다.

혹시 강의에 추가되었으면 하는 기능이 있다거나

조언해주실 부분이 있으시다면 댓글로 거침없이 피드백 주세요! 쓴소리 좋아합니다!

능력있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때 까지 한번 열심히 달려봅시다!

다음 포스팅에서 뵙겠습니다!

---

지난 포스팅

[Excel] 1. 능력있는 직장인이 되는 법. 엑셀 자동화란?

[Excel] 2. 능력있는 직장인이 되는 법. 다른 파일 불러오기!

[Excel] 3. 능력있는 직장인이 되는 법. 물품 입고시키기!

[Excel] 4. 엑셀로 능력있는 직장인이 되는 법. 간편한 UI 만들기!

[Excel] 5. 엑셀로 능력있는 직장인이 되는 법. 로그인 + 접속기록 만들기!

[Excel] 6. 엑셀로 능력있는 직장인이 되는 법. 재고(자산) 관리용 현황판 만들기!

[Excel] 7. 엑셀로 능력있는 직장인이 되는 법. 초급편 (기초함수 소개)

---