엑셀 - INDEX + MATCH 함수 활용 (실전사용)

요약

• 제조공장/소상공인 공통으로 쓸 수 있는 재고·매출 관리 엑셀 파일을 만들었다.
• 핵심은 INDEX + MATCH 조합으로 품목코드 → 품목명/단가 자동 조회, 월 매출 조회까지 한 번에 가져오는 구조다.
• 품목마스터만 잘 관리하면, 입출고·매출 시트는 코드만 입력해도 나머지가 자동으로 채워지도록 설계했다.

 

1. 파일 구성과 전체 흐름

이번 파일은 세 개 시트로 나눴다.

1. 품목마스터
   • 품목코드, 품목명, 규격, 단위, 안전재고, 재고단가, 판매단가
   • 공장 기준으로는 원자재·부자재·완제품까지 한 번에 관리 가능
2. 재고입출고
   • 일자, 구분(입고/출고), 품목코드, 품목명, 수량, 재고단가, 금액
   • 코드만 넣으면 INDEX/MATCH로 품목명·단가가 자동 완성
3. 매출관리
   • 일자, 거래처, 품목코드, 품목명, 수량, 판매단가, 공급가, 부가세, 합계
   • 완제품 위주로 판매내역 관리
   • 오른쪽에 “월별 매출 요약” & “조회월 매출”을 INDEX/MATCH로 가져오는 영역 포함

핵심 데이터는 품목마스터에 있고, 나머지 시트는 “코드 입력 → 정보 자동 조회 → 금액 계산” 구조다.

---

2. 품목마스터 – 모든 계산의 기준

품목마스터 시트에는 예시로 아래처럼 넣어 두었다.

품목코드 품목명 규격 단위 안전재고 재고단가 판매단가

P001	알루미늄 판재	3T10002000	장	50	18,000	22,000
…	…	…	…	…	…	…

여기서 중요한 포인트는 품목코드를 유일한 키로 쓴다는 점이다.
이 코드를 기준으로 다른 시트에서 조회하기 때문에
코드만 정확하면 나머지는 다 따라온다.

---

3. 재고입출고 – INDEX/MATCH로 품명·단가 자동입력

재고입출고 시트의 기본 컬럼은 다음과 같다.

|일자|구분|품목코드|품목명|수량|재고단가|금액|

입출고를 입력할 때는 보통

• 일자
• 구분(입고/출고)
• 품목코드
• 수량

까지만 사람이 직접 입력한다.
품목명과 단가, 금액은 수식으로 처리한다.

3-1. 품목명 조회 (D열)

D2 셀 수식:

=IFERROR(
  INDEX(품목마스터!$B$2:$B$9,
        MATCH($C2, 품목마스터!$A$2:$A$9, 0)
  ),
  ""
)

• MATCH($C2, 품목마스터!$A$2:$A$9, 0)
  → C2(품목코드)가 품목마스터 A열에서 몇 번째 행인지 찾는다.
• INDEX(품목마스터!$B$2:$B$9, 그행번호)
  → 같은 행의 B열(품목명)을 가져온다.
• IFERROR 로 코드가 비어있거나 오타일 때 공백 처리.

근거
코드·이름이 바뀔 수 있는 제조/유통 환경에서는
열 위치가 바뀌면 깨지는 VLOOKUP보다,
행/열을 분리하는 INDEX/MATCH가 구조적으로 안정적이다.

3-2. 재고단가 조회 (F열)

F2 셀:

=IFERROR(
  INDEX(품목마스터!$F$2:$F$9,
        MATCH($C2, 품목마스터!$A$2:$A$9, 0)
  ),
  ""
)

동일한 방법으로 이번에는 F열(재고단가)을 가져온다.

3-3. 금액 계산 (G열)

G2 셀:

=IFERROR(E2*F2,"")

수량×단가만 해주면 되므로 단순한 곱셈이다.
입출고 내역 몇 줄만 입력해 보면 단가 오타 없이 금액이 자동 계산되는 걸 바로 확인할 수 있다.

---

4. 매출관리 – 제품 판매와 월매출 조회

매출관리 시트는 소상공인 매장 매출 관리에도 그대로 쓸 수 있게 구성했다.

4-1. 판매 시 품목명/단가 자동 입력

구조는 다음과 같다.

|일자|거래처|품목코드|품목명|수량|판매단가|공급가|부가세|합계|

품목명(D열):

=IFERROR(
  INDEX(품목마스터!$B$2:$B$9,
        MATCH($C2, 품목마스터!$A$2:$A$9, 0)
  ),
  ""
)

판매단가(F열):

=IFERROR(
  INDEX(품목마스터!$G$2:$G$9,
        MATCH($C2, 품목마스터!$A$2:$A$9, 0)
  ),
  ""
)

공급가(G열):

=IFERROR(E2*F2,"")

부가세(H열, 10% 가정):

=IFERROR(ROUND(G2*0.1,0),"")

합계(I열):

=IFERROR(G2+H2,"")

이렇게 하면 판매 시에는

• 날짜
• 거래처
• 품목코드
• 수량

만 입력하면 되고, 단가/금액 계산은 완전히 자동화된다.

---

4-2. 월별 매출 요약 + INDEX/MATCH 조회

오른쪽 K열~L열에 간단한 월별 요약을 만들어 두었다.

• K2 : 2025-11
• L2 : 2025년 11월 매출합계

L2 수식:

=SUMIFS($I$2:$I$6,
        $A$2:$A$6,">="&DATE(2025,11,1),
        $A$2:$A$6,"<"&DATE(2025,12,1))

그리고 아래와 같이 “조회월”을 입력하는 셀을 두었다.

• L4 : 조회월 입력 (예: 2025-11)
• L5 : 선택한 월 매출

L5 수식:

=INDEX($L$2:$L$2, MATCH($L$4,$K$2:$K$2,0))

여기서는 단일 월만 있지만,
실제 사용할 때는 1년치 월별 합계(K2:K13, L2:L13)를 쌓아두고 같은 구조로 조회하면
원하는 월의 매출 금액을 한 셀에서 바로 뽑아낼 수 있다.

---

5. 실무에서 이렇게 확장해볼 수 있다

• 품목마스터에 카테고리, 창고위치 등을 추가하고,
  INDEX/MATCH로 재고리포트에 함께 가져오기
• 매출관리에 결제수단, 마진율 열을 추가하고,
  판매단가 - (재고단가*소요수량)으로 품목별 이익 분석
• 피벗테이블로 “월별/거래처별/품목별 매출”을 한 번에 분석

가장 중요한 것은 모든 참조의 기준을 품목코드 하나로 통일하는 것이다.
그 위에 INDEX + MATCH를 얹으면,
제조공장 생산자재 관리든, 동네 매장의 매출 관리든
같은 패턴으로 안정적인 관리 파일을 계속 재사용할 수 있다.

재고_매출관리_INDEX_MATCH.xlsx

0.01MB