Excel VLOOKUP 함수

The Excel VLOOKUP 함수는 테이블이나 범위의 첫 번째 열에서 지정된 값을 세로로 검색한 후 동일한 행의 다른 열에서 해당 값을 반환하는 강력한 도구입니다. VLOOKUP 은 매우 유용하지만 초보자에게는 때때로 이해하기 어려울 수 있습니다。 이 튜토리얼은인수에 대한 단계별 설명,유용한 예제및VLOOKUP 함수 사용 시 발생할 수 있는 일반적인 오류 해결 방법을 제공하여 VLOOKUP 을 완벽히 익힐 수 있도록 도와드립니다。

• 인수에 대한 단계별 설명
• 구문 및 인수
• VLOOKUP 예제
  정확히 일치 vs. 근사치 일치|다중 조건을 사용한 Vlookup
• VLOOKUP 일반적인 오류 및 해결 방법
  #N/A 오류|#VALUE 오류|#REF 오류|잘못된 값

VLOOKUP 예제 .xlsx VLOOKUP 오류 .xlsx

관련 동영상

인수에 대한 단계별 설명

위 스크린샷과 같이 VLOOKUP 함수는 주어진 ID 번호를 기준으로 이메일을 찾는 데 사용됩니다. 이제 이 예제에서 VLOOKUP 을 사용하는 방법을 각 인수를 단계별로 나누어 자세히 설명하겠습니다。

단계 1: VLOOKUP 함수 시작하기

결과를 출력할 셀(H6)을 선택한 후수식 표시줄에 다음 내용을 입력하여 VLOOKUP 함수를 시작합니다。

