[엑셀] VLOOKUP 대신 INDEX, MATCH 를 사용하면 좋은 4가지 경우

2018/08/28 - [엑셀] - [엑셀] VLOOKUP 대신 INDEX, MATCH 사용하기 에 이어서

VLOOKUP 대신 INDEX, MATCH 를 사용하면 좋은 경우를 살펴보자.

1. 기준 열 왼쪽에 있는 값을 출력할 때

특정 ID에 해당하는 PRICE 는 VLOOKUP으로 쉽게 출력할 수 있다.

그럼 특정 PRICE에 해당하는 ID는?

VLOOKUP 으로는 이 문제를 해결할 수 없다. VLOOKUP은 왼쪽 열을 기준으로 오른쪽에 있는 데이터를 출력하기 때문.

하지만 MATCH - INDEX 라면 어렵잖게 해결할 수 있다.

앞서와 마찬가지로 F4에 있는 값이 D:D 중 어디에 있는지 알아내고

=MATCH(F4, D:D, 0)

(결과는 4가 출력됨)

A열의 4번째라고 알려주면 된다.

=INDEX(A:A, MATCH(F4, D:D, 0))

직접 열을 지정하므로 왼쪽에 있는 데이터도 얼마든지 출력할 수 있다.

2. 두 가지 이상의 조건으로 결과를 출력할 때

2017/08/24 - [엑셀] - [엑셀] 두 가지 조건으로 VLOOKUP 사용하기 에서 이야기했듯

MATCH - INDEX 로는 두 가지 이상의 조건으로 결과를 출력할 수 있다.

가격이 1,940,000원인 에어컨의 id를 출력하기 위해서는 다음과 같이 수식을 구성하면 되겠다.

=INDEX(A:A, MATCH("에어컨1940000", INDEX(B:B&D:D,), 0))

3. 여러 셀의 데이터를 동시에 가져올 때

예를 들어 위와같이 ID 1, 4, 9 에 해당하는 데이터를 전부 불러와야 할 경우

일단 VLOOKUP으로 접근해 보자. 커서가 있는 셀의 수식은 다음과 같이 구성하면 되겠다.

=VLOOKUP(F4, A:B, 2, 0)

이제 오른쪽과 아래까지 모두 채워보자.

잘 된 것 같아 보이지만 자세히 보면 함정이 있다.

PRICE는 모두 1,770,000이며

ID 9 에 해당하는 에어컨의 VER과 PRICE역시 잘못되어 있다.

이런 일이 생기는 이유는 H4셀에서 F2를 눌러보면 알 수 있다.

H4의 수식은 B:B에서 "TV"를 찾아 C열에 해당하는 데이터를 가져오라는 것.

PRODUCT 열에 TV에 해당하는 항목이 하나이므로 여기까지는 어찌 넘어가지만

PRICE 에 해당하는 I4셀의 수식은 C:C에서 "1"을 찾아 D열에 해당하는 데이터를 가져오라고 입력되어 있을 것이다.

모든 PRICE가 1,770,000인 이유다.

에어컨의 경우 역시 H6셀의 수식은 B:B에서 "에어컨" 을 찾아 C열에 해당하는 데이터를 가져오라고 입력되어 있을 것이고

VLOOKUP 은 위에서부터 일치하는 값을 찾으므로 ID = 9 번이 아닌 ID = 3 번의 데이터를 가져오게 된다.

MATCH - INDEX 로 시도해보자.

일단 A열에서 ID에 해당하는 값을 찾는다.

=MATCH(F4, A:A, 0)

(결과는 2가 출력됨)

이제 B열의 2번째라고 알려주면 되겠다.

=INDEX(B:B, MATCH(F4, A:A, 0))

이제 수식을 옆으로 복사하기 전에 두 가지만 고정하자.

1) ID를 판별하는 부분은 항상 A열로 바뀌지 않으므로, 수식의 A:A 부분에서 F4를 눌러 해당 부분을 고정한다.

=INDEX(B:B, MATCH(F4, $A:$A, 0))

2) ID를 입력받는 F4셀 역시 아래로는 움직여도 되나 오른쪽으로 움직여서는 안되므로 해당 부분에서 F4를 세 번 눌러 F부분만 고정한다.

=INDEX(B:B, MATCH($F4, $A:$A, 0))

완성이다. 오른쪽과 아래로 채워 보자.

의도한대로 데이터를 잘 가져오는 것을 볼 수 있다.

4. 수식 깨짐 방지

VLOOKUP의 가장 큰 문제는 원 데이터가 변화할 때 수식이 깨질 우려가 있다는 것이다. 

위 시트의 A열과 B열 사이에 한 열이 추가된다면 어떻게 될까?

수식의 결과값이 제대로 나오지 않는 것을 볼 수 있다.

애초 A:B 였던 범위는 다행히 A:C 로 잘 변화했으나

두 번째 데이터를 가져오라는 "2" 라는 숫자는 자동으로 변하지 않는다. 

따라서 원 시트에 열 추가, 삭제가 일어날 경우 VLOOKUP은 제대로 동작하지 않는다.

MATCH - INDEX 는 다를까?

마찬가지로 A열과 B열 사이에 열을 하나 추가해 보자.

VLOOKUP과는 달리 임의로 입력한 숫자가 없고 모두 셀 주소로 구성되어 있기 때문에

셀의 변화가 자동으로 수식에 적용된다. 따라서 수식이 깨지지 않는다.

특히 네 번째 이유 때문에, 오래 사용해야 할 시트일수록 VLOOKUP 보다는 MATCH - INDEX 를 사용하여 수식을 구성하는 편이 낫다.

VLOOKUP 함수 역시 빠르게 데이터를 참조하는 데는 간편하게 사용할 수 있으므로, 용도에 맞게 사용하면 좋을 것이다.