WideCastle의 새로운 시작

직장인 엑셀 (Excel) 실무 - INDEX, MATCH 함수 중첩하여 활용하기

안녕하세요~ Wide Castle 입니다.

직장에서 보통 vlookup을 많이 사용하여 자료를 만드는데요.

vlookup으로 값을 찾아올 수 없는 경우가 있습니다.

저 같은 경우는 vlookup으로는 기준열로 부터 우측으로만 값을 찾아올 수 있는데 어떤 자료는 기준열로 부터 좌측에 있는 값도 찾아 와야 하는 경우가 있을 겁니다.

이때 index, match 중첩 함수를 모르는 직장인은 rawdata 자체를 편집하여 vlookup으로 찾아올 수 있게 노가다 작업을 하는 사람들도 있죠? (저도 첨에는 그랬습니다.)

하지만 index, match 중첩 함수를 이해하고 잘 연습해두면 이럴때 수월하게 작업을 하실 수 있고 경쟁력을 갖춰야 하는 직장에서 남보다 더 나은 모습을 보일 수 있을 겁니다^^

자 그럼 Index, Match의 기본적인 사용 예를 알려드리겠습니다.

대부분 이거 하나면 다 됩니다^^

아래는 지난번 countif 함수 설명할 때 사용했던 예제인데요. 이걸 활용해서 해볼께요.

C열에 세부코드명 이 있고 B열에 세부코드가 있습니다.

세부코드명으로 좌측에 있는 세부코드 값을 vlookup 함수로는 찾아올 수가 없죠?

그래서 Index, Match 중첩함수를 사용했습니다.

M5 셀에 있는 멍게의 세부코드 값을 가져와 보았습니다.

함수를 결과는 

=INDEX($A$3:$J$17,MATCH(L5,C3:C17,0),2)

함수풀이 : $A$3:$J$17 전체범위에서 L5의 내용을 C3:C17 에서 정확하게 찾아 전체 범위의 2번때 열의 값을 가져와라.

이렇게 보니까 되게 복잡해 보이죠? 하지만 되게 간단합니다.

아래 함수마법사를 이용해 해당 함수를 작성하는 단계별로 설명을 하겠습니다.

1단계. index 함수를 입력하고 함수마법사 열기 

2단계. index 함수 마법사에서 Array 범위 정하기

Index 함수마법사의 첫번째 항목 Array는 찾고자 하는 표 전체 범위를 선택해 주시면 됩니다.

셀범위에 이름을 지정해서 함수에 이름을 쉽게 넣는 방법도 있지만 오늘은 Index, Match에 집중하기 위해 이 방법으로 하겠습니다.

3단계. index 함수 마법사에서 Row_num 항목에 Match 함수 중첩하여 넣기

Index 함수마법사 창에 있는 Row_num에 Match를 중첩합니다.

Row_num 항목은 찾고자 하는 행번호를 입력하는 곳인데요. 우리가 행번호를 지정할 수 없으니 match 함수를 중첩하여 행번호를 찾는 겁니다.

중첩방법은 match() 를 입력하고 수식입력창에 match 부분에 커서를 두고 클릭하면 바로 match 함수 마법사 창으로 전환되어 열리게 됩니다.

위에 전환되어 열린 Match 함수 마법사 입니다.

4단계. 전환되어 열린 Match 함수 마법사에서 Lookup_value 항목에 찾으려는 값 선택하기

Match 함수마법사에서 Lookup_value는 찾으려는 값 입니다.

여기서 찾으려는 값은 L4 에 있는 멍게가 되겠죠?

5단계. 전환되어 열린 Match 함수 마법사에서 Lookup_array 항목에 찾으려는 값의 범위 선택하기

Lookup_array는 찾고자 하는 값의 참조 범위 입니다.

즉 4단계에서 찾고자 하는 멍게를 어디서 찾을지 범위를 지정해 주는 겁니다.

차후 수식 복사시 범위를 고정하기 위해 F4키를 사용하여 절대값으로 변경하였습니다.

6단계. 전환되어 열린 Match 함수 마법사에서 Match_type 항목에 0 입력하기

Match_type는 0을 입력합니다. 찾고자 하는 값과 정확하게 일치하는 첫번째 값을 가져오라는 의미 입니다.

자 이제 Match 함수 값이 모두 입력되었습니다. 이제 Index 함수 마법사 창으로 넘어가서 마무리해야겠죠??

7단계. Index 함수 마법사로 되돌아가기

이걸 잘 모르시는 분들이 많던데 수식입력창에 Index 함수 글자를 클릭하면 바로 아까 작성하던 Index 함수창이 열립니다.

8단계. Index 함수 마법사에서 Column_num 에 찾고자 하는 열 값 입력

우리가 찾고자 하는 값은 세부코드 B열 입니다. 아까 찾고자 하는 전체 범위에서 2번째 열이 세부코드 이죠?

그래서 2를 입력합니다.

9단계. 결과값이 나왔습니다.

자 !! 이제 우리가 찾고자 했던 멍게의 세부코드 값을 찾아왔습니다.

수식은 밑으로 쭉 끌어 땡겨서 복사하면 끝 !!!

이걸 응용하면 아주 손쉽게 여러가지 응용이 가능하니 일단 기본기를 여러번 실무에 적용하셔서 연습하시면 실력 향상에 아주 큰 도움이 됩니다.

이상 INDEX 와 MATCH 함수를 중첩하여 사용해 보았습니다.

좋아요 및 구독은 저에게 큰 성취감과 기쁨을 줍니다 ~~^^

고맙습니다.^^