EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP For Text

VLOOKUP For Text

Madhuri Thakur
Article byMadhuri Thakur

VLOOKUP For Text

Table of Contents
  • Introduction to VLOOKUP for Text in Excel
    • Example: #N/A Error due to Numbers Stored as Text
  • How to Use VLOOKUP for Text?
    • Example #1 – Paste Special Method
    • Example #2 – VALUE Function
    • Example #3 – TEXT Function

Introduction to VLOOKUP For Text in Excel

While using VLOOKUP in Excel to find any particular value, it is necessary that the format of the data in both the lookup table and the lookup value is the same. Mostly, we use VLOOKUP in Excel to search only numerical data, but if you are using both text and number formats, you can get an #N/A error.

It happens when the lookup value and the data table have different formats, i.e., one is in number, and the other is in text. Here, VLOOKUP shows a #N/A error because it cannot find a match. So, when we use it to find text-based values, you can use VLOOKUP for text to fix the error.

Before we learn the methods to solve this error, let us first understand how this error occurs with the help of an example.

ADVERTISEMENT
Popular Course in this category
MS Excel Advanced Course Bundle - 42 Courses in 1 | 15 Mock Tests

Example: #N/A Error due to Numbers Stored as Text

Here, we have two tables. Table 1 is the main table with city codes, and their specific pin codes, and Table 2 is our lookup array. Let us try to find the pin code for the city code “415930”.

Example 1

  • Enter the following VLOOKUP formula in cell E3.
=VLOOKUP(D3,$A$3:$B$14,2,0)
  • Press Enter and then drag the formula until E14.

Example 2

Result:
When we use the VLOOKUP Function in Column E, it shows a “#N/A” error. It happens because the lookup_value (Column D) is in text format, and the table_array (Columns A & B) is in numeric format.

Example 3

Therefore, to avoid this error, make sure the lookup value and the table values are in the same format.

How to Use VLOOKUP for Text?

To solve this #N/A error, we must make sure that the data table and lookup values are in the same format, i.e., either in text or number format. The Excel functions to correct this error are as follows:

  1. Paste Special feature
  2. VALUE function
  3. TEXT function (for reverse conversion)
You can download this VLOOKUP For Text Excel Template here – VLOOKUP For Text Excel Template

Example #1 – Convert Text to Numeric Values Using the Paste Special Method

The values in Column D look like numbers but are in text format. In this example, we will convert values in column D to number format by using the Paste Special method.

Example 1-1

Solution:
Step 1: Select “Cell G3” and enter number 1.

Example 1-2

 Step 2: Copy “Cell G3” and select the range from Cell “D3 to D14”, as shown below.

VLOOKUP For Text-Example 1-3

Step 3: Right-click the selected range and select the “Paste Special” option, as shown below. You can also use the keyboard shortcut “Ctrl +Alt +V” to open the “Paste Special” window.

Example 1-4

Step 4: Select the “All” option under “Paste” and “Multiply” under the “Operation” section.

Note: The “Multiply” option from the paste special window multiplies the value 1 (the one we copied) with the values in column D. This way, by multiplying a number to the text format, we convert column D into a numeric format.

VLOOKUP For Text-Example 1-5

Step 5: Click on the OK button.

Result:
The format of Column D will change from text to number after applying the “Paste Special” method. Therefore, the VLOOKUP Function will give the correct match instead of the #N/A error.

Example 1-6

Example #2 – Convert Text to Numeric Values Using the VALUE Function

We use the VALUE() function within the VLOOKUP function to directly change the format of a text-based cell into a numeric value.
Let us take the same data as Example #1.

VLOOKUP For Text-Example 2-1

Solution:

Step 1: Select “Cell E3” and add the VALUE Function within the VLOOKUP function as follows:

=VLOOKUP(VALUE(D3),$A$3:$B$14,2,0)

Example 2-2

Step 2: Press “Enter”.

The formula will display “U_362” in Cell E3.

Example 2-3

Step 3: Drag the cell downwards to copy the formula in cell E3 to E14.
The formula will now display the actual pincode instead of a #N/A error.

Result:
The VALUE function changed the text form of lookup_value (Column D) into the numeric form so that the VLOOKUP function could fetch accurate results.

Example 2-4

Example #3 – Convert Numeric to Text Values Using the TEXT Function

In all the above examples, the lookup_value (Column D) was in text format, and the table_array (Columns A & B) was in a numeric format. Hence, we changed the format for the lookup_value (Column D) from text to number.

Now, let us see how to change the data format from numeric to text format.

Consider the same data from the above example. But this time, your original data, i.e., Column A, is in text format, and the lookup_value (Column D) is in numeric format. So, we have to use the TEXT function to convert the lookup_value from the number format into text format.

Example 3-1

Solution:
Let us change the data formatting by simply adding the TEXT Function.

Step 1: Select “Cell E3” and enter the formula:

=VLOOKUP(TEXT(D3,0),$A$3:$B$14,2,0)

Example 3-2

Step 2: Press “Enter”.

The formula displays “U_362” in “Cell E3”.

VLOOKUP For Text-Example 3-3

Step 3: Drag “Cell E3” downwards. The formula will display the accurate result throughout Column E.

VLOOKUP For Text-Example 3-4

Result:
Here, the TEXT function has first changed the lookup_value (D3) number format into text format.

Frequently Asked Questions (FAQs)

Q1. Does VLOOKUP work with alphanumeric data?
Answer: Yes, VLOOKUP can handle alphanumeric data in Excel. It’s a function that finds a specific value from a huge data table. It doesn’t matter if the values are a mix of letters and numbers. Just make sure the data formatting is consistent.

Q2. Why won’t VLOOKUP work with text?
Answer: VLOOKUP for text can give an error in the following situations:

  • If data is present in both text and number format.
  • When the value you are looking for does not match the values in the original data.
  • If there are spelling mistakes or the data is in the wrong order.
  • If the table is not in ascending order before applying the function.
  • If there are extra spaces in the original data or lookup data.

Recommended Articles

This EDUCBA article explains how to use VLOOKUP for text using various Excel functions. Here, we have mentioned methods that you can use to solve the #N/A error using practical examples and an Excel template. Read the following articles to learn more.

  1. VLOOKUP Tutorial in Excel
  2. Text Formula In Excel
  3. VLOOKUP Examples in Excel
  4. VLOOKUP with Different Sheets
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VLOOKUP For Text Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download VLOOKUP For Text Excel Template

EDUCBA

डाउनलोड VLOOKUP For Text Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW