2026-09-22
거래처명을 넣고 VLOOKUP을 걸었는데 그 거래처의 첫 번째 금액만 나오고, 두 번째 세 번째 줄은 아무리 수식을 아래로 채워도 같은 값만 반복된다면 이 글이 맞습니다.
먼저 안 되는 것부터 말씀드립니다. VLOOKUP, HLOOKUP, XLOOKUP, INDEX와 MATCH 조합은 전부 "처음 만나는 한 개"를 돌려주고 멈춥니다. 같은 키가 열 줄 있어도 열 줄 중 맨 위 하나만 봅니다. 함수의 결함이 아니라 설계가 그렇습니다. 그래서 두 번째 값부터는 방법을 바꿔야 합니다. 방법은 엑셀 버전에 따라 셋입니다.
공통 전제는 이렇습니다. A2:A100에 거래처명, B2:B100에 금액이 있고, E1에 찾을 거래처명을 넣습니다.
첫째, 엑셀 2021 이상 또는 Microsoft 365라면 FILTER 한 줄입니다.
=FILTER($B$2:$B$100, $A$2:$A$100=$E$1, "없음")
이 수식을 F2에 넣으면 일치하는 값이 원래 입력된 순서대로 F2, F3, F4로 자동으로 내려옵니다. 아래로 채울 필요가 없습니다. N번째 값 하나만 필요하면 이렇게 감쌉니다.
=INDEX(FILTER($B$2:$B$100, $A$2:$A$100=$E$1), 3)
3을 2로 바꾸면 두 번째, ROW(A1)로 바꾸고 아래로 채우면 1, 2, 3번째가 차례로 나옵니다.
둘째, 2019 이하인데 열 하나를 추가할 수 있다면 도우미 열이 가장 안전합니다. C2에 넣고 끝까지 채웁니다.
거래처명 뒤에 "이 행까지 몇 번째 등장인가"가 붙어서 "가나상사|1", "가나상사|2" 같은 고유한 키가 됩니다. 구분자 "|"를 넣는 이유는 거래처명이 숫자로 끝날 때 "상사1"+"1"과 "상사11"이 섞이는 사고를 막기 위해서입니다. 그다음 F2에 넣고 아래로 채웁니다.
=IFERROR(INDEX($B$2:$B$100, MATCH($E$1&"|"&ROW(A1), $C$2:$C$100, 0)), "")
셋째, 도우미 열을 못 만드는 경우입니다. 남이 준 파일이라 열 구조를 건드리면 안 될 때가 있습니다. 이때는 배열 수식입니다. F2에 넣고 2019 이하에서는 Ctrl+Shift+Enter로 입력한 뒤 아래로 채웁니다.
=IFERROR(INDEX($B$2:$B$100, SMALL(IF($A$2:$A$100=$E$1, ROW($A$2:$A$100)-ROW($A$2)+1), ROW(A1))), "")
IF가 일치하는 행 번호만 남기고, SMALL이 그중 1번째, 2번째로 작은 것을 고르고, INDEX가 그 위치의 금액을 꺼냅니다. 범위가 2행부터 시작하기 때문에 ROW($A$2)를 빼서 위치를 1부터 맞춘 것입니다. 이걸 빼면 한 칸씩 밀린 값이 나오는데 오류가 안 뜨기 때문에 알아채기가 어렵습니다.
어느 방법을 쓰든 결과가 몇 개 나와야 하는지 먼저 세어 두시기를 권합니다.
이 숫자와 실제로 꺼내진 개수가 다르면 아래 함정 중 하나에 걸린 것입니다.
첫째, 앞뒤 공백입니다. 쇼핑몰이나 ERP에서 내려받은 파일은 거래처명 뒤에 공백이 붙어 오는 일이 잦습니다. "가나상사 "와 "가나상사"는 다른 값이라 COUNTIF는 3을 세는데 FILTER는 2개만 돌려주는 식으로 어긋납니다. 기준 열에 =TRIM(A2)를 한 번 돌려 정리한 뒤 붙여넣기(값)로 덮으십시오. 공백이 아니라 CHAR(160) 같은 보이지 않는 문자일 때는 =TRIM(SUBSTITUTE(A2,CHAR(160),""))로 바꿔야 합니다.
둘째, 숫자와 텍스트의 불일치입니다. 기준이 주문번호처럼 숫자로 된 값이면 한쪽은 숫자 3001, 다른 쪽은 텍스트 "3001"일 수 있습니다. 셀 왼쪽 위 초록 삼각형이 그 표시입니다. COUNTIF는 둘을 같은 것으로 세지만 FILTER의 = 비교와 MATCH는 다른 값으로 봅니다. 그래서 COUNTIF 검증은 통과하는데 결과는 비는 상황이 생깁니다. 한쪽을 =VALUE(A2) 또는 =A2&""로 맞추십시오.
셋째, 도우미 열의 $ 위치입니다. COUNTIF($A$2:A2,A2)에서 앞은 잠그고 뒤는 풀어야 범위가 한 줄씩 늘어납니다. 둘 다 잠그면 순번이 전부 같은 숫자가 되고, 둘 다 풀면 전부 1이 되어 첫 번째 값만 반복됩니다.
넷째, FILTER가 #SPILL! 오류를 내는 경우입니다. 결과가 흘러내릴 자리에 다른 값이 하나라도 있으면 수식 전체가 멈춥니다. 아래쪽 셀을 비우거나 INDEX로 감싸서 한 칸씩 받으십시오.
다섯째, 정렬 전 원본 순서와 정렬 후 순서가 다릅니다. "순서대로"가 입력된 순서인지 날짜순인지에 따라 결과가 달라지므로, 정렬을 먼저 하고 수식을 걸어야 원하시는 순번이 됩니다.
찾을 값과 꺼낼 값이 몇 열에 있는지, 그리고 엑셀 버전이 무엇인지에 따라 위 세 방법 중 하나가 정해집니다. 버전을 모르시면 빈 셀에 =FILTER(A1,TRUE)를 넣어 보십시오. 오류 없이 값이 나오면 첫째 방법을 쓰실 수 있습니다.
지금 그 파일, 어디가 문제인지 먼저 보시겠습니까
CSV 를 넣으면 계산이 틀어지는 자리를 찾아 드립니다. 무료이고 파일은 서버로 올라가지 않습니다 — 브라우저 안에서만 처리되고 저희도 내용을 볼 수 없습니다.
매달 같은 순서로 고치고 계신다면 그 순서를 프로그램으로 옮겨둔 것이 있습니다 — 쇼핑몰 월 정산 자동화. 되는지부터 보시려면 무료 진단.