엑셀 XLOOKUP #N/A 오류: 값이 있는데 못 찾을 때

1텍스트 앞뒤에 공백이 있어요

가장 흔한 원인이에요. 다른 프로그램에서 내보낸 데이터나 손으로 입력한 목록에서 자주 생겨요.

이렇게 확인해요

글자 수를 비교해 보세요. 사과 는 눈에는 두 글자지만 =LEN(A5)는 3을 돌려줘요. 셀을 클릭해 수식 입력줄에서 커서가 공백 뒤에 있는지 봐도 돼요.

해결 방법

수식 안에서 찾을 값과 찾을 범위를 모두 TRIM으로 정리하세요. XLOOKUP이 되는 버전이라면 모두 돼요.

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

데이터 자체를 고치려면 보조 열에 =TRIM(A2)를 넣고 아래로 채운 뒤, 결과를 복사해 원래 열에 홈 > 붙여넣기 > 값 붙여넣기로 덮어쓰세요. 그다음 보조 열은 지우면 돼요.

예방 팁. 가져온 목록은 조회 수식을 만들기 전에 TRIM으로 한 번 정리해 두세요. 그러면 그 목록을 쓰는 수식마다 TRIM을 덧붙일 필요가 없어요.

2숫자가 텍스트로 저장돼 있어요(또는 그 반대)

사번, 우편번호, 송장 번호 같은 코드는 텍스트로 들어오는 경우가 많아요. XLOOKUP은 숫자 1002와 텍스트 "1002"를 같은 값으로 보지 않아요.

이렇게 확인해요

텍스트 숫자는 셀 왼쪽에 붙어 있고, 모서리에 작은 초록색 삼각형이 보이는 경우가 많아요. =ISTEXT(A2)가 TRUE면 텍스트예요. 찾을 값은 =ISNUMBER(E2)로 확인하세요.

해결 방법

찾을 값의 형식을 목록과 맞추면 돼요. 목록이 텍스트인데 숫자로 찾는다면 빈 문자열을 붙여 텍스트로 바꾸세요.

=XLOOKUP(E2&"",A2:A100,B2:B100)

반대로 목록이 숫자인데 찾을 값이 텍스트라면 빼기 기호 두 개로 숫자로 바꾸세요.

=XLOOKUP(--E2,A2:A100,B2:B100)

열 전체를 숫자로 바꾸려면 범위를 선택하고 경고 아이콘을 눌러 숫자로 변환을 고르세요. 데이터 > 텍스트 나누기를 연 뒤 바로 마침을 눌러도 돼요.

00123처럼 앞에 0이 있는 코드는 양쪽 모두 텍스트로 두세요. 숫자로 바꾸면 앞의 0이 사라져요.

예방 팁. 코드 열은 텍스트로 쓸지 숫자로 쓸지 한 번 정하고, 입력하거나 붙여 넣기 전에 셀 서식을 먼저 맞춰 두세요.

3줄 바꿈 없는 공백(CHAR(160))과 보이지 않는 문자

웹 페이지, PDF, 일부 회계 프로그램에서 복사한 텍스트에는 160번 문자인 "줄 바꿈 없는 공백"이 섞여 있는 경우가 많아요. 일반 공백처럼 보이지만 TRIM으로는 지워지지 않아요.

이렇게 확인해요

TRIM을 써도 안 되고 =LEN(A5)가 여전히 길어요. 마지막 글자가 이 공백이면 =CODE(RIGHT(A5))가 160을 돌려줘요.

해결 방법

160번 문자를 일반 공백으로 바꾼 다음 TRIM으로 정리하세요.

=XLOOKUP(TRIM(E2),TRIM(SUBSTITUTE(A2:A100,CHAR(160)," ")),B2:B100)

셀 안 줄 바꿈 같은 인쇄되지 않는 문자(코드 0~31)는 CLEAN으로 지워요. 세 가지를 한 번에 정리하는 보조 열 수식은 다음과 같아요.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

찾기 및 바꾸기(Ctrl+H)로도 지울 수 있어요. 찾을 내용 칸을 클릭하고 Alt 키를 누른 채 숫자 키패드로 0160을 입력한 뒤, 바꿀 내용은 비워 두고 모두 바꾸기를 누르세요.

4찾을 범위가 틀렸거나 수식을 복사하면서 밀렸어요

이렇게 확인해요

수식 셀을 클릭하면 엑셀이 각 범위를 색 테두리로 보여 줘요. lookup_array가 찾는 값이 들어 있는 열인지, 옆 열을 잘못 잡지 않았는지 확인하세요. 수식을 아래로 채운 뒤 아래쪽 행에서만 #N/A가 나온다면 범위가 같이 밀려 내려간 거예요.

해결 방법

$ 기호로 범위를 고정하세요. 참조에 커서를 두고 F4를 누르면 돼요.

=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100)

또는 삽입 > 표로 목록을 표로 만들고 열 이름으로 참조하세요. 표 참조는 밀리지 않아요.

=XLOOKUP(E2,제품[품목],제품[가격])

lookup_array와 return_array의 크기가 다르면 #N/A가 아니라 #VALUE!가 나와요.

5match_mode나 search_mode가 의도와 다르게 설정돼 있어요

XLOOKUP은 따로 지정하지 않으면 정확히 일치하는 값을 찾아요. 수식에 다섯 번째나 여섯 번째 인수가 있다면 확인해 보세요.

이렇게 확인해요

=XLOOKUP(E2,A2:A100,B2:B100,,1)처럼 생겼거나 ,2) 또는 ,-2)로 끝나는 수식이에요.

• match_mode가 -1이나 1이면 정확한 값이 없을 때 바로 아래나 위의 값을 돌려줘요. 오류 대신 엉뚱한 값이 나와요.
• search_mode가 2나 -2면 이진 검색이에요. 데이터가 오름차순(2)이나 내림차순(-2)으로 정렬돼 있어야 해요. 정렬이 안 된 데이터에서는 있는 값도 못 찾고 #N/A를 낼 수 있어요.

해결 방법

필요 없는 인수를 지우거나 match_mode를 0(정확히 일치)으로 지정하세요.

=XLOOKUP(E2,A2:A100,B2:B100,"없음",0)

6목록에 정말 그 값이 없어요

오타가 있거나, "(주)"와 "주식회사"처럼 표기가 다르거나, 새 항목이 아직 목록에 없는 경우예요.

해결 방법

if_not_found 인수를 쓰면 없는 값일 때 오류 대신 알기 쉬운 메시지가 나와요.

=XLOOKUP(E2,A2:A100,B2:B100,"없음")

비슷한 값을 찾고 싶다면 와일드카드(match_mode 2)를 쓰세요. E2의 글자가 들어 있는 첫 번째 항목을 찾아요.

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"없음",2)