모두
모두콘텐츠
얼마 전 정비 부품 목록에서 브레이크 패드 가격을 찾는데, 품목이 늘어날수록 눈으로 행을 따라가는 일이 꽤 번거롭더군요. 부품 코드가 적힌 셀 하나만 바꾸면 가격이 바로 표시되도록 만들 때 엑셀 INDEX MATCH 함수가 유용합니다. 이름은 낯설어도 역할을 나눠 보면 어렵지 않습니다. MATCH가 원하는 부품의 위치를 찾고, INDEX가 그 위치에 있는 가격을 가져옵니다.
INDEX와 MATCH가 각각 하는 일
INDEX 함수는 지정한 범위에서 몇 번째 행, 몇 번째 열의 값을 가져옵니다. 예를 들어 =INDEX(B2:B6,3)은 B2:B6 범위의 세 번째 값인 B4의 내용을 돌려줍니다. 이때 숫자 3을 직접 입력하면 목록의 위치가 바뀔 때마다 수식을 고쳐야 합니다. 그 위치를 자동으로 계산하는 함수가 MATCH입니다.
MATCH는 찾을 값이 범위의 몇 번째에 있는지 알려줍니다. =MATCH(E2,A2:A6,0)이라면 E2의 값이 A2:A6에서 몇 번째인지 찾습니다. 마지막의 0은 정확히 같은 값을 찾겠다는 뜻입니다. 부품 코드처럼 한 글자 차이로 다른 제품이 되는 데이터에서는 이 값을 빠뜨리지 않는 편이 안전합니다. 두 함수를 합치면 코드가 놓인 행을 찾고, 같은 행의 가격을 읽을 수 있습니다.
부품 코드로 가격을 가져오는 기본 공식
A2:A6에 부품 코드, B2:B6에 판매 가격이 있고 E2에 찾을 코드를 입력한다고 가정해 보죠. A2가 OP-101이고 B2가 12,000원, A3가 BR-202이고 B3가 38,000원이라면 E2에 BR-202를 적었을 때 원하는 값은 38,000원입니다. 가격을 표시할 셀에 =INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))을 입력하면 됩니다.
수식을 안쪽부터 읽으면 이해가 쉽습니다. MATCH는 BR-202가 코드 범위의 두 번째에 있다고 알려주고, INDEX는 가격 범위의 두 번째 값인 38,000을 반환합니다. 가격과 코드 범위의 시작 행과 끝 행은 서로 맞춰야 합니다. 코드 범위가 2행에서 시작하는데 가격 범위가 3행에서 시작하면 엉뚱한 가격이 나올 수 있습니다.
범위에 붙은 $ 기호는 수식을 다른 셀로 복사해도 참조 범위를 고정합니다. 반면 E2에는 $를 붙이지 않았으므로 아래로 복사하면 E3, E4로 바뀝니다. 여러 부품의 가격을 한꺼번에 찾을 때 편리한 구성입니다. 표시된 38000에 천 단위 쉼표와 원 표시를 붙이고 싶다면 수식보다 셀의 숫자 표시 형식을 조정하면 됩니다.
가격과 재고를 같은 표에서 골라 찾기
실무에서는 가격만 필요한 경우가 드뭅니다. 같은 부품의 재고 수량이나 입고일도 확인하죠. A2:A6에 부품 코드가 있고, B1에는 가격, C1에는 재고, D1에는 입고일이라는 제목이 있다고 가정해 보겠습니다. E2에는 부품 코드, F2에는 가져올 항목 이름을 입력합니다. 그러면 =INDEX($B$2:$D$6,MATCH(E2,$A$2:$A$6,0),MATCH(F2,$B$1:$D$1,0))으로 행과 열을 함께 찾을 수 있습니다.
첫 번째 MATCH는 해당 부품이 몇 번째 행인지, 두 번째 MATCH는 선택한 항목이 몇 번째 열인지 계산합니다. F2가 재고라면 B1:D1 중 두 번째 열을 고르므로 해당 부품의 재고 수량이 표시됩니다. 같은 수식을 유지한 채 F2를 가격으로 바꾸면 가격이 나옵니다. 차종별 정비 이력표에서 행에는 차량 번호, 열에는 주행거리와 최근 교환일을 둔 경우에도 원리는 같습니다.
값이 안 나오거나 다르게 나올 때
가장 흔한 오류는 찾는 값이 목록에 없어서 생기는 #N/A입니다. 먼저 E2의 코드와 원본 코드에 공백이나 오타가 없는지 확인하세요. 숫자로 저장한 코드와 문자로 저장한 코드도 겉보기는 같지만 일치하지 않을 수 있습니다. 예를 들어 00123처럼 앞의 0이 의미 있는 부품 코드는 양쪽 데이터를 같은 문자 형식으로 관리하는 편이 좋습니다.
값은 나오는데 다른 부품의 가격이라면 두 범위의 행 위치를 비교해야 합니다. 또 MATCH는 같은 코드가 여러 번 나오면 첫 번째로 발견한 위치를 반환합니다. 같은 부품 코드로 규격이나 적용 차종이 나뉜다면 코드만으로는 한 제품을 특정할 수 없습니다. 목록에서 식별 기준을 더 세분화하거나, 차종과 규격을 포함한 고유 코드를 만들어야 정확한 값을 얻습니다.
자동차 부품표는 코드 한 자리나 적용 차종 하나가 달라도 가격과 재고가 크게 달라집니다. 수식 자체보다 먼저 코드가 고유한지, 표의 행이 정확히 맞는지 확인하는 습관이 실제 업무에서는 더 큰 차이를 만듭니다.
