엑셀 index match 다중 조건으로 가격 찾는 방법
부품 견적을 엑셀에 모아두면 같은 CPU가 여러 줄에 등장합니다. 메모리 구성이나 판매처가 다른데 CPU 이름만으로 가격을 찾으면 엉뚱한 금액이 나오죠. 이런 표에서는 두세 가지 조건이 모두 맞는 행을 찾도록 INDEX와 MATCH를 묶어 쓰면 됩니다.
먼저 찾을 조건과 결과 열을 나눈다
예를 들어 A열은 CPU, B열은 메모리, C열은 판매처, D열은 가격이라고 해보겠습니다. 데이터는 2행부터 6행까지 있고, F2에는 찾을 CPU, G2에는 메모리 용량, H2에는 판매처를 입력합니다. I2에 가격을 표시할 겁니다.
3행과 4행에 같은 CPU가 있어도 메모리나 판매처가 다를 수 있습니다. 이때 찾을 값은 F2 하나가 아니라 F2, G2, H2의 조합입니다. 실제 견적표에서도 제품명만 보고 가격을 가져왔다가 다른 구성의 금액을 붙이는 실수가 꽤 자주 납니다.
조건 두 개를 곱해서 MATCH에 넣는다
CPU와 메모리만 비교한다면 I2에 =INDEX($D$2:$D$6,MATCH(1,($A$2:$A$6=F2)*($B$2:$B$6=G2),0)) 을 입력합니다. A열에서 CPU가 맞는 행은 TRUE, B열에서 메모리가 맞는 행도 TRUE가 됩니다. 두 비교 결과를 곱하면 둘 다 맞는 행만 1이 되고, MATCH가 그 1의 위치를 찾습니다. INDEX는 같은 위치의 D열 가격을 돌려줍니다.
마지막 0은 정확히 일치하는 값을 찾겠다는 뜻입니다. 이 자리를 생략하면 예상과 다른 행을 잡을 수 있으니 넣어두는 편이 안전합니다. 달러 기호는 조회 범위를 고정합니다. 수식을 아래로 복사할 때 F2와 G2는 다음 행으로 바뀌고, 원본 데이터 범위는 그대로 유지됩니다.
Microsoft 365처럼 동적 배열을 지원하는 엑셀에서는 수식을 입력하고 Enter를 누르면 됩니다. 구형 엑셀에서 이 배열 계산이 제대로 되지 않으면 Ctrl+Shift+Enter로 입력해야 합니다. 이때 엑셀이 수식 바에 중괄호를 표시할 수 있는데, 중괄호를 직접 타이핑하는 방식은 아닙니다.
판매처까지 구분하려면 조건을 하나 더 붙인다
같은 CPU와 메모리 구성이라도 판매처마다 가격이 다르다면 C열 조건을 추가합니다. 수식은 =INDEX($D$2:$D$6,MATCH(1,($A$2:$A$6=F2)*($B$2:$B$6=G2)*($C$2:$C$6=H2),0)) 입니다. 세 조건이 모두 맞는 행에서만 곱한 결과가 1이 됩니다.
가령 F2가 라이젠 5 9600X, G2가 32GB, H2가 B상점이고 그 조합의 가격이 D4에 329000으로 들어 있다면 I2에도 329000이 표시됩니다. 가격 셀에 천 단위 구분 기호를 적용하면 329,000처럼 읽기 편합니다. 표시 형식만 바뀌며 원래 숫자 값은 유지됩니다.
조건을 더 늘릴 때는 같은 형태의 비교식을 곱하면 됩니다. 다만 모든 범위의 시작 행과 끝 행은 같아야 합니다. D2:D6을 결과 범위로 썼다면 비교 범위도 A2:A6, B2:B6처럼 동일한 행 수로 맞춥니다. 한 범위만 7행까지 잡히면 오류가 나거나 수식을 점검하기 어려워집니다.
값이 안 나올 때는 원본 데이터를 확인한다
#N/A가 나오면 먼저 해당 조합이 실제로 있는지 확인합니다. 화면에는 똑같이 보여도 셀 끝 공백이나 ‘32GB’와 ‘32 GB’처럼 다른 표기가 섞여 있으면 일치하지 않습니다. 가격이 숫자처럼 보이지만 텍스트로 저장된 경우도 있으니, 결과를 계산에 다시 쓸 계획이라면 D열의 데이터 형식까지 확인하는 게 좋습니다.
조건에 맞는 행이 둘 이상이면 MATCH는 첫 번째 행을 반환합니다. 중복 견적이 있는 표에서 최신 가격을 찾으려는 목적이라면 이 수식만으로는 부족합니다. 날짜 열을 추가하거나 중복 행을 먼저 구분해야 합니다. 조회 결과를 보기 전에 =COUNTIFS($A$2:$A$6,F2,$B$2:$B$6,G2,$C$2:$C$6,H2) 로 일치 행이 몇 개인지 확인하면 중복을 쉽게 찾을 수 있습니다.
빈 결과 대신 안내 문구를 표시하려면 전체 수식을 IFERROR로 감싸고 마지막 인수에 ‘일치 항목 없음’을 넣을 수 있습니다. 다만 오류를 숨기기 전에 입력값과 범위를 먼저 확인하는 편이 원인을 찾기 쉽습니다. 행이 계속 늘어나는 견적표라면 6행 같은 고정 끝점을 새 데이터까지 확장해야 한다는 점도 챙겨야 합니다. 저는 부품표를 만들 때 조회 수식보다 제품명과 옵션 표기를 일정하게 맞추는 데 시간을 더 씁니다. 그쪽이 실제 가격 오류를 훨씬 많이 줄여줍니다.
