블로그
엑셀 VLOOKUP 함수 사용법, 다른 시트 값 가져오기
엑셀 VLOOKUP 함수는 표의 첫 번째 열에서 값을 찾아, 같은 행의 다른 열 값을 가져오는 조회 함수입니다. 회사에서는 주문 목록의 품목코드로 품명과 단가를 붙이거나, 거래처코드로 담당자와 결제 조건을 불러오는 데 가장 많이 씁니다. 300줄짜리 주문서에 단가를 손으로 옮겨 적으면 한 시간이 걸리고 한두 줄은 꼭 틀리지만, 수식 한 줄을 채우면 1분 안에 끝납니다. 인수 네 개의 뜻부터 다른 시트 조회, 구간표, 대안 함수까지 순서대로 정리합니다.
VLOOKUP 함수의 네 가지 인수
=VLOOKUP(찾을 값, 표 범위, 열 번호, 찾기 방법)
Microsoft Learn의 VLOOKUP 문서는 네 인수를 이렇게 설명합니다.
- 찾을 값(lookup_value): 표의 첫 번째 열에서 찾을 값입니다. 품목코드, 사번, 거래처코드가 들어가며 값이나 셀 참조를 씁니다.
- 표 범위(table_array): 두 열 이상의 범위입니다. 첫 번째 열이 찾을 값이 있는 열이어야 합니다. 대소문자는 구분하지 않습니다.
- 열 번호(col_index_num): 범위 안에서 몇 번째 열 값을 가져올지 정합니다. 시트의 열 문자가 아니라 범위 안에서 센 순서이므로, 범위가 C:F라면 C가 1, F가 4입니다. 1보다 작으면 #VALUE!, 범위의 열 개수보다 크면 #REF!가 납니다.
- 찾기 방법(range_lookup): FALSE(또는 0)는 정확히 일치하는 값만, TRUE(또는 생략)는 정확히 일치하는 값이 없을 때 찾을 값보다 작은 값 가운데 가장 큰 값을 가져옵니다.

위 화면의 =VLOOKUP($A$4,Employee_List!$A$2:$E$11,5,FALSE)는 A4의 사번을 Employee_List 시트 A열에서 찾아 같은 행의 다섯 번째 열(국가)을 가져옵니다. 코드나 번호로 찾는 일이라면 네 번째 인수는 언제나 FALSE입니다. 이 인수를 생략하면 TRUE로 처리되어, 없는 코드를 넣어도 오류 대신 근처 행의 엉뚱한 값이 들어옵니다.
다른 시트와 다른 파일에서 값 가져오기
표 범위 앞에 시트 이름과 느낌표를 붙이면 다른 시트를 조회합니다. 수식을 입력하다가 다른 시트 탭을 눌러 범위를 드래그하면 엑셀이 시트이름!범위 형태를 알아서 넣어 줍니다. 시트 이름에 공백이 있으면 '품목 마스터'!$A$2:$D$500처럼 작은따옴표로 감쌉니다.

위 화면은 입력 양식 시트(Lookup_Form)에서 사번을 넣으면 직원 목록 시트(Employee_List)의 이름을 가져오는 구조입니다. 수식을 아래로 채울 때는 범위를 $A$2:$E$11처럼 F4로 고정해야 행마다 같은 범위를 봅니다.

찾을 값은 A4처럼 상대 참조로 두면 행마다 자기 사번을 찾습니다. 반대로 첫 번째 화면처럼 $A$4로 고정하면 어느 행에 수식을 넣어도 4행의 사번만 찾으므로, 한 사람의 정보를 여러 칸에 펼쳐 보여 주는 조회 양식에 맞습니다. 같은 범위에서 다른 열을 가져오려면 열 번호만 바꿉니다.

