데이터 찾을때 VLOOKUP은 한가지 조건만 가능한 반면 두가지 조건까지 적용 가능한 방법이 있습니다. 바로 INDEX와 MATCH 함수를 조합해서 사용하면 두가지 조건에 충족하는 값을 찾아 올수 있습니다. 심지어는 3가지 조건도 가능 하답니다.
이번 포스팅 에서는 INDEXT와 MATCH 함수를 이용해서 다중조건의 값을 찾아와 보겠습니다.
저는 회사에서 ERP에서 Export 된 data의 가공을 다양하게 하고 있는데 대부분 1가지의 조건에 부합하는 데이터를 가져오기 위해서는 VLOOKUP을 사용 하고 있으나 다중조건이 필요할때도 종종 있어서 이럴때는 INDEX와 MATCH 함수 조합으로 해결을 합니다.
처음에는 잘 적응이 안되어 잘 안쓰게 되었는데 한번 익숙해 지니 이제는 아주 유용하게 사용하고 있지요.
1. 엑셀 다중조건 찾기 – INDEX와 MATCH 함수 조합
본론으로 들어가서 바로 적용 예시를 보도록 하겠습니다.
이제 위의 내용을 보시면 품번과 품명이 있고 이어서 중분류 소분류와 규격이 있죠?
우선은 품번에 해당하는 오른쪽 값을 vlookup으로 가져와 보겠습니다.
1.1 한가지 조건일 때의ㅣ VLOOKUP
그런데 보통 회사에서 업무를 하거나 개인적인 엑셀파일로 데이터 가공을 하다보면 접하게 되는 문제점이 있습니다.
바로 한가지 조건이 아니라 여러 조건을 만족하는 값을 가져와야 할때가 분명히 생기게 되죠.
저도 이 방법을 찾아 보기전 까지는 어떻게 해야 할지 모르겠어서 물어보기도 하고 이참에 VBA를 배워볼까 하는 고민아닌 고민도 했었습니다.
하지만 정말 엑셀의 세계는 무궁무진 한듯 방법이 있어라구요.
바로 다음의 생각보다 간단한 함수 조합 방법을 사용하면 되니까요.
1.2 두가지 조건일 때의 INDEX와 MATCH 함수 조합
위는 앞의 VLOOKUP 양식과는 다르게 품번과 중분류 소분류 세가지를 만족하는 값을 찾아 오는 것입니다.
보시면 일단은 INDEX와 MATCH 함수를 조합하여 값을 찾아오게 되고 맨앞의 IFERROR는 잘 아시겠지만 보기싫은 #N/A를 안 보여주게 하는 함수죠.
단, 여기서 주의할 점은 이렇게 조합하여 사용하는 방법은 배열함수라고 하여 수식을 작성후 그냥 엔터를 누르면 안됩니다..!
반드시 다음과 같이 엔터를 눌러야 수식이 적용되어 작동이 됩니다.
수식을 모두 작성하고 Ctrl + Shift + Enter 이렇게 3가지 키를 동시에 한번에 눌러야 합니다.
그렇게 하면 수식 앞에 { 표시가 붙고 뒤에는 } 표시가 붙습니다. 그럼 성공 한 겁니다.
※ 주의사항
저는 통상 일반 수식을 인접셀에 복사하여 붙여넣기로 적용할 때 복사된 셀을 포함하여 범위를 지정후 수식으로 붙여 넣습니다.
그게 간편하더라구요..! 복사된 셀을 빼고 나머지 셀을 범위로 지정하려면 두번 일이 돼서 그렇게 했었는데요.
배열수식은 일반 수식처럼 복사된 자기셀까지 범위지정 해서 복사해서 수식 붙여넣기가 안됩니다.
예를 들자면 드래그 하여 복사된 셀 포함한 범위를 선택후 수식으로 붙여 넣으면 배열의 일부를 변경할 수 없다고 나옵니다.
저도 처음엔 이것땜에 다소 당황 스러웠습니다.
복사 붙여넣기가 안되는 것이 아니라 복사된 셀을 빼고 복사해야 하는것이죠.
(단, 절대주소, 상대주소는 적정히 적용해야 하는것은 아시죠?)
그리고, 또 한가지는 배열수식이 너무 많이 적용되면 엑셀이 느려질수 있으니 이점도 참고 해야 합니다.
2. INDEX 만의 기본 사용법
이미 앞에서 INDEX와 MATCH 함수의 조합 활용법을 이야기 했습니다만 그래도, INDEX함수에 대해서도 알아 보도록 하겠습니다.
2.1 INDEX 함수
INDEX 함수는 기본적으로 설정한 범위의 특정 행과 열이 교차 되는 지점의 값을 가져오는 함수입니다.
쉽게 말하면 좌표를 이용해 지도에서 특정 위치를 찾는 것과 같은 느낌으로 행과 열의 번호를 입력하면 해당 위치의 데이터를 가져옵니다.
이 함수는 VLOOKUP처럼 왼쪽에서 오른쪽으로만 검색하는 제약이 없이 어느 위치라도 가져올수 있는 함수가 되겠습니다.
2.2 INDEX 함수의 기본 사용법
INDEX 함수는 배열과 참조 형태의 두가지를 쓰는데, 보통은 배열 형태를 주로 사용합니다.
배열 형태의 기본 수식은 다음과 같습니다. 간단 합니다.
=INDEX(범위, 행, 열)
범위는 데이터가 있는 셀 영역이고, 행번호는 범위 내에서 몇 번째 행인지를, 열번호는 몇 번째 열인지를 나타냅니다.
열번호는 선택 사항으로 단일 열 범위에서는 생략할 수 있습니다.
가장 간단한 사용법은 하나의 행 범위에서 특정 행의 값을 가져오는 것입니다.
아래를 보시면 그냥 B3:B34 범위에서 3행의 값인 A0003을 가져 왔습니다. 즉, 열은 생략 인 것이죠.
2.3 2차원 범위에서 값 추출하기
이제는 행과 열 모두 지정 합니다.
아래 제가 예시를 작성한 것을 보시면 B3부터 I34까지 데이터가 있고, 3행 4열의 값을 가져오려면 다음과 같이 작성합니다.
그러면 아래처럼 교차되는 셀의 값이 스피커를 가져 옵니다.
=INDEX(B3:I34, 3, 4)
2.4 전체 행이나 열 가져오기
이번에는 행번호나 열번호에 0을 입력해서 전체 범위중 지정된 행이나 열의 산술적 계산을 해 보겠습니다.
단, 이때는 INDEX 단독으로는 안되고 SUM이나 AVERAGE 같은 것으로 묶어 줘야 합니다.
아래를 보시면 INDEX로 찾은 것이 행번호 이죠. 왜냐하면 열번호는 0이니까.
여기에 SUM을 덮어주면 해당 행번호 전체의 SUM을 해서 값을 가져 옵니다. 아래에서는 A0002품번의 분기별 합계가 되겠네요.
3. MATCH 만의 기본 사용법
데이터를 가공 하다 보면 특정 값이 어디에 위치하는지 찾아야 할 때가 많습니다.
MATCH 함수는 지정한 범위에서 원하는 값을 찾아 그 값이 몇 번째 위치에 있는지 번호를 가져 옵니다.
실제 값이 아닌 위치 번호를 알려준다는 점이 핵심입니다.
예를 들어 과일 목록에서 사과가 다섯 번째에 있다면 MATCH 함수는 5를 반환합니다.
그래서, 이 위치 정보는 INDEX 함수에 전달되어 해당 위치의 다른 정보를 가져오는 데 활용 되기 때문에 앞의 INDEX MATCH 함수 조합이 되는 것입니다.
3.1 MATCH 함수의 기본 구문
MATCH 함수의 기본 형태는 다음과 같습니다.
=MATCH(찾을값, 찾을범위, 유형)
찾을값은 검색하려는 데이터이고, 찾을범위는 검색할 셀 영역입니다. 유형은 검색 방식을 결정하는 옵션으로 선택 사항이지만 매우 중요합니다.
일치유형에는 세 가지 값이 있습니다.
1은 근사값 찾기, 0은 정확한 값 찾기, -1은 근사값 역순 찾기입니다.
어느것을 사용할지 모르면 일단은 저는 0을 입력해서 항상 정확한 값을 가져 오려고 합니다.
3.2 정확한 값 찾기 – 유형 0
그러면 유형을 0으로 입력해서 정확히 일치하는 값의 위치를 찾아 보겠습니다.
아래를 보시면 =MATCH(“제품5”,C4:C34,0)라고 N4셀에 입력했구요. 결과값은 C열의 제품5가 있는 셀이 5번째 셀이니 5를 보여 주네요.
3.3 근사값 찾기 옵션 – 유형 1
유형을 1로 설정하면 찾을값보다 작거나 같은 값 중 가장 큰 값의 위치를 보여 줍니다.
이 옵션을 사용하려면 찾을범위가 오름차순으로 정렬되어 있어야 합니다.
예를 들어 점수 구간에 따라 등급을 매기는 경우 유용합니다.
점수 기준이 0, 60, 70, 80, 90으로 설정되어 있고 75점의 등급을 찾는다면 70점 구간인 세 번째를 보여 줍니다.
=MATCH(75, {0;60;70;80;90}, 1)
이 방식은 세금 구간, 배송비 구간, 할인율 구간 등을 찾을 때 매우 유용합니다.
3.4 역순 근사값 찾기 – 유형 -1
일치유형을 -1로 설정하면 찾을값보다 크거나 같은 값 중 가장 작은 값의 위치를 보여 줍니다.
이 경우 찾을범위가 내림차순으로 정렬되어 있어야 합니다.
통상적으로는 1이나 0에 비해 덜 사용되지만, 내림차순으로 정렬된 데이터에서 검색할 때 필요합니다.
=MATCH(75, {100;90;80;70;60}, -1)
이 수식은 내림차순 범위에서 75보다 크거나 같은 값 중 가장 작은 값인 80의 위치인 3을 보여 줍니다.
3.5 텍스트와 와일드카드 검색
MATCH 함수는 텍스트 검색에서 와일드카드 문자를 지원합니다.
물음표는 한 글자를, 별표는 여러 글자를 대신합니다.
예를 들어 이름이 김으로 시작하는 첫 번째 사람을 찾으려면 이렇게 작성합니다.
=MATCH(“김*”, A1:A20, 0)
또는 세 글자 이름에서 가운데 글자가 영인 사람을 찾으려면 이렇게 사용할 수 있습니다.
=MATCH(“?영?”, A1:A20, 0)
와일드카드는 일치유형이 0일 때만 작동하므로 주의가 필요합니다.
3.6 수평 범위에서 검색하기
MATCH 함수는 수직 범위뿐 아니라 수평 범위에서도 사용할 수 있습니다.
월별 데이터가 열로 나열되어 있을 때 특정 월의 열 위치를 찾는 데 유용합니다.
예를 들어 1월부터 12월까지 B1부터 M1에 나열되어 있고, 7월의 위치를 찾으려면 이렇게 작성합니다.
=MATCH(“7월”, B1:M1, 0)
이는 7월이 몇 번째 열에 있는지 알려주어 INDEX 함수와 함께 해당 월의 데이터를 추출하는 데 활용됩니다.
3.7 대소문자 구분과 데이터 형식
MATCH 함수는 기본적으로 대소문자를 구분하지 않습니다. ABC와 abc를 동일하게 취급합니다.
정확한 대소문자 일치를 확인하려면 다른 방법을 사용해야 합니다.
또한 숫자와 텍스트 형식의 숫자는 다르게 인식됩니다.
셀에 숫자 5가 있고 텍스트 “5”를 검색하면 찾지 못할 수 있으므로 데이터 형식을 일치시켜야 합니다.
4. 마치며
지금까지 INDEX와 MATCH 함수의 조합을 알아 보았고 각 함수인 INDEX와 MATCH함수도 보았는데요
이것을 활용할 만한 분야를 본다면 저는 보통 다음과 같은 유형의 작업을 할 때 이용하고 있습닌다.
- 제품 코드로 제품명 찾기 또는 여러 조건의 제품명 찾기
- 날짜로 해당 요일 데이터 찾기
- 직원 번호로 부서 정보 조회하기
저는 MATCH 함수는 값의 위치를 찾는 단순한 기능이지만, INDEX 함수와 조합을 해서 VBA 부럽지 않은
활용을 하고 있습니다. 한편으로 보자면 초보일때인 제가 VLOOKUP으로 씨름을 했다면 지금은 그 한계를 극복하고 훨씬 유연한 데이터 검색이 가능하도록 작성하고 있는 것이죠.
여러분도 실제 업무 데이터로 연습하면서 INDEX-MATCH 조합의 편리함을 확인해 보세요.
※ VLOOKUP함수에 Sheet명을 자유자재로 하는 방법
§ 본 글은 제가 직접 엑셀을 활용 하면서 자주 사용 하게된 함수를 소개하는 목적으로 작성 하였으며 경우에 따라서 잘못 표현이 될수도 있고 엑셀버전에 따라 조금 다르게 적용될수도 있으니 이점 참고 하시기 바랍니다.