VLOOKUP 다중조건 검색

사용된 공식

[! 주의] 해당 공식은 배열수식이므로 Ctrl + Shift + Enter 키로 입력해야 합니다.
그렇지 않으면 #VALUE! 오류가 반환됩니다.

인수 설명

관련 강의

INDEX/MATCH 함수의 기본 동작원리가 궁금하다면 아래 영상강의를 먼저 확인해보세요.

공식에 대한 설명

INDEX 함수와 MATCH 함수를 응용한 공식으로, VLOOKUP 함수에서는 처리하기 어려운 '여러 조건을 동시에 만족하는 값 검색'을 구현합니다. INDEX/MATCH 공식의 기초 사용법은 관련 포스트에서 함께 확인하세요.

해당 공식은 각 조건범위가 모두 세로 방향이거나 모두 가로 방향일 때 사용합니다. 예를 들어 조건범위1은 가로 방향이고 조건범위2는 세로 방향처럼 두 범위의 방향이 서로 다를 경우에는 적용할 수 없습니다. 이런 경우에는 INDEX 함수의 세 번째 인수까지 활용하여 공식을 작성합니다. 자세한 내용은 INDEX/MATCH 함수의 고급 사용법을 정리한 관련 포스트를 참고합니다.

예제파일에 사용된 공식

공식의 동작 원리

1. 먼저 여러 조건범위에서 모든 조건을 동시에 만족하는 행의 위치를 찾습니다. 이때 MATCH 함수를 사용합니다.
   조건1 : $B$7:$B$18 = '품목'
   조건2 : $C$7:$C$18 = '주문처'
   MATCH 함수는 조건1과 조건2를 동시에 만족하는 행의 번호를 반환합니다.
2. 이어서 MATCH 함수에 입력되는 두 배열의 곱셈 과정을 살펴봅니다.
   = {저지방우유, 하루우유, 하루우유, 저지방우유, 딸기맛우유, ...} '// B7:B18 범위에 입력된 품목 배열입니다.
   = {오빠두식당, 오빠두식당, 엑셀천국, 엑셀천국, 엑셀천국, ...} '// C7:C18 범위에 입력된 주문처 배열입니다.
   '// 검색 조건으로 '하루우유'와 '오빠두식당'을 사용하면, 두 배열의 곱셈은 다음과 같이 계산됩니다.
   = {품목범위=하루우유} * {주문처범위=오빠두식당}
   = {0, 1, 1, 0, 0, ..} * {1, 1, 0, 0, 0, ..}
   = {0, 1, 0, 0, 0, ..} '// 두 배열을 같은 위치끼리 곱한 결과입니다.
3. 이렇게 계산된 두 배열의 곱셈 결과를 MATCH 함수의 검색 범위로 사용합니다.
   = MATCH(1, ('품목범위'='품목')*('주문처범위'='주문처'), 0)
   = MATCH(1, {0, 1, 0, 0, 0, ...}, 0)
   = 2 '// MATCH 함수는 배열에서 '1'의 위치를 찾습니다. 두 번째 항목이 '1'이므로 결과값 2를 반환합니다.
4. 이제 INDEX 함수의 동작 원리를 살펴봅니다. INDEX 함수는 지정한 범위에서 '해당 순번'에 위치한 값을 반환합니다.
   = INDEX($D$7:$D$18, 2) '// MATCH 함수가 반환한 2가 INDEX 함수의 위치 인수로 입력됩니다.
   = INDEX({1200, 1350, 1340, 1300, ...}, 2)
   = 1350 '// 범위에서 두 번째에 위치한 1350이 결과로 반환됩니다.
5. 지금까지의 동작 흐름을 정리하면 다음과 같습니다.
   첫째, 다중 조건('하루우유'와 '오빠두식당')이 동시에 일치하는 행은 출력범위 기준 2번째 행입니다. (MATCH 함수)
   둘째, 따라서 출력범위의 2번째에 위치한 값 1350이 최종 결과로 반환됩니다. (INDEX 함수)

[링크] MS 공식 홈페이지 — VLOOKUP 함수 사용법 상세 설명