하이레벨 엑셀 2. 엑셀 함수 정리, Index + match 함수의 활용, #Index, #match, #vlookup, #xlookup

하이레벨 엑셀 2. 엑셀 함수 정리, Index + match 함수의 활용, #Index, #match, #vlookup, #xlookup

 

 

안녕하세요! 기획실 지차장 입니다.

 

오늘은 하이레벨 엑셀 함수 정리, 두번째 시간. Index 함수와 Match 함수의 콜라보레이션에 대해서 알아보겠습니다.

 

또한 일전에 매우 간단하게 설명드린 적이 있는 vlookup 함수와 오피스365 구독자에게 제공되는 xlookup 함수도 

 

살펴보도록 할께요. vlookup 함수에 대해서는 아래 포스팅을 참고해주세요.

 

vlookup은 두번이나 다뤘던 만큼, 직장에서 가장 많이 사용되는 함수 입니다.

 

---

실무 엑셀 5. 많이 사용하는 함수 시리즈#1, VLOOKUP

 

실무 엑셀 13. 엑셀 IF + vlookup 활용법, 조건부 vlookup 함수 활용, vlookup 사용법, IF 함수 사용법, 엑셀

---

Index 함수와 Match 함수는 각자 사용되기 보다는 같이 사용하시는 것이 더 일반적 입니다.

 

vlookup 함수가 체스의 룩이나 비숍이라면, Index 함수 + Match 함수의 조합은 퀸과 비슷합니다. 

 

vlookup 함수의 사각지대를 커버해주는데, 다만 여러번 사용하면서 손에 익히고 활용하지 않으면,

 

계속 사용 안하게 되는 함수 입니다. 왜냐하면, 엑셀 노가다로 커버 할수가 있으니까요; 그러나,

 

초생산성을 만들기 위해서는 한번 도전해보시는게 좋아요. 쉽게 쉽게 설명드리면서 가보겠습니다.

---

#엑셀 함수 정리, #Index + match, #Index, #match, #vlookup, #xlookup

 

---

① vlookup 함수

 

어떤 상황에서 index 함수와 match 함수를 써야할 지 보겠습니다.

 

아래 화면을 보시면 vlookup 함수를 이용해서, 아시아의 매출에 해당하는 5000 이란 data를 끌어왔습니다.

 

vlookup 을 사용하려면, 끌어오고자 하는 data가 검색값보다 오른쪽 열에 위치해야 합니다.

 

행과 열이 뭔지 잘 모르실 경우 아래 포스팅을 참고 해주세요 :D

 

기초 엑셀 4. 왕초보 엑셀 시리즈#1, 엑셀 셀, 행, 열

 

아시아 를 검색해서 매출을 가져왔는데, 매출 F열(끌어오고자 하는 값)은 아시아 C열(검색값)보다 오른쪽에 있죠? 

 

 

그렇다면, 한국을 검색해서 매출을 가져오려면 어떻게 해야 할까요?

 

끌어오고자 하는 data 매출 F열은 한국G열 왼쪽에 있어요. 이럴 경우, 이 모양 그대로에서는 vlookup 사용이 불가합니다.

 

억지로라도 사용해야 한다면, 아래 그림과 같이 매출 F열 앞쪽에 국가명을 입력해서, 매출이 한국 오른쪽열에 배치되게 만들어야만 vlookup 사용이 가능합니다.

 

---

② Index 함수

 

Index 함수는 끌어오고자 하는 data가 검색값보다 오른쪽, 왼쪽, 위, 아래 상관 없이 어디든 가능합니다.

 

기본 모양은 이렇습니다.

 

= Index ( 범위 , 행번호 , 열번호 )

 

아래 그림에서 보시듯 범위를 지정한 후, 위에서 아래 방향으로 첫번째행, 왼쪽에서 오른쪽으로 3번째열 data를 불러왔는데요. 오른쪽 왼쪽 위 아래 상관없이 몇번째 위치에 있는지만 지정해주면 됩니다.

 

예시를 하나 더 추가해서 볼까요? 아래 그림과 같이 index를 사용하게 되면 아시아 열 기준 오른쪽에 있는 매출이든

 

국가 열 기준 왼쪽에 있는 매출이든 전혀 상관없이 data를 불러 올 수 있습니다.