=VLOOKUP(

단계 2: 조회 값 지정하기

먼저 VLOOKUP 함수에서 조회 값(찾고자 하는 값)을 지정합니다. 여기서는 특정 ID 번호가 포함된 셀 G6 을 참조합니다 1005。

=VLOOKUP(G6

단계 3: 테이블 배열 지정하기

다음으로 찾고자 하는 값과 반환하고자 하는 값이 모두 포함된 셀 범위를 지정합니다。 이 경우 B6:E12 범위를 선택합니다。 이제 수식은 다음과 같이 표시됩니다：

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

단계 4: 값을 반환할 열 지정하기

그런 다음 값을 반환할 열을 지정합니다。

이 예제에서는 ID 번호를 기준으로 이메일을 반환해야 하므로 4 을 입력하여 VLOOKUP 이 데이터 범위의 네 번째 열에서 값을 반환하도록 지시합니다。

=VLOOKUP(G6,B6:E12,4

단계 5: 근사치 또는 정확한 일치 찾기

마지막으로 근사치 일치인지 정확한 일치인지를 결정합니다。

• 정확히 일치하는 값을 찾으려면정확히 일치를 위해 마지막 인수로FALSE를 사용해야 합니다。
• 근사치 일치를 찾으려면근사치 일치를 위해 마지막 인수로TRUE를 사용하거나 이 인수를 비워 두세요。

이 예제에서는 정확한 일치를 위해 FALSE 를 사용합니다。 이제 수식은 다음과 같습니다：

=VLOOKUP(G6,B6:E12,4,FALSE

Enter 키를 눌러 결과 확인하기

위 예제에서 각 인수를 하나씩 설명함으로써 VLOOKUP 함수의 구문과 인수가 훨씬 더 이해하기 쉬워졌습니다。

구문 및 인수

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Lookup_value(필수): 찾고자 하는 값(실제 값 또는 셀 참조)입니다. 이 값은 table_array 의 첫 번째 열에 있어야 합니다。
• Table_array(필수)： 조회 값의 열과 반환 값의 열을 모두 포함하는 셀 범위입니다。
• Col_index(필수): 반환 값이 포함된 열 번호를 나타내는 정수입니다. table_array 의 가장 왼쪽 열부터 1 번으로 시작합니다。
• Range_lookup(선택): VLOOKUP 이 근사치 일치 또는 정확히 일치를 찾을지 여부를 결정하는 논리적 값입니다。
  • 근사치 일치- 이 인수를TRUE로 설정하거나，1비워 두세요.중요
    : 근사치 일치를 찾으려면 table_array 의 첫 번째 열 값이 오름차순으로 정렬되어 있어야 합니다. 그렇지 않으면 VLOOKUP 이 잘못된 결과를 반환할 수 있습니다。: To find an approximate match, the values in the first column of the table_array must be sorted in ascending order in case VLOOKUP returns the wrong result.
  • 정확한 일치- 이 인수를FALSE로 설정하거나0.

예제

이 섹션에서는 VLOOKUP 함수를 보다 포괄적으로 이해할 수 있도록 몇 가지 예제를 보여줍니다。

예제 1: VLOOKUP 에서 정확한 일치와 근사치 일치 비교

VLOOKUP 을 사용할 때 정확한 일치와 근사치 일치에 대해 혼동된다면， 이 섹션에서 그 차이점을 명확히 이해할 수 있습니다。

VLOOKUP 에서 정확한 일치

이 예제에서는 E6:E8 범위에 나열된 점수를 기준으로 해당 이름을 찾으려고 합니다. 따라서 F6 셀에 다음 수식을 입력하고 자동 채우기 핸들을 F8 까지 드래그합니다。 이 수식에서 마지막 인수는 정확한 일치 조회를 위해FALSE로 지정됩니다。

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

그러나 점수 98 이 데이터 범위의 첫 번째 열에 존재하지 않기 때문에 VLOOKUP 은 #N/A 오류 결과를 반환합니다。

VLOOKUP 에서 근사치 일치

앞의 예제를 계속 사용하여 마지막 인수를TRUE로 변경하면 VLOOKUP 은 근사치 일치 조회를 수행합니다。 일치 항목이 없으면 조회 값보다 작거나 같은 다음으로 큰 값을 찾아 해당 결과를 반환합니다。

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

점수 98 이 존재하지 않으므로 VLOOKUP 은 98 보다 작은 다음으로 큰 값인 95 을 찾아 점수 95 의 이름을 가장 가까운 결과로 반환합니다。

예제 2: 여러 조건으로 VLOOKUP 사용하기

이 섹션에서는 Excel 에서 여러 조건으로 VLOOKUP 을 사용하는 방법을 보여줍니다。 아래 스크린샷과 같이 제공된 이름(H5 셀)과 부서(H6 셀)를 기준으로 급여를 찾으려면 다음 단계를 따르세요。

단계 1: 조회 열의 값을 연결하기 위한 도우미 열 추가하기

이 경우이름열과부서열의 값을 연결하는 도우미 열을 만들어야 합니다。

1. 데이터 범위의 왼쪽에 도우미 열을 추가하고 이 열에 헤더를 지정하세요。 스크린샷 참조：
   
   
2. 이 도우미 열에서 헤더 아래 첫 번째 셀을 선택하고 다음 수식을수식 표시줄에 입력한 후Enter를 누르세요。
   

   =C6&" "&D6

   
   참고： 이 수식에서는 앰퍼샌드(&)를 사용하여 두 열의 텍스트를 결합해 하나의 텍스트로 만듭니다。

   • C6는 조인할Name열의 이름입니다。D6는 조인할Department열의 첫 번째 부서입니다。
   • 이 두 셀의 값은 사이에 공백을 넣어 연결됩니다。
3. 이 결과 셀을 선택한 후자동 채우기 핸들을 아래로 드래그하여 같은 열의 다른 셀에도 이 수식을 적용하세요。
   
   

단계 2: 지정된 조건으로 VLOOKUP 함수 적용하기

결과를 출력할 셀(I7)을 선택하고수식 표시줄에 다음 수식을 입력한 후Enter를 누릅니다。

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

결과

일반적인 VLOOKUP 오류 및 해결 방법

이 섹션에서는 VLOOKUP 사용 시 발생할 수 있는 일반적인 오류와 이를 해결하는 방법을 안내합니다。

#N/A 오류가 반환되는 경우

VLOOKUP 에서 가장 흔한 오류는 #N/A 오류로, Excel 이 찾고자 하는 값을 찾지 못했음을 의미합니다. VLOOKUP 이 #N/A 오류를 반환하는 몇 가지 이유는 다음과 같습니다。

원인 1: 조회 값이 table_array 의 첫 번째 열에 없음

Excel VLOOKUP 의 제한 사항 중 하나는 왼쪽에서 오른쪽으로만 조회할 수 있다는 점입니다. 따라서 검색 값 범위은 table_array 의 첫 번째 열에 있어야 합니다。

아래 스크린샷과 같이 주어진 직위을 기준으로 이름을 반환하려고 합니다。 여기서 조회 값()영업 관리자)은 table_array 의 두 번째 열에 있고 반환 값는 조회 열의 왼쪽에 있으므로 VLOOKUP 은 #N/A 오류를 반환합니다。

해결 방법

이 오류를 해결하려면 다음 해결 방법 중 하나를 적용할 수 있습니다。

• 열 재정렬
  
  table_array 의 첫 번째 열에 조회 열이 오도록 열을 재정렬할 수 있습니다。
• INDEX 및 MATCH 함수를 함께 사용
  
  여기서는 이 문제를 해결하기 위해 VLOOKUP 의 대안으로 INDEX 및 MATCH 함수를 함께 사용합니다。

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• XLOOKUP 함수 사용(Excel 365, Excel 2021 이상 버전에서 사용 가능)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

원인 2: 조회 값이 조회 열에서 찾을 수 없음(정확한 일치)

VLOOKUP 이 #N/A 오류를 반환하는 가장 일반적인 이유 중 하나는 찾고자 하는 값이 존재하지 않기 때문입니다。

아래 예제와 같이 E6 의 주어진 점수 98 를 기준으로 이름을 찾으려고 합니다. 그러나 이 점수는 데이터 범위의 첫 번째 열에 존재하지 않으므로 VLOOKUP 은 #N/A 오류 결과를 반환합니다。

해결 방법

이 오류를 해결하려면 다음 해결 방법 중 하나를 시도할 수 있습니다。

• VLOOKUP 이 조회 값보다 작은 다음으로 큰 값을 검색하도록 하려면 마지막 인수를FALSE(정확히 일치)에서TRUE(근사치 일치)로 변경하세요。 자세한 내용은 다음을 참조하세요。예제 1: VLOOKUP 을 사용한 정확히 일치와 근사치 일치.
• 마지막 인수를 변경하지 않고 조회 값이 없을 경우 알림을 받으려면 IFERROR 함수 내에 VLOOKUP 함수를 포함시키세요。
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

원인 3: 조회 값이 조회 열의 최소값보다 작음(근사치 일치)

아래 스크린샷과 같이 근사치 일치 조회를 수행하고 있습니다. 찾고자 하는 값(이 경우 ID 번호 1001)이 조회 열의 최소값 1002 보다 작기 때문에 VLOOKUP 은 #N/A 오류를 반환합니다。

해결 방법

다음 두 가지 해결 방법을 참고하세요。

• 조회 값이 크거나 같음이 조회 열에서 가장 작은 값인지 확인하세요。
• 조회 값이 없을 경우 Excel 이 알림을 표시하도록 하려면 다음과 같이 IFERROR 함수 내에 VLOOKUP 함수를 중첩하세요。
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

원인 4: 숫자가 텍스트로 서식 지정됨

아래 스크린샷에서 볼 수 있듯이 이 예제의 #N/A 오류 결과는 조회 셀(G6)과 원본 테이블의 조회 열(B6:B12) 간 데이터 형식 불일치로 인해 발생합니다. 여기서 G6 의 값은 숫자이고 B6:B12 범위의 값은 텍스트로 서식 지정된 숫자입니다。

해결 방법

이 문제를 해결하려면 조회 값을 다시 숫자로 변환해야 합니다。 다음 두 가지 방법을 참고하세요。

• 숫자로 변환 기능 적용
  
  변환하려는 셀을 클릭하고 해당 셀 옆에 있는 이 버튼을 선택한 후숫자로 변환을 선택하세요。.
  
• 편리한 도구를 적용하여 일괄적으로 텍스트와 숫자 간의 변환
  
  텍스트와 숫자 간의 변환기능은Kutools for Excel여러 셀을 텍스트에서 숫자로로 쉽게 변환하거나 그 반대로 변환할 수 있도록 도와줍니다。지금 30 일간 모든 기능을 무료로 체험하세요！

원인 5: VLOOKUP 수식을 다른 셀로 드래그할 때 table_array 가 고정되지 않음

아래 스크린샷과 같이 E6 및 E7 셀에 두 개의 검색 값 범위이 있습니다. F6 에서 첫 번째 결과를 얻은 후 VLOOKUP 수식을 F6 에서 F7 로 드래그하면 #N/A 오류 결과가 반환됩니다. 이는 셀 참조(B6:C12)가 기본적으로 상대 참조로 설정되어 있어 행을 따라 아래로 이동할 때 자동으로 조정되기 때문입니다. 테이블 배열이 B7:C13 으로 이동되어 더 이상 조회 점수 73 을 포함하지 않게 되었기 때문입니다。

해결 방법

테이블 배열을 고정하여 일정하게 유지하려면 셀 참조의 행과 열 앞에$기호를 추가해야 합니다. Excel 에서 절대 참조에 대해 더 알아보려면 다음 자습서를 참조하세요：Excel 절대 참조(만드는 방법 및 사용법).

#VALUE 오류가 반환됨

다음 조건들이 VLOOKUP 함수가 #VALUE 오류 결과를 반환하도록 만들 수 있습니다。

원인 1: 조회 값이 255 자를 초과함

아래 스크린샷과 같이 H4 셀의 조회 값이 255 자를 초과하므로 VLOOKUP 함수가 #VALUE 오류 결과를 반환합니다。

해결 방법

이 제한을 해결하려면 더 긴 문자열을 처리할 수 있는 다른 조회 함수를 사용할 수 있습니다。 다음 수식 중 하나를 시도해 보세요。

• INDEX 및 MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• XLOOKUP 함수(Excel 365, Excel 2021 이상 버전에서 사용 가능)：
  

  =XLOOKUP(H4,B5:B11,E5:E11)

원인 2: col_index 인수가 1 보다 작음

열 인덱스는 반환하려는 값을 포함하는 테이블 배열 내의 열 번호를 지정합니다。 이 인수는 테이블 배열 내 유효한 열에 해당하는 양수여야 합니다。

열 인덱스에 1 보다 작은 값(즉， 0 또는 음수)을 입력하면 VLOOKUP 함수는 테이블 배열 내에서 해당 열을 찾을 수 없습니다。

해결 방법

이 문제를 해결하려면 VLOOKUP 수식의 열 인덱스 인수가 테이블 배열 내 유효한 열에 해당하는 양수인지 확인하세요。

#REF 오류가 반환됨

이 섹션에서는 VLOOKUP 함수가 #REF 오류를 반환하는 한 가지 원인과 이 문제에 대한 해결 방법을 설명합니다。

원인： col_index 인수가 열 수보다 큼

아래 스크린샷에서 볼 수 있듯이 테이블 배열에는 열이 4 개만 있습니다. 그러나 VLOOKUP 수식에서 지정한 열 인덱스는 5 로， 테이블 배열의 열 수보다 큽니다。 따라서 VLOOKUP 함수는 해당 열을 찾을 수 없어 결국 #REF 오류를 반환합니다。

해결 방법

• 올바른 열 번호 지정
  VLOOKUP 수식의 열 인덱스 인수가 table array 의 유효한 열에 해당하는 숫자인지 확인하세요。
• 지정된 열 헤더를 기준으로 자동으로 열 번호 가져오기
  테이블에 열이 많으면 올바른 열 인덱스 번호를 파악하기 어려울 수 있습니다。 이 경우 VLOOKUP 함수 내에 MATCH 함수를 중첩하여 특정 열 헤더를 기준으로 열 위치를 찾을 수 있습니다。

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  참고： 위 수식에서MATCH(「Email」,B5:E5, 0)함수는 날짜 범위 B6:E12 에서 “Email" 열의 열 번호를 가져오는 데 사용됩니다. 여기서 결과는 4 이며, 이는 VLOOKUP 함수의 col_index 로 사용됩니다。

잘못된 값이 반환됨

VLOOKUP 함수가 올바른 결과를 반환하지 않는 경우 다음 원인들 때문일 수 있습니다。

원인 1: 조회 열이 오름차순으로 정렬되지 않음

마지막 인수를 대략적인 일치를 위해TRUE로 설정하거나비워 둔 경우조회 열이 오름차순으로 정렬되어 있지 않으면 결과 값이 잘못될 수 있습니다。

해결 방법

조회 열을 오름차순으로 정렬하면 이 문제를 해결할 수 있습니다。 이를 수행하려면 다음 단계를 따르세요：

1. 조회 열의 데이터 셀을 선택하고데이터탭에서정렬 가장 작은 값부터 가장 큰 값으로을정렬 및 필터그룹에서 클릭하세요。
2. 에서정렬 경고대화 상자에서선택 영역 확장옵션을 선택하고확인을 클릭하세요。

원인 2: 열이 삽입되거나 삭제됨

아래 스크린샷과 같이 원래 반환하려던 값은 테이블 배열의 네 번째 열에 있으므로 col_index 번호를 4 로 지정했습니다。 새 열이 삽입되면서 결과 열이 테이블 배열의 다섯 번째 열이 되어 VLOOKUP 함수가 잘못된 열에서 결과를 반환하게 되었습니다。

해결 방법

다음 두 가지 해결 방법을 참고하세요。

• 반환 열의 위치에 맞게 열 인덱스 번호를 수동으로 변경할 수 있습니다。 여기서 수식은 다음과 같이 변경되어야 합니다。

  =VLOOKUP(H6,B6:F12,5,FALSE)

• 이 예제에서처럼 항상 특정 열(예： Email 열)에서 결과를 반환하려는 경우 다음 수식을 사용하면 테이블 배열에서 열이 삽입되거나 제거되더라도 주어진 열 헤더를 기준으로 자동으로 열 인덱스를 매칭할 수 있습니다。

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

기타 함수 참고 사항

• VLOOKUP 은 왼쪽에서 오른쪽으로에서만 값을 검색합니다。
  조회 값은 가장 왼쪽 열에 있고， 결과 값은 조회 열 오른쪽의 어느 열에도 위치할 수 있습니다。
• 마지막 인수를 비워 두면 VLOOKUP 은 기본적으로 근사치 일치를 사용합니다。
• VLOOKUP 은 대소문자를 구분하지 않습니다。
• 여러 개의 일치 항목이 있는 경우 VLOOKUP 은 테이블 배열의 행 순서에 따라 처음 발견한 첫 번째 일치 항목만 반환합니다。

관련 문서