VLOOKUP이 가끔 실패하는 이유

VLOOKUP은 안정적이지만, 예측 가능한 몇 가지 방식으로 실패합니다. 규칙을 알고 나면 대부분의 오류는 미스터리하지 않습니다.

이 강의에서는 조회가 제대로 작동하지 않는 일반적인 원인과 각각의 문제를 정확히 해결하는 방법을 배웁니다. 이를 알면 혼란스러운 #N/A 및 #REF! 오류도 빠르고 쉽게 해결할 수 있습니다.

VLOOKUP은 표_배열의 가장 왼쪽 열만 검색할 수 있으며 값을 오른쪽에서 반환합니다. 값을 조회한 뒤 그 왼쪽에 있는 항목을 반환할 수는 없습니다.

식별자가 C열에 있고 원하는 이름이 A열에 있다면 VLOOKUP으로는 왼쪽으로 거슬러 올라갈 수 없습니다. 열의 순서를 바꿔 검색 열을 첫 번째로 만들거나, 어느 방향으로든 검색할 수 있는 INDEX-MATCH 또는 XLOOKUP을 사용하십시오.

열_인덱스_번호는 시트가 아니라 표_배열의 왼쪽부터 셉니다. 시트의 열 문자를 숫자로 사용하는 것이 흔한 실수입니다.

범위가 C1:F10이고 F열을 원한다면, F열은 범위에서 네 번째 열이므로 인덱스는 6이 아니라 4입니다. 잘못된 가장자리부터 세면 잘못된 항목이 반환되며, 숫자가 범위의 너비를 초과하면 #REF! 오류가 발생합니다.

열_인덱스_번호가 표_배열의 열 개수보다 크면 VLOOKUP은 #REF!를 반환합니다.

예를 들어 3열 범위 A1:C10에서 5번째 열을 요청하는 것은 불가능합니다:

필요한 열을 포함하도록 표_배열의 너비를 넓히거나, 범위 안에 실제로 존재하는 열 번호로 인덱스를 수정하면 해결할 수 있습니다.

네 번째 인수를 생략하면 기본값이 TRUE(근사 일치)가 됩니다. 정렬되지 않은 목록에서는 오류 대신 인접한 잘못된 값을 조용히 반환하므로 알아차리기 어렵습니다.

해결 방법은 간단하며 습관으로 삼아야 합니다. 정확한 조회에는 항상 FALSE를 추가하십시오.

조회 값이 "A100"이면 뒤에 공백이 있는 "A100 "과 일치하지 않습니다. 가져온 데이터에는 이러한 보이지 않는 차이가 가득합니다.

증상은 값이 분명히 존재하는데도 #N/A가 표시되는 것입니다. 양쪽을 TRIM으로 정리해 불필요한 공백을 제거하십시오:

조회 값은 숫자 100인데 표에는 코드가 텍스트 "100"으로 저장되어 있거나 그 반대라면, 서로 일치하지 않아 #N/A가 표시됩니다.

텍스트임을 나타내는 작은 초록색 삼각형이나 왼쪽 정렬된 숫자를 찾아보십시오. 텍스트를 VALUE()로 감싸 숫자로 변환하거나, 숫자에 빈 문자열을 &""로 연결해 텍스트로 만들면 해결할 수 있습니다. 이렇게 하면 양쪽의 유형이 같아집니다.

표_배열을 고정하지 않으면 수식을 아래로 복사할 때 범위가 데이터에서 벗어납니다. 2행의 A1:C100은 3행에서 A2:C101이 되고, 그다음에는 A3:C102가 되어 중간의 행을 놓치게 됩니다.

절대 참조를 사용해 해결하십시오. 그러면 조회 값만 이동하고 표는 고정된 상태로 유지됩니다:

각 오류는 원인을 가리킵니다:

• #N/A - 값을 찾지 못함(불일치, 공백, 잘못된 유형 또는 실제로 값이 없음)
• #REF! - 열_인덱스_번호가 범위보다 크거나 참조된 셀이 삭제됨
• #VALUE! - 인수가 잘못된 유형임. 예를 들어 열 인덱스가 음수이거나 0임
• #NAME? - 함수 이름의 철자가 잘못됨. 예: VLOOKP

오류를 그 의미와 연결하면 문제의 절반은 이미 해결한 셈입니다.

문제를 해결하는 동안 조회를 감싸서 사용자가 원시 오류 대신 명확한 메시지를 보게 할 수도 있습니다. IFERROR는 모든 오류를 포착하고 그 대신 사용자가 지정한 텍스트를 반환합니다.

이 방법은 근본 원인을 해결하지 않으므로 조회가 실패한 이유를 이해한 후에만 사용하십시오. 오류를 너무 일찍 숨기면 실제 데이터 문제를 가릴 수 있습니다.

조회가 예상대로 작동하지 않을 때는 다음의 빠른 확인 목록을 따라가 보십시오:

• 검색 값이 범위의 첫 번째 열에 있습니까?
• 열_인덱스_번호를 범위의 왼쪽 가장자리부터 세었으며 범위의 너비 안에 있습니까?
• 정확히 일치하는 검색을 위해 FALSE를 추가했습니까?
• 양쪽의 유형이 같고(텍스트와 숫자 중 같은 유형) 불필요한 공백이 없습니까?
• 표_배열이 달러 기호로 고정되어 있습니까?

이 목록을 위에서 아래로 따라가면 대부분의 조회 실패를 몇 초 안에 해결할 수 있습니다.

이 실패한 조회의 원인을 진단해 보십시오.

일반적인 원인과 해결 방법은 다음과 같습니다:

• 왼쪽 열의 제한 - 열 순서를 바꾸거나 INDEX-MATCH / XLOOKUP을 사용하십시오
• 잘못되었거나 범위를 초과한 열_인덱스_번호 - 범위의 왼쪽 가장자리부터 세고 범위를 넓히십시오
• FALSE 누락 - 식별자에는 항상 정확히 일치하는 검색을 설정하십시오
• 공백 및 텍스트와 숫자의 불일치 - TRIM으로 정리하고 VALUE 또는 &""로 변환하십시오
• 고정되지 않은 표 - $를 사용해 범위가 움직이지 않게 하십시오

오류 코드를 읽고 원인과 연결한 다음 해결 방법을 적용하십시오. 이제 안정적인 조회를 위한 도구를 모두 갖추었습니다.