이전 글에서는 Vlookup함수의 결과값이 여러개일때 조건을 만족하는 고유 결과값을 반환하는 수식을 작성해 보았습니다.
이번 글에서는 Vlookup함수 수식으로 반환할 결과값의 조건이 여러개일때 적용할 수 있는 함수와 수식을 작성해 보도록 하겠습니다.
vlookup 다중조건.
위와같이 A열에는 학년, B열에는 이름, C열에는 점수를 입력한 데이터에서 E열에 학년, F열에 이름을 입력하면 E열과 F열에 입력된 데이터의 조건을 만족하는 C열의 점수를 결과값으로 반환하고자 합니다.
만약 F열에 입력한 이름만으로 C열의 점수를 결과값으로 반환하고자 한다면 =VLOOKUP($F2,$B$2:$C$7,2,0)와 같이 수식을 작성할 수 있습니다.
B2:C7셀 범위중에서 B열에서는 F2셀의 데이터값과 일치하는 조건을 찾고 셀범위에서 2번째 열인 C열의 값을 결과값으로 반환하라는것입니다.
F2셀에 “홍길동”을 입력하면 G2셀의 결과값은 “100”이라는 결과값을 반환하게 됩니다.
그러나 E2셀 “1학년”, F2셀 “홍길동”을 입력후 E2셀과 F2셀의 두가지 조건을 모두 만족하는 C열의 데이터값을 결과값으로 가져오기 위해 수식을 작성한다면 vlookup함수의 찾을 조건은 1가지 조건만을 인수로 작성할 수 있기 때문에 수식을 작성 할 수 없게 됩니다.
이렇게 Vlookup함수의 다중조건을 결과값으로 반환하기 위한 함수는 Lookup함수 또는 Index, Match함수로 수식을 작성해야 다중조건을 만족하는 결과값을 반환 할 수 있습니다.
Lookup함수와 Index, Match함수로 Vlookup함수 다중 조건을 만족하는 수식은 다음과 같이 각각 작성하여 적용할 수 있습니다.
Lookup함수 수식.
| =LOOKUP(1,1/(($A$2:$A$7=$E2)*($B$2:$B$7=$F2)),$C$2:$C$7) |
위와 같은 수식은 배열의 논리값을 찾는 개념으로 (($A$2:$A$7=$E2)*($B$2:$B$7=$F2)),$C$2:$C$7) 부분의 수식을 1로 나누어 1의값을 찾기 위한 계산식으로 Lookup함수의 배열형, 벡터형 수식 구문중 벡터형 수식 구문으로 작성한 수식입니다.
배열과 벡터에 대한 이해를 위한 설명은 이번 글에서는 생략하도록 하고, Lookup함수 수식 구문을 벡터형으로 작성하면 다중 조건을 만족하는 vlookup함수의 대체 함수로 사용이 가능하다는 정도만 기억해 주셔도 좋겠습니다.
만약 조건이 추가 된다면 (($A$2:$A$7=$E2)*($B$2:$B$7=$F2))이 부분의 수식에서 참조범위와 찾을값의 조건을 추가 해줄 수 있습니다.
Index, Match함수 수식.
vlookup함수 다중 조건 수식을 대체 하는 함수는 Lookup함수 뿐만 아니라 index함수, match함수의 조합으로도 수식을 작성하여 활용할 수 있습니다.
| {=INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=$E$2)*($B$2:$B$7=$F$2),0))} |
위의 수식을 G2셀에 작성하는 경우 E2셀의 조건은 A2:A7 범위에서 정확히 일치 하는 값을 찾고, F2셀의 조건은 B2:B7셀 범위에서 정확히 일치 하는 값을 찾아 C2:C7셀 범위에서 해당하는 데이터값을 결과값으로 반환할 수 있습니다.
해당 수식은 배열 수식으로 =INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=$E$2)*($B$2:$B$7=$F$2),0)) 수식을 작성한 후에 Ctrl+Shift+Enter키로 수식을 완성해 주면 수식의 맨 앞,뒷 부분에 중괄호({,})가 생성되어 배열 수식으로 작성해 주어야 합니다.
그러나 엑셀 버전이 최신버전일수록 이러한 배열수식의 작동을 일일이 지정해 주지 않더라도 자동으로 배열수식이 작동하기 때문에 엑셀 버전이 상위 버전이라면 =INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=$E$2)*($B$2:$B$7=$F$2),0)) 여기까지 수식을 작성하고 Enter키로 수식을 완성해도 계산 수식이 결과값을 반환하는데 문제는 없겠습니다.
필자의 경우에는 Excel2021 버전을 사용중으로 굳이 배열수식으로 Ctrl+Shift+Enter키로 작성해 주지 않아도 문제가 되지는 않지만 Excel97버전부터 사용해오던터라 배열수식은 Ctrl+Shift+Enter키로 작성해 주는 습관이 있지만 간혹 배열수식을 일반 수식으로 작성하더라도 문제가 발생하지는 않습니다.
Excel 2019 버전 이하에서는 이러한 기능이 작동하지 않는것으로 알려져 있어 엑셀 버전이 이보다 하위버전이라면 기억하고 있어야할 부분이 되겠습니다.
또한 해당 수식의 특이점으로는 index함수의 Row인수로 작성된 Match함수를 배열로 작성하면서 일반적인 수식 구문과는 차이점을 보이고 있다는점입니다.
index함수의 기본 수식 구문은 =index(결과값을 반환할 참조 범위, 참조 행(Row) 범위, 참조 열(Column) 범위)로 작성하는데 예제에서는 참조 행에서 찾아야할 조건이 2개 이므로 참조 행(Row) 범위=Row_num 인수를 Match함수의 배열로 작성하여 결과값을 찾도록 작성했다는것입니다.
글을 마치며…[행아아빠 생각]
엑셀 수식을 활용하는 분이라면 Vlookup함수 정도는 기본적으로 활용하고 계실것입니다.
그러나 vlookup함수의 한계점으로 찾을 조건을 1개 이상 적용할수 없다는점과 열방향이 조건을 찾는 범위가 반듯이 첫번째 범위로 지정이 되어야 한다는것으로 동일한 수식으로 결과값을 가져오고자 할때 일부셀에서는 찾고자 하는 범위가 지정한 범위의 첫번째 열이 아닌 경우 오류값을 반환하게 되어 참조 범위를 수정해 줘야 하는 경우도 발생할 수 있습니다.
이럴때 본문에서 설명하고 있는 Lookup함수 또는 Index Match함수 조합으로 수식을 작성하는 경우 참조범위의 순서, 1개이상의 조건을 만족하는 결과값을 반환할 수 있기 때문에 vlookup함수보다 활용도가 높다고 하겠습니다.
