업무용 스프레드시트를 다루다 보면 방대한 데이터 속에서 원하는 정보를 찾아야 할 때가 있습니다.
VLOOKUP 함수로 해결하려 했지만, 별 소용이 없었던 경험, 아마 누구나 해보셨을 것으로 생각하는데요.
특히 검색 기준이 표의 오른쪽에 있거나, 여러 조건을 동시에 만족해야 하는 경우엔 다중조건이 필요해질 수 있습니다.
이럴 때 INDEX와 MATCH 함수를 조합하면 훨씬 유연하고 강력한 검색이 가능해집니다.
다중 조건 검색은 복잡한 데이터 분석에서 필수적인 기술인 만큼, 아래의 검색 방법을 익혀 보시기 바랍니다.
INDEX 뜻 : 좌표로 값을 찾는 방법
사진 출처 (support google)
INDEX 함수는 지정한 범위에서 특정 행과 열을 가져오는 함수로, ‘지정’이라는 뜻을 갖고 있습니다.
격자 위의 특정 칸을 집어내듯, 행 번호와 열 번호를 입력하면 해당 위치의 데이터를 반환하는데요.
기본 구문은 `=INDEX(범위, 행번호, [열번호])`로 구성됩니다.
예를 들어 A1:C10 범위에서 3번째 행, 2번째 열의 값을 찾으려면 `=INDEX(A1:C10, 3, 2)`라고 입력하면 됩니다.
열번호를 0으로 설정하면 해당 행의 모든 값을 배열로 가져올 수 있으며, 반대로 행번호를 0으로 하면 특정 열 전체가 반환됩니다. 이는 매우 단순해 보이지만 MATCH 함수와 만나면 엄청난 시너지를 발휘합니다.
MATCH 함수, 위치를 찾아주는 탐색 기능
MATCH 함수는 범위 내에서 특정 값이 몇 번째에 있는지 알려줍니다. 값 자체가 아닌 ‘위치’를 반환한다는 점이 핵심인데요.
기본 구문은 `=MATCH(검색할_키, 범위, 검색_유형)`으로 이뤄져 있으며, 검색_유형에는 세 가지 옵션이 있습니다.
0을 입력하면 정확히 일치하는 값만 찾으며, 1은 찾는 값보다 작거나 같은 최댓값을, -1은 크거나 같은 최솟값을 찾아냅니다. 실무에서는 정확한 일치를 찾는 0 옵션을 가장 많이 활용하게 됩니다. 예를 들어 `=MATCH(“사과”, A1:A10, 0)`은 A1:A10 범위에서 “사과”가 몇 번째 위치하는지 알려줍니다.
INDEX 함수·MATCH 조합, VLOOKUP보다 나은 점
사진 출처 (support google)
두 함수를 결합하면 VLOOKUP의 한계를 극복할 수 있는데,
기본 공식은 `=INDEX(출력 범위, MATCH(찾을 값, 검색 범위, 0))`입니다.
MATCH가 찾은 위치 번호를 INDEX가 받아서 실제 값을 반환하는 구조인데요. VLOOKUP은 검색 기준이 반드시 표의 왼쪽에 있어야 하지만, INDEX·MATCH는 위치에 제약이 없습니다. 열을 추가하거나 삭제해도 공식이 깨지지 않는다는 것도 장점입니다.
함수 활용의 예제
학생 명단에서 이름으로 점수를 찾는 상황을 예로 들어보겠습니다. `=INDEX(C2:C20, MATCH(“김철수”, A2:A20, 0))`라고 입력하면 A 열에서 “김철수”의 위치를 찾고, 같은 행의 C 열 값을 가져옵니다.
다중조건 검색, 실무의 효율성을 높이는 방법
사진 출처 (xlworks)
실무에서는 하나의 조건만으로 부족한 경우가 많습니다. 제품 코드와 생산 연도를 동시에 만족하는 단가를 찾아야 하거나, 지역과 기간을 함께 고려해야 하는 상황이 생기죠.
이때 배열 수식을 활용한 다중 조건 검색이 필요한데, 핵심 원리는 각 조건을 곱셈(*) 연산으로 연결하는 것입니다.
`(조건1=범위1)*(조건2=범위2)`처럼 조건들을 곱하면, 각 조건은 TRUE 또는 FALSE를 반환합니다.
표는 TRUE를 1로, FALSE를 0으로 자동 변환하는데, 모든 조건이 TRUE일 때만 1×1=1이 되고, 하나라도 FALSE면 결과는 0이 됩니다. 결국 모든 조건을 만족하는 행에서만 1이라는 값이 나타나게 되는 겁니다.
다중조건 공식 활용의 실전 예제
영상 출처 (오빠두 엑셀)
제품 데이터에서 제품 코드가 “n001″이고 생산 연도가 “2021”인 단가를 찾아야 한다고 가정해 봅시다.
이때 공식은 `=INDEX(C2:C10, MATCH(1, (A2:A10=”n001″)*(B2:B10=2021), 0))`입니다.
먼저 (A2:A10=”n001″)은 제품 코드 열의 각 셀을 “n001″과 비교하며, 일치하면 TRUE(1), 불일치하면 FALSE(0)가 됩니다.
마찬가지로 (B2:B10=”2021″)은 생산 연도 열을 검사하는데, 두 조건을 곱하면 {0;0;1;0;0;0;0;0;0}와 같은 배열이 만들어집니다.
여기서 1은 두 조건을 모두 만족하는 3번째 행을 의미합니다. 최종적으로 INDEX(C2:C10, 3)이 실행되어 C열의 3번째 값을 가져오는 것이죠.
3개 이상의 조건을 추가하는 방법
3개 이상의 조건도 같은 방식으로 추가할 수 있는데, `*(조건3=범위3)*(조건4=범위4)`처럼 계속 연결하면 됩니다. 이를 활용해 더 여러 조건을 충족하는 값을 정확한 값을 골라낼 수 있습니다.
계산 오류를 피하는 팁
범위를 지정할 때는 반드시 절대참조($)를 활용해야 합니다.
`$A$2:$A$10`처럼 달러 기호를 붙이지 않으면 공식을 복사할 때 범위가 틀어집니다.
또 조건 범위와 출력 범위의 행 개수는 반드시 일치해야 합니다.
N/A 오류가 나타난다면 조건에 맞는 데이터가 없다는 뜻이니 조건 값이나 범위를 다시 한번 점검해 보시길 바랍니다.
VALUE! 오류는 보통 범위 크기가 서로 다를 때 발생하는데, IFERROR 함수로 오류를 깔끔하게 처리할 수 있습니다.
`=IFERROR(INDEX(…), “검색 결과 없음”)`처럼 감싸주면 오류 대신 원하는 메시지가 표시됩니다.
INDEX 함수 실무 활용과 확장 가능성
INDEX MATCH 함수 다중 조건 검색은 복잡한 데이터 분석에 매우 자주 사용되는 함수입니다.
재고 관리 시스템에서 품목과 창고 위치로 수량을 찾거나, 인사 데이터에서 부서와 직급으로 급여를 조회하는 등 활용처가 무궁무진합니다.
QUERY 함수가 더 적합한 경우
영상 출처(오빠두 엑셀))
다만 조건이 매우 복잡하거나 데이터양이 방대하다면 QUERY 함수가 더 효율적일 수 있습니다.
QUERY는 SQL과 유사한 문법으로 더 직관적인 조건 설정이 가능하거든요.
반면 INDEX·MATCH는 정확한 위치 기반 검색이 필요하거나, 동적인 범위 참조가 필요할 때 강력합니다.
FILTER나 ARRAYFORMULA 같은 배열 함수와 조합하면 더욱 강력한 도구가 됩니다.
OR 조건이 필요하다면 곱셈(*) 대신 덧셈(+) 연산자를 사용하면 됩니다. `(조건1=범위1)+(조건2=범위2)`처럼 작성하면 둘 중 하나만 만족해도 1 이상의 값이 나옵니다.
글을 마치며
데이터의 복잡도가 높아질수록 이런 INDEX 함수 뜻을 안다면 검색 기법은 더 유용하게 활용될 수 있습니다.
처음엔 낯설고 어렵게 느껴질 수 있지만, 몇 번 연습하다 보면 자연스럽게 손에 익게 됩니다.
지금 당장 업무 데이터를 열고 오늘 알려드린 공식을 손에 익혀보세요.
여러분의 스프레드시트 실력과 업무 능력이 한 단계 도약하는 계기가 될 겁니다.
