엑셀 데이터베이스는 필요에 따라 크게도, 작게도 만들 수 있습니다. 하지만 규모가 방대해지면 데이터를 관리하는 일이 쉽지만은 않죠. 특정 셀에 있는 항목을 찾으려면 수없이 스크롤해야 할 때도 있습니다. VLOOKUP만으로는 부족하다고 느낀다면, 엑셀의 INDEX 함수가 훌륭한 대안이 되어 줄 것입니다.
지금부터 엑셀 INDEX 함수를 활용해 원하는 데이터를 바로 찾는 방법을 소개합니다.
참고: 이 가이드의 스크린샷은 Excel 365 기준이지만, Excel 2019 및 Excel 2016에서도 동일하게 적용할 수 있습니다. 다만 버전에 따라 UI가 약간씩 다를 수 있습니다.
엑셀 INDEX 함수란 무엇인가?
INDEX 함수는 엑셀을 비롯한 다양한 데이터베이스 도구에서 사용되는 수식으로, 수식에 입력한 위치 정보를 기준으로 목록이나 표에서 특정 값을 가져옵니다. 일반적으로 다음과 같은 형식으로 표시됩니다.
=INDEX(array, row_number, column_number)
이 수식은 INDEX 함수를 지정한 뒤, 데이터를 가져오는 데 필요한 인수를 전달하는 구조입니다. 먼저 데이터 범위(또는 미리 정의해 둔 이름 범위)를 지정하고, 그다음 배열 기준의 상대 행 번호, 상대 열 번호를 차례로 입력합니다.
여기서 중요한 점은 행과 열 번호가 지정한 범위 내에서의 위치라는 것입니다. 예를 들어 데이터 범위의 두 번째 행에 있는 값을 가져오고 싶다면, 전체 데이터베이스에서 두 번째 행이 아니더라도 행 번호에 2를 입력하면 됩니다. 열 번호도 마찬가지입니다.
엑셀에서 INDEX 함수 사용하는 방법
INDEX 수식은 미리 정의된 데이터 범위에서 원하는 정보를 찾아내는 데 매우 유용한 도구입니다. 이번 예시에서는 문구류와 반려동물 간식을 판매하는 가상의 판매업체 주문 목록을 사용해 보겠습니다. 주문 보고서에는 주문 번호, 제품명, 개별 가격, 판매 수량이 포함되어 있습니다.
작업할 엑셀 데이터베이스를 열거나, 아래 예시와 같은 데이터를 직접 만들어 따라 해 보세요.
INDEX 결과가 표시될 셀을 선택합니다. 첫 번째 예시에서는 Dinosaur Treats(공룡 간식)의 주문 번호를 찾아 보겠습니다. 해당 데이터가 A7 셀에 있다는 것을 알고 있으므로, 다음과 같은 형식으로 INDEX 함수를 입력합니다.
=INDEX(A2:D7,6,1)
이 수식은 A2부터 D7까지의 셀 범위 안에서, 해당 범위의 여섯 번째 행(실제 7행)이자 첫 번째 열(A열)에 있는 값을 찾아 결과값 32321을 출력합니다.
이번에는 Staples(스테이플러)의 주문 수량을 알고 싶다면 다음 수식을 입력합니다.
=INDEX(A2:D7,4,4)
그러면 결과값 15가 출력됩니다.
또한 행(Row)과 열(Column) 입력값에 다른 셀을 참조하도록 설정하면, 원래 수식을 수정하지 않고도 INDEX 결과를 동적으로 변경할 수 있습니다. 예시는 다음과 같습니다.
여기서 유일한 차이점은 INDEX 수식의 행과 열 값이 셀 참조, 즉 F2와 G2로 입력되었다는 점입니다. 해당 셀의 내용이 바뀌면 INDEX 결과값도 자동으로 함께 변경됩니다.
팁: 배열(array) 자리에 이름 범위(named range)를 사용할 수도 있습니다.
참조(Reference)와 함께 INDEX 함수 사용하기
INDEX 수식은 배열 대신 참조(reference)와 함께 사용할 수도 있습니다. 이 방식을 활용하면 여러 개의 범위, 즉 여러 배열을 정의해 두고 그중 원하는 데이터를 가져올 수 있습니다. 함수 입력 방식은 거의 동일하지만, 영역 번호(area number)라는 인수가 하나 더 필요합니다. 형식은 다음과 같습니다.
=INDEX((reference), row_number, column_number, area_number)
앞서 사용한 예시 데이터베이스를 거의 동일한 방식으로 활용해 참조형 INDEX 함수의 기능을 살펴보겠습니다. 다만 이번에는 범위 안에 세 개의 개별 배열을 정의하고, 두 번째 괄호로 묶어 줍니다.
작업할 엑셀 데이터베이스를 열거나, 빈 시트에 동일한 데이터를 입력해 예시를 따라 해 보세요.
INDEX 결과가 표시될 셀을 선택합니다. 이번 예시에서도 Dinosaur Treats의 주문 번호를 찾지만, 이 데이터는 범위 내 세 번째 배열에 속해 있습니다. 따라서 함수는 다음과 같은 형식으로 작성합니다.
=INDEX((A2:D3,A4:D5,A6:D7),2,1,3)
이 수식은 데이터베이스를 두 행씩 세 개의 범위로 나눈 뒤, 세 번째 배열의 두 번째 행, 첫 번째 열에 있는 값을 찾습니다. 그 결과 Dinosaur Treats의 주문 번호가 출력됩니다.
