EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP For Text
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

VLOOKUP For Text

By Jeevan A YJeevan A Y

VLOOKUP For Text

VLOOKUP for Text (Table of Contents)

  • Introduction to VLOOKUP For Text
  • Examples to Apply VLOOKUP Formula

Introduction to VLOOKUP For Text

VLOOKUP has served us in several ways in our daily work. We all know VLOOKUP can fetch the data based on the lookup value from the data table. LOOKUP value should be the same as it is in the data table, then only VLOOKUP can give us the value correctly; otherwise, it will return the error value as a result.

One such error case is when the numerical values are stored as text values in excel. This article will see how to deal with this kind of case and still get the job done.

Examples to Apply VLOOKUP Formula

We all know if the numerical values appear in excel are all based on the format applied to those numerical values.

Start Your Free Excel Course

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

You can download this VLOOKUP For Text Excel Template here – VLOOKUP For Text Excel Template

Example #1 – Numbers Stored as Text Value

For example, look at the below image.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,639 ratings)

VLOOKUP For Text 1-1

In Column B, Numerical Values are stored as Numbers, and in Column C, Numbers are stored as text values so we can get the total of the numbers. Let’s apply the SUM formula in cell B5.

VLOOKUP For Text 1-2

After applying the formula, we will get the result.

VLOOKUP For Text 1-3

Now let’s apply the formula in cell C5.

VLOOKUP For Text 1-4

After applying the formula, the result is shown below.

VLOOKUP For Text 1-5

This is the scenario in excel when the numbers stored as text values.

Example #2 – VLOOKUP Function to Extract the Data

In table 1, we have city code and pin code against city code. In table 2, we have only City Code and based on this; we need to extract the Pin code from table 2.

VLOOKUP For Text 2-1

The first thing that comes to our mind is the VLOOKUP function; yes, you are correct; we can fetch the data by applying the VLOOKUP function. Let’s apply the VLOOKUP function to extract the Pin Code for each city.

VLOOKUP For Text 2-2

We have got an error value of #N/A against all the city codes. This seems to be unrealistic at this point in time, isn’t it? One of the common problems in these cases is numbers are stored as text values.

VLOOKUP For Text 2-3

You can say we can convert these numbers from text values to number values, yes we can, but still, we do not get the results. I think you must have encountered this kind of situation when the actual data is coming from different servers. Nothing to worry I will show you the way to get rid of this such situation. There are several ways you get rid of this.

VLOOKUP For Text 2-4

Example #3 – Convert Text Values to Numerical Values using Paste Special Method

We can convert all the number looking text values to numerical values using Paste Special method.

Keep the VLOOKUP formula as it is already applied.

Paste Special Method 3-1

Enter number 1 in cell H1 and Copy the number.

Numerical Values using Paste Special Method 3-2

After copying the cell H1, select the range D3 to D22.

Numerical Values using Paste Special Method 3-3

After selecting the range D3 to D22, press ALT + E + S + M.

Numerical Values using Paste Special Method 3-4

Once you complete this process, you should get the result with the VLOOKUP function.

Paste Special Method 3-5

Method #4 – Convert Text Values to Numerical Values using VALUE Function

If you are aware, we have a function called VALUE in excel. Using this function, we can convert number looking text values to number format in excel. Firstly before we supply the lookup value to the VLOOKUP function, we need to convert the numerical looking text value to the actual number, so nest the function VALUE inside the VLOOKUP to convert it to a number.

Open VLOOKUP & VALUE in the same formula as shown in the below image.

Numerical Values using VALUE Function 4-1

Select the lookup value.

Numerical Values using VALUE Function 4-2

Select the lookup table, mention column index number and range lookup type.

Numerical Values using VALUE Function 4-3

Now we should get values of pin code against each country code.

Numerical Values using VALUE Function 4-4

Method #5 – Convert Numerical Values to Text using TEXT Function

We have seen how to deal with when the lookup value is in text format. What if the data table itself is in text format, but your lookup values are in numerical format.

Text using TEXT Function 5-1

Open VLOOKUP & TEXT function together in cell E3 and select the lookup value as cell D3.

Text using TEXT Function 5-2

In order to convert the number value to text, mention the format text argument as zero (0).

Text using TEXT Function 5-3

After applying the VLOOKUP Formula answer is shown below.

Text using TEXT Function 5-4

As usual select remaining arguments of the VLOOKUP function and complete the formula. You should get the result for your VLOOKUP function.

Text using TEXT Function 5-5

Things to Remember About VLOOKUP For Text

  • To convert numerical values to text values, we can use the TRIM function as well as the TEXT function.
  • To convert the number looking text values to numerical values, we can use the VALUE function.
  • In order to find the text values, errors turn on the error checking.

Recommended Articles

This is a guide to VLOOKUP For Text. Here we discuss how to use VLOOKUP For Text in excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Tutorial in Excel
  2. Text Formula In Excel
  3. VLOOKUP Function in Excel
  4. VLOOKUP Examples in Excel
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
Excel Advanced Training (16 Courses, 23+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

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

EDUCBA Login

Forgot Password?

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

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

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

EDUCBA

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

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

Let’s Get Started

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

EDUCBA

Download VLOOKUP For Text Excel Template

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