Excel VLOOKUP 함수

Excel VLOOKUP 함수는 테이블이나 범위의 첫 번째 열에서 지정된 값을 수직으로 검색하고 동일한 행의 다른 열에서 해당 값을 반환하는 강력한 도구입니다. 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 번호1005가 포함된 셀 G6을 참조합니다.

=VLOOKUP(G6

단계3: 테이블 배열 지정

다음으로, 찾고자 하는 값과 반환하려는 값을 포함하는 셀 범위를 지정합니다. 이 경우 범위 B6:E12를 선택합니다. 이제 수식은 다음과 같이 나타납니다:

=VLOOKUP(G6,B6:E12

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

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

그런 다음 반환할 값을 원하는 열을 지정합니다.

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

=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이 잘못된 결과를 반환할 수 있습니다.
  • 정확한 일치 - 이 인수를 FALSE 또는0으로 설정하세요.

예제

이 섹션은 VLOOKUP 함수에 대한 보다 포괄적인 이해를 돕기 위한 몇 가지 예제를 보여줍니다.

예제1: VLOOKUP에서 정확한 일치 vs. 대략적인 일치

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은 연결할 이름 열의 첫 번째 이름이고, D6은 연결할 부서 열의 첫 번째 부서입니다.
   • 이 두 셀의 값은 중간에 공백을 두고 연결됩니다.
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 함수 사용 (Excel365, Excel2021 및 이후 버전에서 사용 가능)
  

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

이유2: 조회 값이 조회 열에서 발견되지 않음 (정확한 일치)

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

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

해결책

이 오류를 수정하려면 다음 해결책 중 하나를 시도할 수 있습니다.

• VLOOKUP이 조회 값보다 작은 다음 큰 값을 검색하도록 하려면 마지막 인수 FALSE(정확한 일치)를 TRUE(대략적인 일치)로 변경하세요. 자세한 내용은 예제1: VLOOKUP을 사용한 정확한 일치 vs. 대략적인 일치를 참조하세요.
• 마지막 인수를 변경하지 않고 조회 값이 발견되지 않은 경우 알림을 받으려면 VLOOKUP 함수를 IFERROR 함수 내에 포함할 수 있습니다:
  

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

이유3: 조회 값이 조회 열의 가장 작은 값보다 작음 (대략적인 일치)

아래 스크린샷에 표시된 바와 같이, 대략적인 일치 조회를 수행하고 있습니다. 찾고자 하는 값(이 경우 ID 번호1001)은 조회 열의 가장 작은 값1002보다 작으므로 VLOOKUP은 #N/A 오류를 반환합니다.

해결책

여기 두 가지 해결책이 있습니다.

• 조회 값이 조회 열의 가장 작은 값보다 크거나 같도록 하세요.
• Excel이 조회 값이 발견되지 않았음을 알리도록 하려면 VLOOKUP 함수를 IFERROR 함수 내에 포함하세요:
  

  =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에서 첫 번째 결과를 얻은 후 F6에서 F7로 VLOOKUP 수식을 드래그하면 #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 함수 (Excel365, Excel2021 및 이후 버전에서 사용 가능):
  

  =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 수식의 열 인덱스 인수가 테이블 배열의 유효한 열에 해당하는 숫자인지 확인하세요.
• 지정된 열 헤더를 기반으로 열 번호 자동 가져오기
  테이블에 많은 열이 포함되어 있는 경우 올바른 열 인덱스 번호를 결정하는 데 어려움을 겪을 수 있습니다. 여기서는 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은 table_array의 행 순서에 따라 첫 번째 일치 항목만 반환합니다.

관련 기사