Excel: XLOOKUP이 #N/A를 반환함

안녕하세요, BlueByte입니다. XLOOKUP이 #N/A를 돌려준다면, 수식 자체는 멀쩡합니다 — 조회 범위를 검색했는데 여러분이 찾는 값을 그냥 찾지 못한 것입니다. 증상은 매칭된 값이 나와야 할 셀에 #N/A(또는 spill된 열 전체가 #N/A)가 뜨는 것입니다. 오늘은 여기서 #N/A가 무슨 뜻인지, 조회가 아무것도 못 찾는 몇 가지 이유, 어느 이유에 걸렸는지 확인하는 법, 원인별 해결, 그리고 날 오류 대신 쓸모 있는 메시지를 보여주는 법까지 하나씩 짚어보겠습니다.

XLOOKUP의 #N/A가 뜻하는 것

#N/A는 "사용 가능한 값이 없음"을 뜻합니다 — 조회는 실행됐지만 일치를 찾지 못한 것입니다. XLOOKUP은 기본적으로 정확히 일치를 사용하며, Microsoft 문서는 분명히 밝힙니다: 유효한 일치가 없고 if_not_found가 생략되면 #N/A가 반환됩니다. 전체 서명은 다음과 같습니다:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

match_mode를 생략하면 0(정확히 일치)이 기본값이므로, lookup_array 안에 lookup_value와 정확히 같은 값이 없으면 #N/A가 나옵니다. 이는 구조가 깨진 수식과 다릅니다 — 이 수식은 정상적으로 실행됐고, 값이 거기 없다고 알려주는 것입니다.

정확히 일치 조회가 아무것도 못 찾는 이유

정확히 일치의 #N/A는 다음 중 하나에서 옵니다:

• 값이 조회 범위에 정말로 없음.
• 값은 있지만 한쪽에 여분의 공백이나 비인쇄 문자가 있어 두 문자열이 같지 않음.
• 한쪽은 숫자가 텍스트로 저장되고 다른 쪽은 실제 숫자임 — "12345"는 12345와 절대 같지 않음.
• lookup_array를 엉뚱한 열에 두었거나, 범위가 모든 행을 덮지 못함.

이들 각각은 match_mode 0이 수행하는 정확히 일치 검사를 깨뜨립니다. 어느 것도 XLOOKUP의 버그가 아닙니다.

몇 가지 빠른 점검으로 어느 이유인지 확인하기

추측하지 마세요 — 보조 수식 몇 개가 원인을 갈라줍니다. 값이 아예 있는지, 텍스트 길이가 다른지(길이 차이는 숨은 공백을 뜻함) 확인합니다:

=COUNTIF(A:A, D2)
=LEN(D2) & " vs " & LEN(A2)

COUNTIF가 0을 돌려주면 값이 정말 없는 것입니다(또는 타입·공백 불일치가 가리고 있는 것). 같아 보이는 텍스트인데 두 LEN 값이 다르면 여분 공백이 있는 것입니다. 그다음 양쪽의 데이터 타입을 확인합니다:

=ISNUMBER(D2) & " / " & ISNUMBER(A2)

한쪽이 TRUE, 다른 쪽이 FALSE라면 숫자와 텍스트를 비교하고 있는 것입니다 — 그게 #N/A의 원인입니다.

조회 값을 정리해서 고치기

여분 공백이나 비인쇄 문자에는 조회 값을 TRIM(여분 공백 제거)으로, 필요하면 CLEAN(비인쇄 문자 제거)으로 감쌉니다:

=XLOOKUP(TRIM(D2), A2:A100, B2:B100)

예상 결과: #N/A를 돌려주던 조회가 이제 매칭된 값을 돌려줍니다. TRIM(D2)가 저장된 키와 정확히 같아졌기 때문입니다. 원본 데이터에 공백이 있다면 수식마다 감싸는 대신 그 열을 한 번에 정리하세요.

숫자가 텍스트로 저장된 불일치 고치기

한쪽은 텍스트, 다른 쪽은 실제 숫자라면 타입을 같게 맞춥니다. 텍스트 조회 값을 숫자로 바꾸려면:

=XLOOKUP(VALUE(D2), A2:A100, B2:B100)

반대로 키가 텍스트이고 조회 값이 숫자라면 D2 & ""로 반대 방향으로 맞춥니다. 오래 가는 해결은 키 열을 한 가지 타입으로 저장하는 것입니다 — 열을 선택하고 데이터 ▸ 텍스트 나누기 ▸ 마침으로 텍스트 숫자를 실제 숫자로 그 자리에서 변환하세요.

#N/A 대신 친절한 메시지 보여주기

빈 결과가 정당하다고 — 값이 정말 없을 수 있다고 — 확인했다면, 시트에 날 #N/A를 남기지 마세요. XLOOKUP에는 바로 이걸 위한 if_not_found 인수가 있습니다:

=XLOOKUP(D2, A2:A100, B2:B100, "Not found")

일치가 없을 때 #N/A 대신 Not found를 돌려줍니다. 다시 쓰기 곤란한 기존 수식은 IFNA로 감싸세요. IFNA는 #N/A만 잡고 다른 결과는 그대로 통과시킵니다:

=IFNA(XLOOKUP(D2, A2:A100, B2:B100), "Not found")

여기서는 IFERROR보다 IFNA를 쓰세요: IFERROR는 #REF!, #VALUE!, #DIV/0!까지 감춰 진짜 수식 버그를 가릴 수 있습니다.

실제 사례: 같아 보이지만 아닌 주문 ID

주문 ID를 매칭하는 중입니다. =XLOOKUP(D2, A:A, B:B)가 #N/A를 돌려주는데, D2와 A47 둘 다 ORD-1001로 보입니다. =LEN(D2)&" vs "&LEN(A47)을 실행하니 8 vs 9가 나옵니다 — A47이 내보내기 과정에서 끝에 공백을 얻은 것입니다. 보조 열에 =TRIM(A2)를 넣어 정리된 열을 조회 대상으로 삼으니, #N/A가 올바른 고객명으로 바뀝니다. 이제 길이 점검은 8 vs 8로, 키가 드디어 일치함을 확인해 줍니다.

일치를 검증하고 재발을 막기

시작할 때 쓴 점검으로 고침을 확인하세요 — COUNTIF는 이제 1(이상)을 돌려주고 두 LEN 값은 일치해야 합니다. 재발을 막으려면: 키 열을 한 가지 타입으로 유지하고, 들어오는 데이터를 TRIM하며, 앞으로 값이 없을 때 빨간 오류 대신 텍스트가 뜨도록 if_not_found 메시지를 상시 넣어 두세요. 키가 깨끗하다면 match_mode를 기본값 0으로 두고 근사가 아닌 정확히 일치에 기대세요.

#N/A가 #REF! 및 조용한 VLOOKUP과 다른 점

#N/A는 조회가 실행됐지만 아무것도 못 찾았다는 뜻입니다. #REF!는 다릅니다: 수식 안의 셀 참조가 더 이상 존재하지 않는 것 — 삭제된 행이나 열 — 을 가리켜, 수식이 입력 자체를 해석하지 못하는 것입니다. 그리고 #N/A의 반대를 조심하세요: VLOOKUP은 네 번째 인수를 TRUE(근사 일치)로 두면 정확히 일치가 없을 때 #N/A 대신 가장 가까운 값을 돌려줍니다 — 오류 없이 틀린 답이 나오는 것입니다. XLOOKUP이 정확히 일치를 기본으로 두는 것이 더 안전한 이유가 바로 이것입니다. 조용히 추측하는 대신 #N/A를 드러내 주니까요.