다른 파일에서 가져올 때는 =VLOOKUP(A2,'[품목마스터.xlsx]마스터'!$A:$D,3,FALSE)처럼 파일 이름을 대괄호로 감쌉니다. 참조 파일을 닫아도 마지막 값은 남지만, 파일 이름을 바꾸거나 다른 폴더로 옮기면 연결이 끊깁니다. 매일 쓰는 기준표는 같은 파일 안 시트로 복사해 두는 편이 관리가 쉽습니다.
열 번호를 MATCH로 자동으로 찾기
열 번호를 숫자로 적어 두면 기준표 중간에 열이 추가될 때 다른 열 값을 가져오게 됩니다. ERP에서 내려받는 품목 마스터는 버전에 따라 열 구성이 바뀌는 일이 잦습니다. 이때 열 번호 자리에 MATCH를 넣으면 머리글 이름으로 열을 찾습니다.
=VLOOKUP($B2,마스터!$A:$H,MATCH("판매단가",마스터!$A$1:$H$1,0),FALSE)
Microsoft Learn의 MATCH 문서에 따르면 MATCH는 찾는 값의 상대 위치를 돌려주고, 마지막 인수가 0이면 정확히 같은 첫 번째 값을 찾습니다. "판매단가" 머리글이 몇 번째 열에 있든 그 위치가 열 번호로 들어가므로, 마스터 열이 바뀌어도 수식은 그대로 맞습니다.
품명, 규격, 단가처럼 여러 열을 한꺼번에 가져올 때도 MATCH가 편합니다. 결과 표의 머리글을 마스터와 같은 이름으로 적고 MATCH(C$1,마스터!$A$1:$H$1,0)처럼 결과 표 머리글을 참조하면, 수식 하나를 오른쪽과 아래로 채우는 것만으로 모든 칸이 맞는 열을 찾아옵니다.
근사 일치는 구간표에만: 수량별 할인율
근사 일치(TRUE)는 "어느 구간에 속하는가"를 찾을 때 씁니다. 수량별 할인율표를 예로 들면 첫 열에 각 구간의 시작값을 오름차순으로 적습니다.
| 최소 수량 | 할인율 |
|---|---|
| 0 | 0% |
| 100 | 3% |
| 500 | 5% |
| 1,000 | 8% |
=VLOOKUP(D2,할인표!$A$2:$B$5,2,TRUE)에 수량 230을 넣으면 230보다 작거나 같은 값 가운데 가장 큰 100의 행을 찾아 3%를 돌려줍니다. 첫 열이 오름차순이 아니면 결과가 틀리고, 수량이 첫 구간(0)보다 작으면 #N/A가 나옵니다. 구간이 늘어 행을 추가했다면 반드시 다시 정렬합니다. 이 경우를 빼면 VLOOKUP의 네 번째 인수는 FALSE로 둡니다.
와일드카드와 오류 처리
정확히 일치 모드에서 찾을 값이 텍스트라면 와일드카드를 쓸 수 있습니다. *는 여러 글자, ?는 한 글자입니다. =VLOOKUP("*"&G2&"*",$A:$D,4,FALSE)는 G2를 포함하는 첫 번째 품명의 단가를 가져옵니다. 실제 별표나 물음표를 찾으려면 앞에 ~를 붙입니다.
값을 못 찾으면 #N/A가 납니다. 보고용 표에서 빈칸으로 보이게 하려면 =IFNA(VLOOKUP(…),"")를 씁니다. IFNA는 #N/A만 바꾸고 #REF! 같은 다른 오류는 그대로 보여 주므로, 범위가 깨진 수식을 숨기지 않습니다. 기준표에 같은 코드가 두 번 있으면 VLOOKUP은 처음 찾은 행의 값을 가져온다는 점도 기억해 둡니다. #N/A가 계속 난다면 형식과 공백 문제일 가능성이 높으니 아래 글의 순서대로 확인합니다.
👉 엑셀 VLOOKUP 안될 때, #N/A 오류 원인 6가지
예시: 세진물산 주문 목록에 품명, 단가, 할인율 붙이기
세진물산 영업지원 담당자는 거래처에서 받은 주문을 10월_주문접수.xlsx의 주문 시트에 모읍니다. 주문 시트에는 거래처, 품목코드, 수량만 있고, 같은 파일의 품목마스터 시트에 ERP에서 내려받은 품목코드, 품명, 규격, 판매단가가, 할인표 시트에 수량 구간별 할인율이 있습니다. 오늘 주문은 214줄입니다.
담당자는 주문 시트 D1~F1에 품목마스터와 같은 이름으로 "품명", "규격", "판매단가"를 적고, D2에 =VLOOKUP($B2,품목마스터!$A:$D,MATCH(D$1,품목마스터!$A$1:$D$1,0),FALSE)를 넣어 오른쪽 두 칸과 아래 214줄로 채웁니다. 찾을 값은 $B2로 열만 고정하고, 열 번호는 D$1로 행만 고정해 각 칸이 자기 머리글을 찾게 했습니다.
결과를 보니 6줄이 #N/A였습니다. 4줄은 품목코드 뒤에 공백이 붙어 있었고, 2줄은 이번 달 새로 나온 품목이라 마스터에 아직 없었습니다. 공백은 TRIM 보조 열로 정리하고, 신규 품목 2줄은 영업 담당자에게 단가를 물어 마스터에 추가했습니다.
다음은 할인율입니다. G열에 =VLOOKUP(C2,할인표!$A$2:$B$5,2,TRUE)를 넣어 수량 구간 할인율을 가져오고, H열에 =C2*F2*(1-G2)로 공급가액을 계산했습니다. 마지막으로 수량이 1,000개를 넘는 주문 세 건의 할인율을 표와 직접 대조해 8%가 맞게 들어갔는지 확인했습니다. 손으로 옮기던 때는 오후 내내 걸리던 작업이 수식을 채우고 예외 6줄을 처리하는 20분으로 줄었습니다.
VLOOKUP, INDEX/MATCH, XLOOKUP 비교
| 항목 | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| 왼쪽 열 가져오기 | 안 됨 | 됨 | 됨 |
| 기본 찾기 방법 | 근사 일치 | MATCH 0이면 정확히 일치 | 정확히 일치 |
| 열 추가에 강함 | 열 번호 고정이라 약함 | 강함 | 강함 |
| 쓸 수 있는 버전 | 모든 버전 | 모든 버전 | Microsoft 365, Excel 2021 이후 |
VLOOKUP은 오른쪽 열만 가져오고 첫 열에서만 찾습니다. 품명으로 찾아 왼쪽의 품목코드를 가져오려면 =INDEX(마스터!$A:$A,MATCH(G2,마스터!$B:$B,0))처럼 INDEX/MATCH를 씁니다. Microsoft 365와 Excel 2021 이후 버전이라면 =XLOOKUP(G2,마스터!$B:$B,마스터!$A:$A,"없음")이 가장 간단하고, 못 찾았을 때 표시할 값까지 한 번에 정합니다. 다만 Excel 2019 이하에서 열면 #NAME?이 나오므로, 거래처와 주고받는 파일에는 VLOOKUP이나 INDEX/MATCH를 씁니다.
VLOOKUP을 쓸 때 자주 하는 실수
범위를 고정하지 않는 실수가 가장 흔합니다. A2:D500을 그대로 두고 아래로 채우면 한 줄 내려갈 때마다 범위도 A3:D501로 밀려, 아래쪽 행은 기준표 윗부분을 찾지 못합니다. 범위를 선택하고 F4를 눌러 $A$2:$D$500으로 고정하거나, 열 전체($A:$D)를 지정합니다. 기준표를 **표(Ctrl+T)**로 만들어 이름으로 참조하면 행이 늘어도 범위가 따라옵니다.
열 번호를 시트 열 기준으로 세는 실수도 많습니다. 범위가 C열에서 시작하면 C가 1번입니다. 시트의 F열이 4번째인지 6번째인지 헷갈린다면 MATCH로 머리글을 찾게 하는 편이 낫습니다.
찾을 값과 기준표의 형식이 다른 경우에는 값이 분명히 보이는데도 #N/A가 납니다. 한쪽은 숫자, 한쪽은 텍스트인 코드가 대표적입니다. 결과를 값으로 붙여넣지 않고 수식 그대로 두는 것도 실수가 될 수 있습니다. 마스터 단가가 바뀌면 이미 지난 주문의 단가도 함께 바뀌므로, 확정한 주문은 단가 열을 복사해 값 붙여넣기로 고정해 둡니다.
엑셀 버전에 따라 쓸 수 있는 방법도 다릅니다. VLOOKUP, INDEX, MATCH는 모든 버전에 있지만 XLOOKUP과 배열로 여러 열을 한 번에 가져오는 방식은 Microsoft 365와 Excel 2021 이후에서만 됩니다. Microsoft 365에서는 찾을 값 자리에 B2:B215처럼 범위를 넣으면 결과가 아래로 쏟아지므로 수식을 채울 필요도 없습니다. 파일을 받을 사람의 버전을 모르면 한 셀에 한 수식을 넣는 전통적인 방식이 가장 안전합니다.
엑셀 VLOOKUP 함수 자주 묻는 질문 (FAQ)
Q. VLOOKUP으로 여러 열을 한 번에 가져올 수 있나요?
결과 표 머리글을 기준표와 같은 이름으로 적고, 열 번호 자리에 MATCH(D$1,기준표머리글,0)을 넣어 오른쪽으로 채웁니다. Microsoft 365라면 열 번호에 {2,3,4}처럼 배열을 넣어 세 열을 한 번에 쏟아 낼 수도 있습니다.
Q. 조건이 두 개인 값(거래처별 품목 단가)은 어떻게 찾나요?
기준표 맨 왼쪽에 =A2&"|"&B2처럼 두 값을 이어 붙인 보조 열을 만들고, 찾을 값도 같은 순서로 이어 붙여 VLOOKUP합니다. XLOOKUP을 쓸 수 있다면 =XLOOKUP(G2&H2,A:A&B:B,C:C)처럼 보조 열 없이도 됩니다.
Q. VLOOKUP과 HLOOKUP은 무엇이 다른가요?
VLOOKUP은 첫 번째 열에서 세로로 찾고, HLOOKUP은 첫 번째 행에서 가로로 찾습니다. 이름의 V와 H가 각각 세로(vertical)와 가로(horizontal)를 뜻합니다. 월별 단가처럼 기준 값이 머리글 행에 가로로 놓인 표에는 HLOOKUP을 씁니다.
Q. 결과가 숫자인데 합계가 안 됩니다.
기준표의 값 자체가 텍스트로 저장된 경우입니다. 기준표 열을 텍스트 나누기로 숫자로 바꾸거나, 수식을 =VALUE(VLOOKUP(…))로 감쌉니다.
inline AI로 주문 목록 채우기
inline AI는 주문 파일을 연 채로 "품목코드로 품목마스터에서 품명과 판매단가, 할인표에서 할인율을 가져와줘"라고 말하면 범위와 찾기 방법이 맞는 수식을 넣고, 공백이나 신규 품목 때문에 못 찾은 행을 따로 정리합니다. AI OS의 ARP를 함께 쓰면 거래처 메일로 온 발주서가 품명과 단가가 붙은 주문으로 바로 등록되므로, 주문 목록에 값을 붙이는 일 자체가 줄어듭니다.
inline AI에게 문서 작업부터 회사 업무까지 맡기세요.
회사 파일을 연 채 말로 맡기면 AI가 문서를 바로 고치고 새로 만듭니다. 회사용 AI OS까지 함께 쓰면 ERP를 비롯한 회사 업무 전체를 AI가 처리합니다.
무료 AX 컨설팅 받기




