VLOOKUP 대체 함수, INDEX MATCH 함수
이전글에서 엑셀 함수의 꽃이라 불리는 VLOOKUP 함수를 배웠습니다.
VLOOKUP 은 찾을 값이 가장 좌측에 위치한 참조 범위의 열 방향 우측에 있는 값을 찾는 함수 입니다.
여기서 주목할 단어는 우측에 입니다. 찾을 값의 우측에 있는 참조 값만 가져올 수 있다는 것 입니다.
이게 무슨 문제가 될까? 하는 생각이 들 겁니다.
일반적인 경우에는 별 문제가 되지 않습니다.
하지만, 회사에서 사용할 때는 큰 문제가 될 수 있습니다.
이번 글에서는 찾는 값의 위치를 출력하는 INDEX 함수와, 참조 범위에서 특정한 위치의 값을 출력해주는 MATCH 함수, 그리고 이 두 함수를 결합하여 INDEX MATCH 결합 함수로 사용하는 것에 대해 배워보겠습니다.
엑셀에서 값을 참조하기 위해 가장 많이 사용하는 함수는 바로 VLOOKUP 입니다.
하지만 VLOOKUP 함수에는 치명적인 단점이 있습니다.
바로 찾는 값의 우측에 있는 값만 참조할 수 있다는 것 입니다.
찾는 값의 우측에 있는 값만 참조할 수 있다면, 좌측에 있는 값은 어떻게 참조해야 할까요?
바로 이 때 사용할 수 있는 방법이 INDEX 함수와 MATCH 함수를 결합하여 사용하는 INDEX MATCH 함수 입니다.
VLOOKUP 함수에 익숙한 사용자들도 INDEX MATCH 함수를 결합하여 사용할 때 어려움을 겪습니다.
어려운 개념이 아니지만 함수 두 개를 결합하여 사용한다는 것 자체가 난이도 높은 문제로 느껴지기 때문입니다.
어렵게 느껴지는 INDEX MATCH 함수를 사용하지 않고 문제를 해결할 수 있는 팁들이 있지만, 근본적인 해결책은 아닙니다.
다소 어렵게 느껴지더라도 이번 글에서 INDEX MATCH 함수를 정확하게 이해하고 넘어가시기 바랍니다.
한 번에 이해하기는 어렵습니다. 이 글을 읽는 순간 만이라도 이해하실 수 있으면 됩니다.
잘 모르겠으면 다시 이 글을 찾아와 읽어보시기 바랍니다.
MATCH 함수
아래의 표에 학생의 생년월일과 이름 정보가 포함된 목록이 있습니다.
해당 목록에 있는 학생의 이름으로 생년월일 정보를 검색하여 참조해 보겠습니다.
이전에 사용했던 VLOOKUP 함수의 결과를 나타내는 F3 셀은 “#N/A” 오류가 출력되고 있습니다.
이유가 무엇인지 눈치 채셨나요? 바로 찾을 값의 좌측에 참조하려는 값이 있기 때문입니다.
이련 경우 VLOOKUP 을 사용할 수 없습니다.
함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(MATCH)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.
위의 그림에서 =MATCH( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (lookup_value, lookup_array, match_type) 의 의미는 아래와 같습니다.
lookup_value : 찾을 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
lookup_array : 찾을 값(lookup_value)이 포함된 범위 주소를 두 번째 인수로 입력해야 합니다. (필수 입력)
match_type : 보통 정확히 일치하는 첫번째 값을 찾는 옵션 0 을 세 번째 인수로 입력해야 합니다. (필수 입력)
F5 셀에 =MATCH(E3,C3:C8,0) 함수 수식을 입력합니다.
그러면 F5 셀에 “6” 이 출력됩니다.
찾는 값인 lookup_value “서경준” 학생은 참조 범위 lookup_array “C3:C8” 에서 6 번째에 위치하고 있다는 뜻 입니다.
MATCH 함수의 출력값 만 보자면 어떤 의미가 있는지 이해하기 어렵습니다.
이 결과가 INDEX 함수에 사용되었을 때 아주 중요한 의미가 있는 값이 된다는 것을 확인해 보겠ㅅ브니다.
INDEX 함수
함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(INDEX)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.
위의 그림에서 =INDEX( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (array, row_num, [column_num]) 의 의미는 아래와 같습니다.
array : 참조 범위를 첫 번째 인수로 입력해야 합니다. (필수 입력)
row_num : 참조 범위(array)에서 몇 번째 행번호에 위치한 값인지를 두 번째 인수로 입력해야 합니다. (필수 입력)
[column_num] :참조 범위(array)에서 몇 번째 행번호에 위치한 값인지를 세 번째 인수로 입력해야 합니다. (선택 입력)
F6 셀에 =INDEX(B3:B8,F5) 함수 수식을 입력합니다.
그러면 F6 셀에 “2002.7.23” 값이 출력됩니다.
참조범위 B3:B8 에서 6 번째 행에 위치한 값인 B8 셀의 값이 출력된 것 입니다.
결론적으로 서경준 학생의 생년월일 값이 출력된 것 입니다.
이 값은 이전에 VLOOKUP 함수를 사용했을 때 얻은 값과 같습니다.
VLOOKUP 함수를 사용할 수 없는 경우임에도 동일한 결과를 얻은 것 입니다.
VLOOKUP 대체 함수, INDEX MATCH 함수
MATCH 함수의 결과값을 INDEX 함수에 인수로 사용했더니, VLOOKUP 함수의 결과값과 동일한 값을 얻을 수 있었습니다.
INDEX, MATCH 두 개의 함수를 결합하여 값을 얻었다고 볼 수 있습니다.
좀 더 쉽게 사용하려면 MATCH 의 결과값을 먼저 계산한 후 INDEX 함수에서 그 값을 사용하면 됩니다.
하지만, 실제 엑셀 작업시에는 두 개의 함수를 결합하여 하나로 쓰는 것이 적합합니다.
INDEX, MATCH 함수를 각각 사용한 예제를 보여드린 것은 이러한 과정을 좀 더 쉽게 접근하게 도움을 주고자 했던 것 입니다.
F7 셀에 =INDEX(B3:B8,MATCH(E3,C3:C8,0)) 함수 수식을 입력합니다.
F6 셀에 입력된 =INDEX(B3:B8,F5) 함수 수식과 천천히 비교해 보시기 바랍니다.
INDEX 함수에 두 번째 인수로 사용된 F5 대신 MATCH(E3,C3:C8,0) 가 입력된 것을 볼 수 있습니다.
이전엔 F5 셀에 미리 계산해둔 =MATCH(E3,C3:C8,0) 함수 수식의 값을 사용했다면, 이번엔 함수 수식 그 자체로 사용을 한 것 입니다.
수식이 꽤 복잡해 보이지만 결론은 동일한 수식이라는 뜻 입니다.
결과 역시 동일하게 출력되고 있음을 확인 가능합니다.
INDEX MATCH 결합 함수를 사용하는 것에서 주의할 것은 INDEX 함수와 MATCH 함수에 사용된 참조 범위의 행 범위가 동일하다는 것 입니다.
둘 다 3행 부터 8행 까지 입니다.
표로 작성된 데이터에서 특정 행은 하나의 완전한 자료의 집합을 의미하는 경우가 많습니다.
VLOOKUP 함수도 이러한 형태의 자료에서 특정한 항목(컬럼) 값을 참조하기 위해 사용하는 함수입니다.
VLOOKUP 대체 함수 의미로 INDEX MATCH 결합 함수를 사용할 때도 동일합니다.
열은 B열, C열 로 다르지만 행은 3행, 8행 으로 동일하게 지정해야, MATCH 함수의 결과값이 동일한 행의 데이터를 참조할 수 있다는 것을 꼭 기억하시기 바랍니다.
VLOOKUP 대체 함수, INDEX MATCH 함수 동작 테스트
위의 그림에서 E3 셀 찾을 이름 값을 다양하게 변경해 보겠습니다.
좌측의 학생 목록에 있는 이름 중 어떤 값을 입력하더라도 정확한 생년월일 정보가 출력되는 것을 확인할 수 있습니다.
즉, 완벽하게 VLOOKUP 함수와 동일한 기능을 하고 있다는 것을 알 수 있습니다.
오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.
각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.
엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.