그런데요. Index에도 치명적인 단점이 있으니, 행번호, 열번호를 매번 이렇게 수동으로 번거롭게 입력해줘야만 한다면,

 

너무 비효율적이라는 것이죠. 그래서 이때 match 함수가 등장하게 됩니다.

---

③ Match 함수

 

Match 함수는 행이나 열에서 찾는 값이 몇번째에 위치하는 지를 알려줍니다.

 

기본 모양은 이렇습니다.

 

= Match ( "찾을 것" , "배열" , 옵션 )

 

찾을 것 = 따옴표 " " 안에 직접 입력해도 되고, 셀 주소를 넣어도 됩니다.

 

배열 = 행 or 열

 

옵션 = 0으로 입력해주세요. (정확하게 일치하는 값을 찾을 경우)

 

아래 그림에서 보시듯 "찾을 것"과 "행 or 열"을 입력 후, 옵션에는 0 (정확하게 일치하는 값을 찾겠어요) 하면 끝

 

오른쪽으로 3번째열 data를 불러왔는데요. 오른쪽 왼쪽 위 아래 상관없이 몇번째 위치에 있는지만 지정해주면 됩니다.

 

가위는 배열 C5:F5 에서 세번째

 

바위는 배열 C7:F7에서 네번째

 

보는 배열 C7:F7에서 첫번째, 배열 C4:C7 에서는 네번째

마찬가지로 예시를 하나 더 들어볼께요.

 

아시아의 매출 5000 은 아시아 ~ 한국 배열 C5:G5 에서 몇번째에 있을까요?

 

독일의 매출 3800 은 매출 배열 F4:F7 에서 몇번째에 있을까요?

 

답은 아래 그림에 있습니다.

 

이렇게 Match 도 알아봤는데요. 그래서 Index 와 Match 는 어떻게 조합하면 되는 것일까요?

---

④ Index + Match 함수 ★★★★★

 

너무나 중요해서, 별 다섯개 드렸습니다. 앞서서 Index 함수는 = Index ( 범위 , 행번호 , 열번호 )

 

Match 함수는 = Match ( "찾을 것" , "배열" , 0 )

 

Index + Match 함수 = Index ( 범위 , Match ( "찾을 것" , "배열" , 0 ) , Match ( "찾을 것" , "배열" , 0 ) )

 

Index 에서 행과 열을 수기로 입력하는 것이 문제였으니까 행번호와 열번호를 Match로 대체한다.

 

잠깐만요. 너무 당황하지 마시고, 예시를 보면서 따라오시면 쉽습니다.

 

아래 그림을 보시면, 행번호와 열번호 모두 Match 함수로 대체할 수도 있고, 둘중 하나만 대체할 수도 있습니다.

첨부 파일을 드릴테니, 꼭 직접 실습을 매일 한번씩 해보셔야 해요.

 

아무리 설명을 여러번 들으셔도 직접해보시지 않으면 감이 안옵니다. 꼭꼭 연습을 하셔야만 익힐수 있습니다.

---

⑤ Xlookup 함수

 

xlookup 함수는 아쉽게도 오피스365 사용자만 사용이 가능합니다.

 

그래도 사용하면 참 유용하고 무시무시한 함수이나 맛보기로만 알아볼께요.

 

기본 모양은 이렇습니다.

 

= xlookup ( "키값" , "키값 data 배열" , "찾고자하는 data 배열" )

 

xlookup 함수가 매력적인 이유는 무엇보다도 직관적인 것에 있습니다.

 

나 이거 찾고 싶어, 찾을 배열 여기 있고, 찾고자 하는 배열은 여기야. ( 배열이기 때문에 hlookup 기능까지 있습니다. )

 

vlookup 과 hlookup 을 섞어놨고, 왼쪽오른쪽위아래 구분도 없으니 킹왕짱이죠. 

 

예시1)

예시2)

---

이상 vlookup 함수, index 함수, match 함수, index + match 함수, xlookup 함수 의 활용에 대해서 알아봤습니다.

 

엑셀 함수 활용은 연습이 매우 중요합니다.

 

오늘은 글이 너무 길었네요. 또뵈요!

 

하이레벨2.xlsx

0.01MB