엑셀 다중 조건에 맞는 특정 범위 값 가져오는 방법(구버전 엑셀)
조건에 해당하는 값을 반환하는 경우 if 함수를 사용하면 되며 특정 범위의 값과 비교하고 일치하는 값을 가져오는 경우 vlookup 함수를 사용하면 된다.
하지만 if 함수는 범위의 값과 비교를 할 수 없으며 vlookup 함수는 하나의 값만 비교할 수 있기 때문에 다중 조건이 필요한 상황에서는 사용이 불가능한데 특정 범위에서 다중 조건에 맞는 값을 가져오고자 한다면 index와 match함수를 사용해 보자.
엑셀 다중 조건에 맞는 특정 범위 값 가져오기
1. 기본적인 수식.
index와 match 두 함수를 사용하여 수식을 만들면 수식을 만드는 사람에 따라 결과는 같지만 다양한 수식이 나오지 않을까 한다.
여기서는 배열 수식을 만들어서 사용해 봤다.
예를 들어 A1, B1, C1에 조건을 위한 비교 값이 있고 E, F, G가 기준 값이 있는 범위 셀이 있는 경우 아래와 같은 배열 수식을 사용할 수 있다.
(배열 수식은 입력 후 [Ctrl + Shift + Enter]을 입력해야 정상 적용 된다.)
| =INDEX(가져올 값, MATCH(1, (비교 값1=범위 값1)*(비교 값2=범위 값2)*(비교 값3=범위 값3)*..., 0)) =INDEX(H:H, MATCH(1, (A1=E:E) * (B1=F:F) * (C1=G:G), 0)) |
수식을 간단하게 설명하면 아래와 같다.
match 함수를 사용하여 비교 값과 범위 값을 비교한다.
이때 범위에서 같은 값을 찾게 되면 조건에 맞는 것으로 판단하고 해당 값의 위치를 반환한다.
다중 조건인 경우 *(곱하기 연산자)로 구분하는데 AND 연산자의 역할을 하는 것이라고 보면 된다.
(*을 통해 조건을 추가하는 방식)
index 함수에서는 match가 반한환 위치 값을 기준으로 가져올 값을 반환한다.
2. 결과는 같지만 다른 수식.
1의 수식은 배열 수식으로 배열 수식을 사용하는 것이 싫다면 아래와 같은 일반 수식으로 사용하는 것도 가능하다.
| =INDEX(가져올 값, MATCH(비교 값1&비교 값2&비교 값3, INDEX(범위 값1&범위 값2&범위 값3&...,), 0)) =INDEX(H:H, MATCH(A1&B1&C1, INDEX(E:E&F:F&G:G,), 0)) |
해당 수식은 아래와 같은 방식으로 작동한다.
가장 안쪽의 index 함수가 비교할 범위 값을 & 연산자로 통합한다.
그다음 MATCH 함수에서 비교 값 역시 & 연산자로 하나로 통합 후 두 값을 비교하여 같은 값을 찾으면 조건에 맞는 값으로 판단하여 값의 위치를 반환한다.
(&를 통해 조건을 추가하는 방식)
마지막 index 함수에서는 match가 반환한 위치 값을 기준으로 가져올 값을 반환한다.
그 외 내용
- 배열 수식을 기본 수식으로 먼저 소개한 이유는 이유는 수식의 효율 때문이다.
값이 얼마 없다면 큰 차이는 없지만 값이 많아지는 경우 일반 수식의 경우 속도가 느려질 수 있다.
때문에 간단하게 사용한다면 어떤 수식을 사용하던 상관없지만 값이 많다면 배열 수식을 사용하는 것이 좋다.
- 엑셀의 함수는 어떻게 조합하고 사용하느냐에 따라서 단순한 역할을 하는 함수가 다양한 역할을 할 수 있다.
이런 경우에도 마찬가지인데 이 글에서는 index와 match 함수를 사용하여 다중 조건에 맞는 특정 범위 값을 가져오는 수식을 작성했지만 같은 함수로 다른 수식을 만들어 같은 결과를 가져올 수도 있으며 vlookup 함수를 어떻게 사용하느냐에 따라 같은 결과를 가져올 수도 있다.
결국은 사용자가 이해하고 사용하기 편한 수식을 만들어서 사용하는 것이 가장 좋지 않을까 한다.
- 글 제목에도 있지만 해당 방법은 구버전 엑셀을 위한 방법이라고 보면 된다.
2019 이상의 엑셀에서는 filter라는 함수 하나만으로도 다중 조건에 맞는 특정 범위의 값을 가져올 수 있기 때문에 이를 사용하는 것이 더 쉽다.
다만 구버전 엑셀을 위한 방법이니 만큼 엑셀의 버전에 상관없이 사용 가능하다는 점에서 범용성은 훨씬 좋다.