VLOOKUP, INDEX & MATCH 함수 총망라(고급 활용편)

서론

많은 분들의 이야기를 들어보면 VLOOKUP 함수와 SUMIF / AVERAGEIF 함수 수준을 자유롭게 구사하느냐, 아니냐가
엑셀을 잘 다루느냐 아니냐를 판단하는 기준이 되는 것 같다.
실제로 VLOOKUP 함수는 실무에서 쓰는 엑셀 활용도 측면에서도 빈도수가 상당히 높은 함수이다.
기본적인 기능이나 사용법은 여타 다른 자료에서도 쉽게 찾아볼 수 있으니, 본 포스팅에서는 더 깊은 이야기를 해볼까 한다.
VLOOKUP을 자주 접하는 분들에게도 항상 아쉬운 부분들이 있기마련이다.
내가 생각하는 그 한계성에 대해서 아래에 간단하게 3가지 정도로 정리해 보았다.

---

VLOOKUP 함수의 한계

조회하고자 하는 데이터열보다 좌측에 위치한 값을 조회할 수 없음

범위에서 기준 데이터열(녹색)이, 찾으려는 데이터열(빨강)보다 우측에 존재할 경우

다중 조건을 만족하는 값을 찾기 어려움

조건 여러개를 만족하는 값을 찾아야 하는 경우

중복된 값이 있다면, 첫번째 값만 가져오는 문제가 있음

조건을 만족하는 여러개의 결과값을 불러와야하는 경우

많은 분들의 질문들을 듣다 보면, 어떤 기능을 요구하고 원하는지에 대한 파악이 자연스럽게 이루어진다.
각기 다른 분야에서 엑셀을 활용하지만, 공통적으로 VLOOKUP을 이용할 때 해결하지 못하는 부분들은 위 세가지가 대표적이었다.
이번 포스팅에서는 이 세가지 이슈들에 대한 각각의 대처 방법을 알아보려고 한다.

---

INDEX & MATCH 함수로 대체

위치와 상관없이 셀주소를 통해 값을 찾아내는 INDEX & MATCH 함수 활용

INDEX 함수와 MATCH 함수는 사실 각각의 함수이기도 하지만, 짝지어서 많이 사용되는 영어로치면 숙어와 같은 느낌의 복합수식이다.

=INDEX(범위,행번호[,열번호])

INDEX 함수는 범위를 설정하고, 그 범위안에서 행번호를 기입하면 그 값이 툭 튀어나온다.

'행번호를 모두 알고있는 것도 아니고, 일일히 내가 세보고 그 숫자를 기입하느니.. 그냥 값을 직접쓰고말지'
라는 생각을 누구나 할텐데, 그래서 저 행번호 위치에 MATCH 함수를 조합한다.

=MATCH(찾을값,범위,정확하게찾을지여부)

MATCH 함수는 내가 찾고자하는 값이 범위의 몇번째에 있는지 숫자로 반환해준다.

후술하겠지만, 과일은 1번째 뿐만 아니라, 2번째와 3번째에도 존재하지만 MATCH 함수의 결과값은 항상 가장 윗 데이터, 혹은 가장 좌측 데이터 값만 반환한다.
즉, 범위를 설정하면 검색하는 순서가 "왼쪽 위 → 오른쪽 아래" 라고 생각하면 편하다.
그리고, 이 두 함수를 조합한 식은 다음과 같다.

=INDEX(찾을값의 범위,MATCH(조건1값, 조건1범위, 0))

영어라는 분류값을 가진 값 데이터를 가져왔다.

가만 생각해보니, VLOOKUP과 동일한 기능을 한다.
조금 더 복잡한 수식이지만, 그만큼 장점도 존재한다. 바로 참조범위나 열번호를 기입할 필요없이 조건에 맞는 행번호를 MATCH함수가 찾아주면, 그것에 맞는 인덱스값을 찾을 수 있게 된다.
2019 버전 이상 혹은 365버전의 엑셀을 이용하는 분들께는 XLOOKUP의 신규함수가 있는데, 이 함수 역시 이런 VLOOKUP의 단점을 완벽히 보완한다.

INDEX & MATCH 함수 이용시 주의사항

