[엑셀꿀팁] VLOOKUP, INDEX & MATCH, 파워쿼리 병합 비교해보자_2탄 !! INDEX & MATCH 함수에 대하여

드디어 INDEX & MATCH 를 이용하여 VLOOKUP의 심화버전을 보여드릴 수 있게 되었습니다. 

 

지난 번 VLOOKUP의 경우, 여러 열을 끌고 와야할 경우 일일이 숫자를 변경해야 하는 불편함이 있었습니다. 

INDEX & MATCH를 쓰게 되면 그런 불편함 없이 한 번에 예쁘게 숫자를 들고 올 수 있습니다. 

 

다만 이번 편은 VLOOKUP의 심화편인 만큼 아주 사알짝 복잡하지만 유용하게 써먹을 수 있습니다.(혼합참조 주의)

 

01. INDEX함수 사용법 기본

- INDEX함수는 전체 범위에서 어디 부분에 위치하는 지를 찾아내는 함수입니다.

 

무조건 예시로 이해하시면 됩니다. 

 

■ INDEX 함수 예제

1행, 2행, 3행과 A열, B열, C열로 구성된 표가 있습니다. 

각 행과 열에는 그에 맞게 좌표를 구성하였습니다. 

 

=INDEX(C6:E8,2,2)의 경우 C6 ~ E8의 셀 중에서 행의 2번과 열의 2번이 교차하는 지점을 찾아주세요 라는 뜻입니다. 

그림으로 보게되면 이렇습니다.

INDEX함수 예시

그럼 INDEX(C6:E8,2,2)의 값은 ? (2, B)가 나옵니다.

 

하나만 더 INDEX함수 예시 들어볼게요

=INDEX(C6:E8,3,1)의 경우에는 어떤 값이 나타날까요?

 

해당 함수도 C6 ~ E8 중에서 3행 1열의 값을 찾아줘라는 뜻이니 3행 1열(A)를 찾아서 값을 살펴보니 (3, A)로 나타납니다.

 

INDEX함수는 살펴보니 전체 범위중에서 행과 열을 지정해주면 찾아줄게 ! 라는 함수였습니다.

다음은 INDEX함수와 찰떡함수인 MATCH함수에 대해 알아보겠습니다. 

 

02. MATCH함수 사용법 기본

- MATCH함수는 범위 내에서 순번을 뜻합니다.

 

바로 예시로 살펴보겠습니다.

INDEX 함수 예시와 동일하게 표가 있습니다. 

 

=MATCH(2,B6:B8,0)를 구해보고 싶습니다. 

해당 함수를 사람의 말로 읽어보자면, '2'를 찾아줘 / B6 ~ B8 범위에서, 0은 정확하게 일치하는 경우에만 이라는 뜻입니다.

그러니깐 B6 ~ B8 범위는 1, 2, 3으로 구성되어 있는데 이 중에서 2가 어디에 위치해있니? 라는 함수이며 2는 1, 2, 3중 두 번째에 위치하기 때문에 2를 반환합니다. 

 

숫자로 하니 헷갈리신다구요?

그럼 열을 살펴보겠습니다.

A, B, C 열 중에서 C가 몇 번째에 위치해있니? 라고 컴퓨터에게 함수로 물어보고 싶습니다. 

 

그런 경우에는 =MATCH("C",C5:E5,0) 로 사용합니다. 

왜 여기서는 C를 " " 로 묶어줬을까요?

엑셀에서 문자열의 경우 "  " 로 묶어줍니다. 

C는 A, B, C 중 세 번째에 위치해있으므로 3을 반환합니다. 

 

이제 INDEX와 MATCH를 하나씩 배웠습니다. 

 

사실 둘은 함께 썼을 때 아름답습니다. 

 

03. INDEX & MATCH 함수 사용법 응용

 

그럼 지난 번에도 VLOOKUP으로 사용했던 예시 그대로 INDEX & MATCH를 통해 어떻게 쓸 수 있는 지 보여드리겠습니다. 

빈칸을 채울 예정인 데이터

팀장님이 해당 포켓몬들의 값을 알아오라고 하였습니다. 

저희는 전체 원본 데이터를 가지고 있습니다. 

 

먼저 어떻게 결합할 지 고민해보면,

INDEX(전체범위, MATCH를 통해 원하는 행, MATCH를 통해 원하는 열) 이런 형태로 불러오면 될 것 같습니다. 

 

먼저 전체범위는 원본데이터의 범위를 지정해야 합니다. 

참조값(2)라는 시트의 E2에서 I152까지가 전체 범위입니다.

그럼 =INDEX(참조값!$E$2:$I$152,행,열)을 작성해줄 수 있습니다.(※  $는 행/열 고정을 위한 기호입니다) 

이제 배웠던 MATCH함수를 사용해 행과 열만 지정해주면 됩니다.

코뿌리의 체력값을 들고오기 위해서는

=MATCH('코뿌리', 원본데이터범위, 0) 으로 들고오면 됩니다. 

그게 실제 엑셀에서는 =MATCH(!$B2,참조값!$B$2:$B$152,0) 입니다. 

엇.. 왜 이렇게 복잡해졌죠 라고 생각이 드시겠지만 초록색 부분은 그냥 시트이름입니다. 그러니 주목해야 될 부분은 초록색 부분을 제외한 부분으로 봐주시면 됩니다. 

간단하게 만들자면, =MATCH($B2,$B$2:$B$152,0) 이런 형태로 나타날 것 같습니다. 

코뿌리의 행 값을 계산해보니 112번 행입니다. 

 

이제 MATCH를 통해 열만 가져오면 됩니다.

열은 행과 하는 방법이 동일합니다. 

=MATCH(E$1,참조값!$E$1:$I$1,0) 이런 형태로 가져오면 체력값은 1번, 공격값은 2번으로 가져올 수 있습니다. 

 

그럼 코뿌리의 값은 INDEX(전체범위, 112, 1) 라는 뜻으로 전체 범위에서 112행 1열의 값을 가져와 라고 해석하면 됩니다. 

 

INDEX & MATCH가 처음에는 굉장히 어렵게 느껴집니다만, 적응이 되시면 쉽게 쉽게 할 수 있습니다. 

 

업무 자동화에 한발자국 더 가까워지기를 바랍니다.