엑셀 VLOOKUP 함수에서 ~, *, ? 특수문자가 검색되지 않을 때 해결하는 방법

 

엑셀을 사용하다 보면, VLOOKUP 함수를 이용해 데이터를 검색할 때 특정 값이 검색되지 않는 문제를 겪는 경우가 있습니다. 특히 검색하려는 텍스트에 특수문자 ~, *, ? 가 포함되어 있을 경우 의외의 오류가 발생할 수 있습니다.

이번 포스팅에서는 엑셀에서 VLOOKUP 함수로 특수문자가 포함된 텍스트를 정확히 검색하는 방법에 대해 알려드립니다. 이 글을 끝까지 읽으시면 엑셀에서 왜 ~ 문자가 검색되지 않는지, 그리고 어떻게 해결할 수 있는지를 명확히 이해하실 수 있습니다.

---

✅ 엑셀 VLOOKUP에서 특수문자 검색 문제: 왜 발생할까?

엑셀 함수에는 **와일드카드(wildcard)**라는 개념이 있습니다.
다음은 엑셀에서 자주 사용하는 와일드카드입니다:

문자 의미

*	임의의 여러 문자 (0개 이상)
?	임의의 한 문자
~	*, ?, ~ 등을 일반 문자로 해석하도록 만드는 escape 문자

이러한 특수문자들은 엑셀에서 고유한 기능을 하기 때문에, VLOOKUP에서 검색어로 그대로 입력할 경우 다르게 해석되어 오류가 발생할 수 있습니다.

🔍 예시 상황

다음과 같이 A열에 데이터가 입력되어 있다고 가정해봅시다:

A열 B열

test~1	값1
test*2	값2
test?3	값3

이제 VLOOKUP을 사용하여 test~1이라는 값을 찾고자 할 때, 아래와 같은 수식을 입력할 수 있습니다:

=VLOOKUP("test~1", A:B, 2, FALSE)

하지만 이 수식은 올바른 결과를 반환하지 못합니다. 이유는 ~ 문자가 엑셀에서는 escape 문자로 해석되며, 뒤따르는 문자를 일반 문자로 바꾸려는 기능을 수행하기 때문입니다. 즉, 엑셀은 ~1을 문자 그대로 인식하지 않고 escape 처리를 시도합니다. 그 결과 정확히 일치하는 텍스트를 찾지 못하게 되는 것입니다.

---

💡 정확하게 ~, *, ? 문자로 검색하려면?

해결 방법은 간단합니다. ~, *, ?를 일반 문자로 검색하려면 엑셀이 이 문자를 escape 문자로 인식하지 못하게 만들어야 합니다. 이를 위해서는 다음과 같이 변환해줘야 합니다:

• ~ → ~~
• * → ~*
• ? → ~?

즉, 특수문자를 일반 텍스트처럼 다루기 위해서는 이중으로 처리하거나 escape 문자를 붙여야 하는 것입니다.

---

✅ SUBSTITUTE 함수로 특수문자 치환하기

📌 해결 수식 예시

=VLOOKUP(SUBSTITUTE("test~1", "~", "~~"), A:B, 2, FALSE)

🔎 함수 설명

• SUBSTITUTE("test~1", "~", "~~") : 문자열에서 ~ 기호를 ~~로 바꾸어 줍니다.
• VLOOKUP(...) : 변환된 문자열을 사용해 정확히 일치하는 값을 검색합니다.

이 방식은 단순한 VLOOKUP과 달리 엑셀이 오해하지 않도록 문자를 재구성해주는 것이 핵심입니다.

---

📌 동적으로 검색어를 처리하고 싶다면?

위의 예시처럼 고정된 텍스트가 아닌, 셀 참조 방식으로 검색어를 입력받아 처리하고 싶다면 아래와 같은 수식을 사용할 수 있습니다.

🧩 동적 참조 예제

=VLOOKUP(SUBSTITUTE(C1, "~", "~~"), A:B, 2, FALSE)

• 이 수식은 C1 셀에 입력된 값에서 ~를 ~~로 바꿔 검색합니다.
• 사용자가 직접 검색어를 바꿔가며 실시간 검색할 수 있도록 유연하게 처리할 수 있습니다.

---

🎯 *, ?도 동시에 처리하려면?

앞서 설명드린 것처럼, *와 ?도 엑셀에서는 와일드카드로 인식되므로 이를 일반 문자처럼 검색하고자 할 때는 추가적인 SUBSTITUTE 처리가 필요합니다.

=VLOOKUP(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(C1, "~", "~~"),
    "*", "~*"),
  "?", "~?"),
A:B, 2, FALSE)

💡 함수 순서 중요!

SUBSTITUTE 함수는 중첩해서 사용할 수 있으며, 바꾸고자 하는 특수문자의 우선순위를 지정할 수 있습니다. 일반적으로 다음 순서로 처리하면 안전합니다:

1. ~ → ~~
2. * → ~*
3. ? → ~?

이 방식은 특히 사용자가 특수문자를 포함한 키워드를 입력할 가능성이 있는 시스템(예: 고객명, 파일명 등)에 유용하게 사용됩니다.

---

✅ 실전 예제와 함께 정리

🔍 문제 데이터 예시

A열 B열

report~v1	완료
report*log	실패
error?log	대기중

📋 사용 수식

=VLOOKUP(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(D1, "~", "~~"),
    "*", "~*"),
  "?", "~?"),
A:B, 2, FALSE)

• D1 셀에 사용자가 검색어를 입력
• VLOOKUP은 변환된 문자열을 기준으로 정확히 일치하는 값을 찾아 반환

---

✅ 요약: 특수문자 검색 시 정리표

특수문자 의미 처리 방법

~	escape 문자	~ → ~~
*	임의의 여러 문자	* → ~*
?	임의의 한 문자	? → ~?

이렇게 변환해주면 VLOOKUP 함수로도 정확한 검색이 가능합니다.

---

✨ 마무리하며

엑셀에서 VLOOKUP은 매우 강력한 함수지만, 특수문자 처리에 대해 정확히 이해하지 못하면 원하는 결과를 얻기 어렵습니다. 특히 ~, *, ? 같은 문자가 포함된 데이터를 다룰 때는 반드시 이들 문자의 동작 원리를 이해하고 적절히 escape 처리를 해주어야만 안정적인 결과를 얻을 수 있습니다.

이 포스팅을 통해:

• 엑셀에서 ~, *, ?의 역할을 명확히 이해하고,
• VLOOKUP에서 정확한 검색을 위한 방법을 익히며,
• 실무에서도 바로 적용 가능한 유용한 팁까지 얻어가셨기를 바랍니다.