MATCH 함수에서의 참조범위와, INDEX 함수에서의 참조범위의 길이는 반드시 같아야 한다.
=INDEX($D$3:$D$11,MATCH($F$3,$B$3:$B$11,0))

INDEX 함수 참조범위가 꼭 세로로 길 필요는 없다? 열번호로 찾기

=INDEX($C$3:$D$11,MATCH($F3,$B$3:$B$11,0),1)
=INDEX($C$3:$D$11,MATCH($F3,$B$3:$B$11,0),2)

세번째 인수(열번호)는 생략가능한 인수지만, 이처럼 입력하게 되면 범위에서 N번째 열 데이터를 가져올 수 도 있다.

다중 조건을 만족하는 값 찾기

다양한 조건을 모두 만족(AND)하거나, 혹은 일부만 만족하는 여러개의 값(OR)을 가져오고 싶을 때가 있다.
(... 인정한다. 설명 더럽게 못했음)
그냥 보도록 하자...

우선 알아야 할 것은, TRUE와 FALSE의 개념이며, 엑셀에서 TRUE는 1로 표현가능하고, FALSE는 0으로 표현가능하다는 점이다. 또, 우리는 수학적 특성을 통해 어떤 값이든 0을 곱하면 0이 된다는 사실을 알 고 있다.

모든 결과를 만족한 값의 곱만이 1을 반환한다 .

즉, "우리가 찾고자 하는 조건을 만족한다" 라는 개념을 TRUE 라고 본다면, 반대로 FALSE는 "우리가 찾고자 하는 조건을 만족하지 않는다"로 설명 가능하고,
(조건1의 매칭결과)*(조건2의 매칭결과)*...연산에서 1을 반환하는 것만이, 모든 조건을 만족했다고 볼 수 있다.

=INDEX(찾을범위,MATCH(1,(조건1범위=조건1)*(조건2범위=조건1),0))

MATCH 함수에서 찾을 값(첫번째 인수)의 값이 1이다. 즉 모든 조건을 만족한 값을 찾는다는 뜻이 된다.
MATCH 함수의 참조범위(두번째 인수)위치에 (조건1범위=조건1) * (조건2범위=조건2) 가 들어왔다.
이 결과값이 "1"이 나오는 값만 찾겠다는 의미가 된다.

분류(조건1)가 "과일"이며, 글자수(조건2)가 "3"개인 값을 찾아냈다.

{=INDEX($D$3:$D$11,MATCH(1,($B$3:$B$11=$F$4)*($C$3:$C$11=$G$4),0))}

이 수식은 참고로 "배열수식" 이다.
따라서, 수식 입력시 입력을 다 마치고 난뒤, 그냥 엔터가 아닌 CTRL+SHIFT+ENTER 키를 눌러야 정상적으로 작용한다.
(365버전 부터는 동적배열기능을 가지고 있어서 그냥 ENTER로 입력해도 무방하다.)
수식의 앞 뒤에를 {} (중괄호)로 감싸고 있다면, 그것은 배열수식을 의미한다.
배열수식이 어떤 것인지에 대해서는 또 다른 큰 파트가 될 것이므로 여기서는 다루지 않겠다. 검색해보시길 바란다.

이 방식을 이용하면, 2개의 조건 뿐아니라 그 이상의 조건을 모두 만족하는 값을 찾아낼 수 있게 된다.
또, 조건과 조건사이에 곱셈기호가 아닌 + 기호를 이용하면, AND 조건(모두 만족하는)이 아닌 OR 조건(하나만 만족하는) 구현도 가능하다.

중복 값일 경우, 원하는 조건의 값을 "모두" 가져오는 수식 작성

자, 이제 마지막....
(점점더 높은 난이도를 요구하고 있다)

찾고자하는 분류값에 매칭되는 값이 여러개일때, 기본적으로 VLOOKUP함수와 MATCH함수는 위에서 기술한 검색방식에 따라 가장 윗 값만을 불러온다.

따라서, 나머지 값들을 모두 조회하기 위해서는 조금 더 복잡한 처리가 추가적으로 필요하다.

과일을 여러개 값을 갖고있지만, 사과밖에 불러오지 않는다.

아이디어는,

