엑셀에서 가장 유용하게 사용되는 대표 함수 중 하나로 Vlookup 함수가 있지만, 찾으려는 값이 반드시 기준 값의 오른쪽에 위치해야 한다는 한계점이 있습니다. 이를 보완할 수 있는 함수가 바로 Match 함수와 Index 함수인데요, 두 가지 함수를 어떻게 조합해서 활용할 수 있는지 좀 더 자세히 살펴보겠습니다.
Match 함수
=MATCH(Lookup_value, Lookup_array, Match_type)
- Lookup_value : 찾으려는 값
- Lookup_array : 특정 범위
- Match_type : 완전히 일치하는 값을 찾는 경우 0, 근삿값을 찾는 경우 1을 입력
Match 함수란 특정 범위에서 찾으려는 값의 행(Row) 번호나 열(Column) 번호를 구하는 함수입니다. 그러므로 Match 함수의 결과 값은 무조건 숫자라는 특징이 있습니다.
예를 들어보겠습니다. 아래 꽃꽂이 수업 일자와 수강료가 적힌 표가 있습니다. 이때, 총 6개의 수업 중에서 "화기 제작하기" 수업이 몇 번째 수업인지 행 번호를 확인하려면 Match 함수를 활용할 수 있습니다. F6 셀에 있는 "화기 제작하기"를 C3에서 C8의 범위, 즉 아래 화면 분홍색 범위인 수업 내용 열에서 완전히 일치하는 값을 찾도록 =MATCH(F6, C3:C8, 0)이라는 수식을 만들 수 있습니다. 결과 값은 G6셀에 적힌 것처럼 4입니다. 앞서 말씀드린 것처럼 결과 값은 숫자로 나타납니다.
데이터 유효성 검사
엑셀에는 데이터 유효성 검사라는 기능이 있습니다. 드롭다운 형식으로 목록을 나타내 주는 기능을 하는데요, 데이터 탭에서 데이터 유효성 검사를 클릭하시면 데이터 유효성 창이 뜹니다. 여기에서 제한 대상을 목록으로 설정하고 드롭다운에 나타낼 목록의 범위를 아래 화면 분홍색처럼 선택해주면 아래 화면 오른쪽 빨간 칸처럼 드롭다운 형식의 목록이 만들어집니다.
앞서 MATCH 함수 예제에서 "화기 제작하기"라는 데이터를 찾기 위해, 찾는 값을 C6이 아닌 F6으로 지정한 이유는 바로 이 데이터 유효성 검사 기능을 활용해서 목록에서 "화기 제작하기"가 아닌 다른 값, 예를 들어 "식물 분갈이"를 선택하더라도 F6셀에는 변경된 값인 "식물 분갈이"를 찾는 값으로 반영하기 때문이랍니다. 이때 결과 값은 1이 나오겠네요.
Index 함수
=INDEX(Array, Row_num, Column_num)
- Array : 특정 범위
- Row_num : 특정 범위에서 찾을 행 번호
- Column_num : 특정 범위에서 찾을 열 번호
Index 함수란 특정 범위에서 특정 행(Row)과 열(Column)에 해당하는 셀(Cell)의 값을 구하는 함수입니다. 즉, Match 함수에서 결과 값으로 구한 숫자를 특정 행과 열의 값으로 넣어주면 Index 함수와 Match 함수를 결합해서 원하는 값을 찾을 수 있다는 뜻입니다.
예를 들어보겠습니다. Index 함수와 Match 함수를 조합해서, 아래 화면 오른쪽 초록색 표에서 G3과 H3에 들어갈 "화기 제작하기" 수업의 일자와 수강료를 구해보겠습니다. B3에서 D8의 범위에서 4행 1열의 값을 찾으라고 =INDEX(B3:D8, 4, 1)이라는 수식을 만들면 "화기 제작하기" 수업의 일자를 구할 수 있겠죠? 정답은 10월 25일입니다.
Array
우리가 Index 함수를 활용해서 구하고자 하는 값은 "수업 내용"에 따른 "일자"와 "수강료"입니다. 그러므로 원하는 데이터가 있는 모든 범위를 Array에서 지정해줘야 하는데, 이때 특정 범위는 아래 화면 주황색으로 표기된 B3에서 D8까지의 범위가 됩니다. 이 범위에 수업 내용, 일자, 수강료 데이터가 모두 들어있으니까요.
Row_num
위에서 지정한 Array 범위에서 구하려는 행의 번호를 넣는 자리입니다. Array, Row_num, Column_num 중에서 유일하게 변하는 데이터인데요, "화기 제작하기"가 들어갈 수도 있고 "식물 분갈이"가 들어갈 수도 있는 자리이기 때문입니다. 하지만 매번 변하는 데이터의 행 번호를 구할 수는 없기 때문에 Match 함수의 활용이 필요합니다. 앞서 우리는 MATCH(F6, C3:C8, 0)이라는 수식을 통해서 "화기 제작하기"가 4번째 행에 있는 수업이라는 사실을 알았습니다. 그럼 Index 함수에서도 =INDEX(B3:D8, 4, 1)이라는 수식에서 4 대신 MATCH(F6, C3:C8, 0)을 넣어줄 수 있다는 사실도 아셨을 겁니다.
※ 물론 Match 함수와 Index 함수의 예제를 위해 아래 화면 오른쪽에 초록색 표를 2개 만들었기 때문에, 이미 Match 함수 예제에서 사용한 아래 있는 초록색 표는 배제하고, 위에 있는 초록색 표를 활용한다면 MATCH(F6, C3:C8, 0)의 수식에서 F6 대신에 F3을 넣을 수 있습니다.
Column_num
Index 함수에서 마지막 3번째 자리에 들어가는 Column_num에 1을 넣어준 이유는 우리가 찾는 값인 "일자"가 Array 범위에서 가장 첫 번째 열에 있는 데이터이기 때문입니다. 마찬가지로 "수강료"를 찾는 경우에는 해당 데이터가 Array 범위에서 3번째 열에 있으니 1이 아닌 3, 즉 =INDEX(B3:D8, MATCH(F3, C3:C8,0), 3)이라는 수식을 만들 수 있습니다. 이때 Column_num 자리에 Match 함수를 활용하지 않고 직접 열 번호 숫자를 넣어준 이유는 해당 자리는 변하는 데이터가 아닌 고정된 데이터이기 때문이죠.
Match 함수와 Index 함수를 처음 사용하신다면 혼동되실 수 있습니다. 특히 Match는 결과 값으로 숫자를 반환하기 때문에 이걸 어디에 사용하지?라는 의문이 드실 수 있습니다. 하지만 Index 함수와 함께 활용한다면 Vlookup 함수 못지않은 효과가 있기 때문에 활용만 잘하실 수 있다면 업무에서도 큰 도움이 된답니다.
그럼 오늘도 좋은 하루 되세요 :)
