엑셀 VLOOKUP 함수 인식, 매칭이 잘 안되는 특수문자. 오류 해결방법은?

천만 직장인이 회사에서 엑셀을 사용할 때, 무조건! MUST! 꼭 쓸 수밖에 없는 함수. 

바로 VLOOKUP 함수입니다.

그런데 이 친구도 만능은 아니예요. 

예를 들어 기준값과 혹은 비교하는 값들과 매칭이 잘 안돼서 오류가 발생하는 경우가 간혹 있는데,

오늘은 그 부분에 대해 자세히 알아보겠습니다. :)

 

---

엑셀에서 VLOOKUP 또는 기타 데이터 처리 기능을 사용할 때, 매칭이 잘 안 되는 이유는 주로 특수 문자나 숨겨진 문자 때문입니다. 특히 아래와 같은 특수 문자와 요소들이 문제를 일으킬 가능성이 있습니다.

 

 

1. 공백 문자

종류:

• 일반 공백: ASCII 코드 32번의 일반적인 공백.
• 비표준 공백: 특수 공백 문자(예: 비분리 공백, ASCII 코드 160번).

문제:

• 눈에 보이지 않는 공백이 문자열 앞뒤에 포함되어 있으면 VLOOKUP에서 매칭되지 않을 수 있습니다.

해결:

• TRIM 함수로 불필요한 공백 제거.아래 그림과 같이 B9 셀을 자세히 보시면 '가' 앞에 빈칸으로 띄어쓰기 공백이 들어가 있습니다.이런 공백이 있으면 vlookup 매칭이 안될 수 있어요.
•  

vlookup 공백때문에 오류 발생시

 

 

*** 아스키코드 32번이 뭐지? 하시는 분들은 아래 글을 봐주세요!!

https://only-chart.tistory.com/entry/ASCII-%EC%BD%94%EB%93%9C%EB%9E%80-%EC%95%84%EC%8A%A4%ED%82%A4-%EC%BD%94%EB%93%9C-%EC%89%BD%EA%B2%8C-%EC%84%A4%EB%AA%85%ED%95%B4%EB%93%9C%EB%A6%BD%EB%8B%88%EB%8B%A4

ASCII 코드란? (아스키 코드) 쉽게 설명해드립니다.

 

 

---

 

2. 숨겨진 문자

종류:

• 제어 문자(ASCII 0~31번).
• 보이지 않는 텍스트 방향 제어 문자(예: 좌에서 우 마크 LRM, 우에서 좌 마크 RLM).

문제:

• 보이지 않기 때문에 데이터가 동일해 보여도 VLOOKUP에서 불일치로 간주될 수 있습니다.

해결:

• CLEAN 함수로 제거.

#수식 사용법, 간단하게 아래처럼 사용하시면 됩니다.

=CLEAN(A1)

---

 

3. 특수 기호 및 문자

문제를 일으킬 가능성이 높은 기호:

• 화살표(→, ↔ 등)
• 따옴표(‘, ’, “, ” 등)
• 대시(-)와 하이픈(–): 서로 다르게 인식될 수 있음.
• 점(.): 다른 글자와 결합된 경우 오탐 가능.
• 기타 기호: &, %, $, #, @, *, ^ 등.

해결:

• SUBSTITUTE 함수로 특수 기호 제거하고 VLOOKUP을 사용하시면 됩니다.

 

 

---

 

4. 유니코드 문자

종류:

• 이모지: 😊, 👍, ❄ 등.
• 다국어 문자(예: 일본어, 중국어, 아랍어).
• 동일해 보이는 문자지만 다른 코드(예: 라틴 알파벳의 "a"와 키릴 문자 "а").

문제:

• 유니코드가 다르면 동일한 문자로 보이더라도 매칭되지 않음.

해결:

• 문제가 되는 문자를 UNICODE 함수로 식별 후 제거
• =UNICODE(A1) 이런식으로 사용하시면 됩니다.

unicode 함수

 

---

 

5. 숫자와 문자열의 혼합

문제:

• 숫자로 된 값이 실제로는 텍스트로 저장된 경우(예: 123와 "123"은 다르게 인식됨).

해결:

• VALUE 또는 TEXT 함수로 일관성 있게 변환
• =VALUE(A1)  // 숫자로 변환
  =TEXT(A1, "0")  // 텍스트로 변환

 

이렇게 숫자 데이터이지만 텍스트형식으로 되어 있다면..

아래와 같이 바꾸실 수 있습니다.

엑셀 Value 함수

 

 

---

6. 줄 바꿈 문자

문제:

• 셀 내에서 Alt + Enter로 추가된 줄 바꿈 문자는 매칭에 문제를 일으킬 수 있음.

해결:

• SUBSTITUTE로 제거
• =SUBSTITUTE(A1, CHAR(10), "")

---

 

7. 잘못된 데이터 형식

문제:

• 날짜와 시간이 잘못된 형식으로 입력되거나 저장된 경우.

해결:

• 올바른 형식으로 변환
• =TEXT(A1, "yyyy-mm-dd")

 

---

 

위와 같은 방법들이 있지만!!

 

제가 따로 사용하는 방법도 올려보겠습니다.

 

만약, 여러 데이터들중에서 VLOOKUP을 사용해야 하는데,

 

여러 데이터들 중에 일부 값에 → 이런 화살표 혹은 특수문자가 포함된 데이터가 있어서 오류가 생길 때.. 이런 사례가 있었습니다.

 

예를 들어 특수문자가 포함된 셀에만 함께 들어있는 텍스트가 있다면 저는 if함수를 사용해서 그 텍스트를 먼저 찾아내는 방식으로 사용하기도 합니다.

 

수식을 보여드리면 아래와 같아요.

 

=IF(ISNUMBER(SEARCH("안과장화이팅",Q74)),VLOOKUP("*안과장화이팅*",B:C,2,0),VLOOKUP(Q74,B:C,2,0))

 

위와 같이 특수문자가 들어있는 셀값에만 '안과장화이팅'이라는 텍스트가 함께 있다는 가정하에 위 수식처럼 사용하시면 됩니다.

 

더 궁금하신 사항은 댓글 남겨주세요.

 

감사합니다!!