1. 조건에 맞는 값이 위치한 행번호를 결과값으로 가져온뒤,

2. 오류 반환이 아닌 행번호를 나타내는 값들을 "정렬"시키고

3. 그 값들만 인덱스함수를 씌워 최종 값으로 나타내게 하는 방법이다.

백문이 불여일견.

먼저, ROW / COLUMN 함수에 대한 이해가 필요하다.

=ROW(범위)

이렇게 실행하면, 범위에 해당되는 "행" 번호값을 배열로 뱉는다.

마찬가지로 COLUMN 함수를 쓰면, "열" 번호를 반환한다.

열번호를 반환하지만, 첫번째 값이 3을 불러온다.

우리가 참조한 범위내에서 순차적인 값을 불러오기 위해서는, MATCH함수를 씌워주면 된다.

MATCH 함수에서 "찾아야할 값"과 "찾아야할 범위"를 똑같이 넣어줌으로 인해, 범위 안에서의 행번호를 순차적으로 불러들일수 있다.

그리고 우리는, **조건에 맞는 데이터들만 행 번호를 불러와!**를 구현하기 위해 다음과 같은 수식을 쓴다.

여기서부터는 결과값으로 범위를 반환하는 "배열수식"이므로, 수식입력시 CTRL+SHIFT+ENTER로 입력해야한다.

{=IF(조건열=조건,MATCH(ROW(값범위),ROW(값범위)))}

조건에 맞는 행번호만 반환하기 위해서, IF함수를 사용했다.

다음은, 조건에 맞는 값들을 모두 불러오기 위해서, 행번호를 반환한 데이터만 정렬 시키는 과정이 필요하다.

여기서 MIN, MAX, SMALL, LARGE 함수등을 활용할 수 있는데,

예제에서는 SMALL함수를 활용해보겠다.

{=SMALL(IF(조건범위=조건,MATCH(ROW(값범위),ROW(값범위))),1)}
{=SMALL(IF(조건범위=조건,MATCH(ROW(값범위),ROW(값범위))),2)}

SMALL함수는 몇번째로 작은 데이터를 가져올까요? 라는 뜻이기 때문에

뒤에 쓴 1, 2 라는 숫자에 따라서 첫번째로 작은 수(MIN과 같은 결과값), 2번째로 작은수를 각각 출력한다.

근데 여기서 우리에게 "작은수"라는 개념은, "더 작은 행번호"를 의미하게 된다.

똑똑한 분이라면 이미 눈치채셨겠지만, 앞서 소개한 MIN, MAX, SMALL, LARGE 함수 등은 내가 불러오고자하는 조건값의 행번호를 컨트롤 하는 함수라는 얘기다.

조건에 맞는 값의 행번호를 작은(SMALL함수)숫자부터 차례차례 정렬시켜준다.

기억하는지 모르겠다. INDEX 함수는 범위에서 행번호만 있다면, 그 값을 가져온다는 것을...

최종적으로 다음과 같이 수식을 입력해보자.

{=INDEX(찾을값,SMALL(IF(조건범위=조건,MATCH(ROW(값범위),ROW(값범위))),1))}
{=INDEX(찾을값,SMALL(IF(조건범위=조건,MATCH(ROW(값범위),ROW(값범위))),2))}

{=IFERROR(INDEX(찾을값,SMALL(IF(조건범위=조건,MATCH(ROW(값범위),ROW(값범위))),1)),"")}
{=IFERROR(INDEX(찾을값,SMALL(IF(조건범위=조건,MATCH(ROW(값범위),ROW(값범위))),2)),"")}

조건에 안맞는 값들은 에러처리가 날테니, IFERROR 함수를 통해 빈칸으로 반환한 모습이다.

최종적으로, 조건에 맞는 모든값을 불러오는것에 성공했다.

---

마무리

당장은 본 포스팅에서 구현한 수식을 이해하는데에 시간을 할애해야겠지만, 내용이 익숙해진다면 개념을 정리해둘 필요가 있다. 머릿속에서 이 개념이 익숙해진다면 다양한 수식을 훨씬 더 자유롭게 구사하게 될 것이다.

이해가 안되는 부분이나, 틀린 개념을 설명했거나, 궁금한게 있다면 댓글 달아주세요